Microsoft Business Intelligence

Wednesday, 30 June 2021

Some time we need to run the sql files in sql server. Sql file may have sql script. In this post we will see that how we will run the sql file.

Below is the file.

This will return the result set. We will store this result set into the other files.

Basically, this file contains the execution result sql script.

DECLARE @filePath VARCHAR(4000)

, @Execution_Log_Path VARCHAR(4000)

, @serverName VARCHAR(20)

, @sqlString VARCHAR(4000)

, @dbName VARCHAR(100);

Set @filePath = 'J:\code\DimProduct.sql';

set @Execution_Log_Path= 'J:\code\DimProduct_resultset.txt';

set @serverName ='BAGESH\BAGESHDB';

SET @dbName ='AdventureWorksDW2017';

SET @sqlString = 'SQLCMD -E -S '+@serverName+' -d '+@dbName+' -i '+ @filePath +' -o '+ @Execution_Log_Path

EXEC master..xp_cmdshell @sqlString

Running this script

Executed successfully.

See the result in the result set file.

Get the expected result.

Pages