xp_dirtree 'C:\Windows'
The copy-pastes and explanations blog for SQL code, errors and daily cases! This blog is a 'list' of actions that always good to have available. The copy-paste concept here is short and clear explanations and descriptions (no long stories!) and - of course - the code to take (copy) and use (paste). The blog deals in the database (mostly) and software issues.
Blog Pages
▼
Insert default values to table
-- Insert default values to specific column:
INSERT INTO StamTable (StamTableStr, StamTableInt) VALUES ('example1', DEFAULT)
-- Insert default values to all columns in the table:
INSERT INTO StamTable DEFAULT VALUES
Join to Dynamic SQL code
The situation:
You have a dynamic SQL code that return a select results, and you want to make it available to join other queries (you don't know what will be the queries).
Solution:
1. Insert the dynamic SQL code into stored procedure.
2. Join it using OpenRowSet:
Select *
From OpenRowSet( 'SQLNCLI',
'Server=(Local);Trusted_Connection=Yes',
'Set FmtOnly Off Exec DB_Name.dbo.SP_Name'
) d
JOIN ...
Notes:
1.You need to configure the server 'Ad Hoc Distributed Queries' property to 1:
SP_Configure 'Ad Hoc Distributed Queries',1;
Go
Reconfigure With Override;
Go
2. Stored procedure that executed from OpenRowSet can't get parameters.
3. Check performance.
Error message when execute USE DB
If DB name include special characters (like '-', '.'), error messages will be displayed when execute:
USE Ddddd.7
--> error msg: Incorrect syntax near '.7'.
The problem is in the DB name.
Solution:
USE [Ddddd.7]
It can be more dangerous when execute it in dynamic SQL:
DECLARE @DBName nvarchar(128) = DB_NAME()
DECLARE @Sql nvarchar(500)
SET @Sql = 'USE ' + @DBName + ........
EXEC (@SQL)
--> error msg: Incorrect syntax near '.7'.
It recommended to write it in this syntax:
.....
SET @Sql = 'USE [' + @DBName + ']' + ........
EXEC (@SQL)