Get Windows directories Structure from SQL

xp_dirtree 'C:\Windows'

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)