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

Question

I would like to execute some SQLQL (* .sql) TSQL script files in SSMS against Azure SQL Database. I am using SSMS 2008 R2

The code that I tried to execute in the SSMS query window associated with the corresponding database instance, as was chosen from the 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, Azure SQL does not support xp_cmdshell.

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

Thanks in advance.

PS I know how to open a file in SSMS

Answer 1

When using SSMS, the script is sent to the server and then executed there. As I mentioned in a comment in SQL Database, the server does not have access to the files on the machines on which they are located, and you cannot upload them to these machines. So you're approaching, not working. Opening files that you have on your computer is also not possible.

To run a set of scripts from your local computer into an SQL database, you must 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 }