How could I execute a set of .SQL files (each does some data transformations) from within SQL Server Management Studio?
What other alternative are there for executing .SQL files in batch?
While SQLCMD.exe is the best way, SSMS also has a SQLCMD mode where you can execute a SQLCMD script. To enable this mode click Query in menu bar then select SQLCMD Mode.
The ":r filename.sql" command is the SQLCMD script command to import and execute a sql script file. You know you are in SQLCMD mode because any lines that are SQLCMD script commands will appear with colored (gray I think) background.
:setvar path "c:\Path_to_scripts\"
:r $(path)\file1.sql
:r $(path)\file2.sql
: command's back colour changes grey/white in the query window.Use SqlCmd.exe.
For example:
sqlcmd -S myServer\instanceName -i C:\myScript.sql
or to save output to a file:
sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt
sqlcmd -S myServer ....You can execute T-SQL files without using SQLCMD Mode or using SQLCMD outside SSMS, like this:
exec sp_configure 'show advanced options',1;reconfigure with override;
exec sp_configure 'xp_cmdshell',1;reconfigure with override;
declare @sqlfile nvarchar(100),@sqlcmd varchar(4000)
exec xp_cmdshell 'del c:\SQLscript.sql && echo select ''This Is a script running on SQLCMD from SSMS'' >> c:\SQLscript.sql',no_output --test script
set @sqlfile = 'c:\SQLscript.sql' --script location
set @sqlcmd = 'sqlcmd -E -i '+@sqlfile
exec xp_cmdshell @sqlcmd --executing script
exec sp_configure 'xp_cmdshell',0;reconfigure with override;
exec sp_configure 'show advanced options',0;reconfigure with override;
SQLCMD. Thanks for posting!This is an addendum to Poseidon's answer:
You may have cause to preserve the state of 'xp_cmdshell' and even 'show advanced options' configuration settings, such that if they were on or off beforehand, they are toggled after (rather than always toggling them off). Here's a pair of (overdesigned) blocks I've written that will preserve the state of both as temp tables, then if they exist it will set the settings back afterwards. This has the advantage of being immune to a GO, so it will persist through the session and multiple batches. These are local temp tables though, so it will have to be the same session checking the AFTER block. You can either wrap your script with this as bookends or just run the before block, do what you need to do (in the same window or another one, the setting is server-wide), then in the same session run the after block before closing out.
-- BEFORE
IF (SELECT TOP 1 CAST([value] AS INT) FROM sys.configurations WHERE [name] = 'xp_cmdshell') <> 1
-- xp_cmdshell is configured off, need to reconfigure to run
BEGIN
IF(OBJECT_ID('tempdb.dbo.#xp_cmdshell_disabled') IS NOT NULL)
DROP TABLE #xp_cmdshell_disabled
CREATE TABLE #xp_cmdshell_disabled (placeholder bit)
IF (SELECT TOP 1 CAST([value] AS int) FROM sys.configurations WHERE [name] = 'Show Advanced Options') <> 1
-- advanced options is off, must be reconfigured in order to enable xp_cmdshell
BEGIN
IF(OBJECT_ID('tempdb.dbo.#advanced_options_disabled') IS NOT NULL)
DROP TABLE #advanced_options_disabled
CREATE TABLE #advanced_options_disabled (placeholder bit)
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
END
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
END
-- AFTER
BEGIN
IF(OBJECT_ID('tempdb.dbo.#xp_cmdshell_disabled') IS NOT NULL)
-- xp_cmdshell was previously off, let's turn it back off per site configuration.
BEGIN
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
IF(OBJECT_ID('tempdb.dbo.#xp_cmdshell_disabled') IS NOT NULL)
DROP TABLE #xp_cmdshell_disabled
IF(OBJECT_ID('tempdb.dbo.#advanced_options_disabled') IS NOT NULL)
BEGIN
EXEC sp_configure 'Show Advanced Options', 0
RECONFIGURE
IF(OBJECT_ID('tempdb.dbo.#advanced_options_disabled') IS NOT NULL)
DROP TABLE #advanced_options_disabled
END
END
END