CODEWITHSUNDEEP
azure-sql-database
SamJolly
SamJolly

Reputation: 6477

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

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

Upvotes: 4

Views: 4281

Answers (1)

Jan Engelsberg
Jan Engelsberg

Reputation: 1087

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
}

Upvotes: 2

Related Questions