How can I execute a set of .SQL files from within SSMS?

Question

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?

Grubbs

Answer 1

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

Erstwhile

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. – Caviar

Why is sqlcmd better than the solution suggested in this post? – Bisitun

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. – Rois

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. – Outgoings

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 – Aegeus

@PaulMcCarthy - you likely need to include a GO - either at the foot of each file or between the :r commands in the script containing all the file paths. Otherwise you will end up with just a single batch with a concatenation of all the file contents. And CREATE TRIGGER must be the first statement in the batch or you will get this type of error – Looper

mssql 2016 incorrect syntax near '\' – Larisalarissa

Answer 2

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

Tasteful

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. – Suffumigate

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 .... – Outgoings

Answer 3

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;

Cultivation

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! – Londrina

This is just using sqlcmd a different way and has the same problem, it needs credentials to connect to the database. – Aegeus

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. – Aegeus

Ran this script on a test file and nothing happens. – Larisalarissa

@urasquirrel you may need to add some logging or PRINT statement in order to know what's happening – Cultivation

Answer 4

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

Multiplicand

Answer 5

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

Taps