如何在SSMS中针对SQL Azure运行多个SQL脚本? [英] How to run multiple SQL scripts, in SSMS, against SQL Azure?

问题描述

我想在SSMS中针对SQL Azure DB执行多个SQL(* .sql)TSQL脚本文件.我碰巧正在使用SSMS 2008 R2

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

我尝试在SSMS查询窗口中执行的代码链接到相关的数据库实例，该代码是从上一个SO问题中获取的，

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 

不幸的是，SQL Azure不支持"xp_cmdshell".

Unfortunately SQL Azure does not support "xp_cmdshell".

如何为Azure SQL实例执行多个SQL脚本?

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

谢谢.

P.S我知道如何在SSMS中打开文件

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

推荐答案

使用SSMS时，脚本将发送到服务器，然后在服务器上执行.正如我在SQL数据库中的注释中提到的那样，服务器无权访问托管它们的计算机上的文件，也无法将它们实际上传到这些计算机.所以你的方法行不通.也无法打开本地计算机上的文件.

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.

要针对SQL数据库从本地计算机执行一组脚本，您必须将SQL脚本转换为PowerShell脚本，例如:

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
}

这篇关于如何在SSMS中针对SQL Azure运行多个SQL脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！