How to run multiple SQL scripts, in SSMS, against SQL Azure?

Question

I would like to execute multiple SQL (*.sql) TSQL script files, in SSMS, against a SQL Azure DB. I happen to be using SSMS 2008 R2

The code, I tried to execute in a SSMS query window, linked to the relevant DB instance, as picked up from a previous SO question, was :

/*  
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) = 'DBServerName '  
DECLARE @DBName VARCHAR(100) = 'DBName'  
DECLARE @FilePath   VARCHAR(200) = 'c:\temp\scripts'  
/*

create a holder for all filenames to be executed  

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

INSERT INTO @FileList VALUES ('script1.Sql')  
INSERT INTO @FileList VALUES ('script2.Sql') 
INSERT INTO @FileList VALUES ('script3.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

Unfortunately SQL Azure does not support "xp_cmdshell".

How can I execute multiple SQL scripts for an Azure SQL instance?

Thanks in advance.

P.S I am aware of how one can open a file in SSMS

You cannot store or access files on the machines that host your SQL database. So trying to do this on the server side will not work. You need to run this from a client for example use PowerShell to iterate through the files and execute them using sqlcmd. — Jan Engelsberg
– Jan Engelsberg, Commented Mar 31, 2015 at 23:42
@JanEngelsberg, Thanks for this. I was trying to run this on my client pc which has SSMS installed, and from within which I access and query my SQL Azure instances. I was hoping I could run one SQL script which called other scripts. I know I can open each script file and run each seperately, but I was hoping to make the process a little more efficient. — SamJolly
– SamJolly, Commented Mar 31, 2015 at 23:51

Jan Engelsberg · Accepted Answer · 2015-04-02 00:05:20Z

2

When using SSMS the script is send to the server and then executed there. As I mentioned in the comment in SQL Database the server does not have access to files on the machines they are hosted on nor can you actually upload them to these machines. So you approach does not work. Opening files that you have locally on your machine is also not possible.

To execute a set of scripts from your local machine against a SQL Database you have to translate your SQL script for example into a PowerShell script:

Get-ChildItem "c:\temp\scripts" -Filter script*.sql | `
Foreach-Object{
    Write-Host 'Executing' $_.FullName
    sqlcmd -U <username>@<server> -P <Password> -S <server>.database.windows.net -d <DatabaseName> -i $_.FullName
}

answered Apr 2, 2015 at 0:05

Jan Engelsberg

1,0876 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andreas Schwarz Over a year ago

Quick remark: I add to stick the -P option and the password to make it work. The command would then be: sqlcmd -U <username>@<server> -P<Password> -S <server>.database.windows.net -d <DatabaseName> -i $_.FullName

Collectives™ on Stack Overflow

How to run multiple SQL scripts, in SSMS, against SQL Azure?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related