3

I have a simple SQL file I'm using to perform some test:

DECLARE @VAR1 AS VARCHAR(100) = '12345'
DECLARE @VAR2 AS INTEGER      = 54321

WAITFOR DELAY '00:00:02'

INSERT Orders SELECT @VAR1 AS COL1, @VAR2 AS COL2, ...
...

Before launching all that, I would like to do some cleaning:

/* Clean SpecialSubSubEntries */
DELETE SpecialSubSubEntries
  WHERE Id IN (SELECT Id FROM SubSubEntries
                 WHERE SubEntryId IN (SELECT Id FROM SubEntries
                                        WHERE EntryId IN (SELECT Id FROM Entries
                                                            WHERE Name='ENTRY01')
                                      )
               )

DELETE SubSubEntries
  WHERE SubEntryId IN (SELECT Id FROM SubEntries
                         WHERE EntryId IN (SELECT Id FROM Entries
                                             WHERE Name='ENTRY01')
                      )
...

For readability reasons, I'd like to put all the cleaning commands in a file "cleanup.sql" and launch it at the beginning of my SQL-file. How do I do something like that?

I guess it's something like:

EXECUTE ".\cleanup.sql"

... but I'm not sure about the syntax.

1
  • Danger: you run the risk of SQL injection here. If anybody gets access to the cleanup.sql file, they can put in whatever commands they want, and you'll run them.
    – Brent Ozar
    Jul 1 at 10:30

2 Answers 2

4

You can perform SQLCMD from inside T-SQL/SSMS. See the answers here: How can I execute a set of .SQL files from within SSMS?

You need to set the query window to SQLCMD mode. That is a requirement, it is the client tool that does the work for you, not the database engine. To enable this mode click Query in menu bar then select SQLCMD Mode.

For a pure T-SQL solution, I've just tested the code below and it works like a charm:

An important note: it's not recommended to use this approach unless you are very aware of the risks behind it. The script disables xp_cmdshell after execution, as Microsoft recommend.

If xp_cmdshell must be used, as a security best practice it is recommended to only enable it for the duration of the actual task that requires it.

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'' DataColumn >> c:\SQLscript.sql',no_output --test script

set @sqlfile = 'c:\SQLscript.sql' --script location
set @sqlcmd = 'sqlcmd -E -i '+@sqlfile 
/* the above string runs sqlcmd on the server which sqlcmd was invoked, and with trusted connection, 
if you need to run the script on a different server/instance, you'll need replace the "-E" with "-S" and specify servername/ip address and credentials with "-U" for username and "-P" for password*/

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;
0
3

In general, reading a .sql file from the hard drive inside another .sql file is an anti-pattern. Instead, it's better to create a stored procedure:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

After that, you can execute it in an arbitrary place in sql when like this:

EXEC procedure_name;

--

If you still need it, then it's more correct to do it like this:

DECLARE @SQL varchar(MAX)
SELECT @SQL = BulkColumn
FROM OPENROWSET
    ( BULK 'c:\temp\cleanup.sql'
    , SINGLE_BLOB ) AS MYTABLE

--PRINT @sql
EXEC (@sql)

--

Note

Microsoft does not recommend using the xp_cmdshell procedure. This is not safe.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.