77

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?

5 Answers 5

108

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
Sign up to request clarification or add additional context in comments.

7 Comments

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.
Why is sqlcmd better than the solution suggested in this post?
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.
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.
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
|
37

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

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.
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 ....
4

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

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!
This is just using sqlcmd a different way and has the same problem, it needs credentials to connect to the database.
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.
Ran this script on a test file and nothing happens.
@urasquirrel you may need to add some logging or PRINT statement in order to know what's happening
3

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

0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.