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?
Add a comment |
5 Answers
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 
7 Comments
AjV Jsy
Notes - file paths are relative to the machine running SSMS, not the server. To get used to this feature it's easier to add the SQLCMD button to the toolbar, and then you can see the mode enabled/disabled on the button while a
: command's back colour changes grey/white in the query window.
what evAR
Why is sqlcmd better than the solution suggested in this post?
RonC
I could never get the sqlcmd.exe approach to work. I'm sure it was just because I was specifying one of the parameters incorrectly with regard to the server, username or password. But I was able to use this answer and use SQLCMD mode in SSMS without any issue. Perhaps because I was able to first connect to the database as I always do in SSMS. Excellent approach.
Jan
The commands worked, but my sql file failed with "Query completed with errors". Checked View > Error List and View > Output but all empty. Where can I find these errors? Also double clicking on the error message didnt work.
Paul McCarthy
I tried this with a batch of sql files to create and got: Incorrect syntax near the keyword 'TRIGGER' The sql to create the trigger runs fine if I open the file and run it. So SQLCMD mode must do something different
|
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 
2 Comments
revobtz
Very simple and useful. Note: If you have a huge SQL file this is the way to go! I imported 5 gb of script and couldn't do it from management studio. This was the only way to do it.
Jan
Be sure to allow remote connections by right clicking properties on the server in MSSMS and choice Connections tab. Also check the product > instance name under View connection properties. If empty then just use
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; 
5 Comments
James L.
SQLCMD Mode in SSMS is interesting, but this really is the only solution to run complex scripts where you need the FULL capabilities of
SQLCMD. Thanks for posting!Paul McCarthy
This is just using sqlcmd a different way and has the same problem, it needs credentials to connect to the database.
Paul McCarthy
After some more experimenting I discovered that SQLCMD is a pile of crap. It can't connect to the database so it's about as useful as a broken guitar string.
Urasquirrel
Ran this script on a test file and nothing happens.
Poseidon
@urasquirrel you may need to add some logging or PRINT statement in order to know what's happening
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 
Comments
I had a very large INSERT and was tryin to run with help from SQLCMD, I needed to edit the file first so opened it in VS Code and was able to run INSERT without issue or hassle.
I'll be avoiding SQLCMD another ten years if I can help it
