Is it possible to execute a text file from SQL query?

Question

I have a number of generated .sql files that I want to run in succession. I'd like to run them from a SQL statement in a query (i.e. Query Analyzer/Server Management Studio).
Is it possible to do something like this and if so what is the syntax for doing this?

I'm hoping for something like:

exec 'c:\temp\file01.sql' 
exec 'c:\temp\file02.sql'

I am using SQL Server 2005 and running queries in management studio.

Answer 1

use xp_cmdshell and sqlcmd

EXEC xp_cmdshell  'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName

Answer 2

Very helpful thanks, see also this link: Execute SQL Server scripts for a similar example. To turn xp_cmdshell on and off see below:

On

SET NOCOUNT ON  
EXEC master.dbo.sp_configure 'show advanced options', 1 
RECONFIGURE 
EXEC master.dbo.sp_configure 'xp_cmdshell', 1 
RECONFIGURE 

Off

EXEC master.dbo.sp_configure 'xp_cmdshell', 0 
RECONFIGURE 
EXEC master.dbo.sp_configure 'show advanced options', 0 
RECONFIGURE  
SET NOCOUNT OFF 

Answer 3

Or just use openrowset to read your script into a variable and execute it (sorry for reviving an 8 years old topic):

DECLARE @SQL varchar(MAX)
SELECT @SQL = BulkColumn
FROM OPENROWSET
    (   BULK 'MeinPfad\MeinSkript.sql'
    ,   SINGLE_BLOB ) AS MYTABLE

--PRINT @sql
EXEC (@sql)

Answer 4

This is what I use. Works well and is simple to reuse. It can be changed to read all files in the directory, but this way I get to control which ones to execute.

/*  
execute a list of .sql files against the server and DB specified  
*/  
SET NOCOUNT ON  

SET XACT_ABORT ON  
BEGIN TRAN  

DECLARE @DBServerName   VARCHAR(100) = 'servername'  
DECLARE @DBName VARCHAR(100) = 'db name'  
DECLARE @FilePath   VARCHAR(200) = 'path to scrips\'  
/*

create a holder for all filenames to be executed  

*/  
DECLARE @FileList TABLE (Files NVARCHAR(MAX))  

INSERT INTO @FileList VALUES ('script 1.sql')  
INSERT INTO @FileList VALUES ('script 2.sql')  
INSERT INTO @FileList VALUES ('script X.sql')  

WHILE (SELECT COUNT(Files) FROM @FileList) > 0  
BEGIN  
   /*  
   execute each file one at a time  
   */  
   DECLARE @FileName NVARCHAR(MAX) = (SELECT TOP(1) Files FROM @FileList)  
   DECLARE @command  VARCHAR(500)  = 'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i "' + @FilePath + @Filename +'"'  
   EXEC xp_cmdshell  @command   

   PRINT 'EXECUTED: ' + @FileName     
   DELETE FROM @FileList WHERE Files = @FileName  
END  
COMMIT TRAN  

Answer 5

I wouldn't recommended doing this, but if you really have to then the extended stored procedure xp_cmdshell is what you want. You will have to first read the contents of the file into a variable and then use something like this:

DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd

Note: xp_cmdshell runs commands in the background, because of this, it must not be used to run programs that require user input.

Answer 6

Take a look at OSQL. This utility lets you run SQL from the command prompt. It's easy to get installed on a system, I think it comes with the free SQL Server Express.

Using the osql Utility

A qick search of "OSQL" on stack overflow shows a lot of stuff is available.

The main thing to handle properly is the user and password account parameters that get passed in on the command line. I have seen batch files that use NT file access permissions to control the file with the password and then using this file's contents to get the script started. You could also write a quick C# or VB program to run it using the Process class.

Answer 7

For Windows Authentication, if you are running as another user: Open Command Prompt as your Windows user (Right click on it, Open File Location, Shift + Right Click, Run as a different user)

 sqlcmd -S localhost\SQLEXPRESS -d DatabaseName-i "c:\temp\script.sql"

Or if you are using Sql Server user:

sqlcmd -S localhost\SQLEXPRESS -d DatabaseName-i "c:\temp\script.sql" -U UserName -P Password

Replace localhost\SQLEXPRESS with you server name if not local server.

Answer 8

Open windows command line (CMD)

sqlcmd -S localhost -d NorthWind -i "C:\MyScript.sql"

Answer 9

For anybody stumbling onto this question like I did and might find this useful, I liked Bruce Thompson's answer (which ran SQL from files in a loop), but I preferred Pesche Helfer's approach to file execution (as it avoided using xp_cmdshell).

So I combined the two (and tweaked it slightly so it runs everything from a folder instead of a manually created list):

DECLARE @Dir NVARCHAR(512) = 'd:\SQLScriptsDirectory'

DECLARE @FileList TABLE (
  subdirectory NVARCHAR(512),
  depth int,
  [file] bit
)

INSERT @FileList
EXEC Master.dbo.xp_DirTree @Dir,1,1

WHILE (SELECT COUNT(*) FROM @FileList) > 0  
BEGIN  
   DECLARE @FileName NVARCHAR(MAX) = (SELECT TOP(1) subdirectory FROM @FileList) 
   DECLARE @FullPath NVARCHAR(MAX) = @Dir + '\' + @FileName

   DECLARE @SQL NVARCHAR(MAX)
   DECLARE @SQL_TO_EXEC NVARCHAR(MAX)
   SELECT @SQL_TO_EXEC = 'select @SQL = BulkColumn
   FROM OPENROWSET
       (   BULK ''' + @FullPath + '''
       ,   SINGLE_BLOB ) AS MYTABLE'

   DECLARE @parmsdeclare NVARCHAR(4000) = '@SQL varchar(max) OUTPUT'  

   EXEC sp_executesql @stmt = @SQL_TO_EXEC
                 , @params = @parmsdeclare
                 , @SQL = @SQL OUTPUT  

   EXEC (@sql)
   DELETE FROM @FileList WHERE subdirectory = @FileName  

   PRINT 'EXECUTED: ' + @FileName     
END