更智能的方式使用SQLCMD动态调用

问题描述：

我有这样的MSSQL SQLCMD代码，可以登录该数据库，并在thisjust做一个SELECT语句：更智能的方式使用SQLCMD动态调用

:CONNECT czasql-001 
    SELECT * FROM [Lps_Hepper_Cz].[config].[LpsPlant] 
    GO 

    :CONNECT LS_Hepper_DK 

    SELECT * FROM [LPS_Hepper_NY].[config].[LpsPlant] 

    :CONNECT LS_Hepper_DK 
    SELECT * FROM [LPS_Hepper_DK].[config].[LpsPlant] 

    :CONNECT LS_Hepper_DK 
    SELECT * FROM [LPS_Hepper_SUPPLIER].[config].[LpsPlant] 
    GO 

      :CONNECT LS_Hepper_372 
    SELECT * FROM [LPS_Hepper_MO].[config].[LpsPlant] 
    GO 

      :CONNECT LS_Hepper_678 
    SELECT * FROM [LPS_Hepper_678].[config].[LpsPlant] 
    GO

此解决方案还行，但需要我来复制粘贴多次复制以更改数据库名称。但是，有人可以通过使用循环来改善这一点。我尝试过使用临时表。像这样：

declare @tbl table (ServerName nvarchar(50), DbName nvarchar(50), IsDone bit default(0)) 
insert into @tbl (ServerName,DbName) VALUES ('CZASQL-001', '[Lps_Hepper_CZ]') 
insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_DK]') 
insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_NY]') 
insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_Supplier]') 
insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_372', '[Lps_Hepper_MO]') 
insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_678', '[Lps_Hepper_678]')

，但我不能弄清楚，以便对数据库做逻辑迭代一个连接到服务器，并设置数据库名称，当我拥有的数据是作为一个nvarchar？有人有建议吗？

更新： 我只会用这个INSERT/UPDATE或DELETE。所以即时通讯不使用存储过程。我喜欢用它来更新我们所有数据库的数据。这就是为什么我只使用应该是脚本一部分的临时表。

服务器名称和数据库名称将来自上面的临时表。所有数据库中的表名都是相同的，因为我们在全球范围内有数据库副本以限制数据下载，所以我需要确保当我更新一个数据库时，我也更新了其余数据库。

更新2： 我试图玩弄它，但我一直拖延，不能使用临时表中的值。意思是说我不能使用：SETVAR设置服务器名：

declare @tbl table (ServerName nvarchar(50), DbName nvarchar(50), IsDone bit default(0)) 
    insert into @tbl (ServerName,DbName) VALUES ('CZASQL-001', '[Lps_Hepper_CZ]') 
    insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_DK]') 
    insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_NY]') 
    insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_Supplier]') 
    insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_372', '[Lps_Hepper_MO]') 
    insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_678', '[Lps_Hepper_678]') 

     WHILE (SELECT COUNT(*) FROM @tbl WHERE IsDone = 0) > 0 
     BEGIN 

     DECLARE @selectedRow INT = (SELECT TOP 1 Id FROM @tbl WHERE IsDone = 0) 
     --DECLARE @ServerName NVARCHAR(50)= (SELECT ServerName FROM @tbl WHERE Id = @selectedRow) 
     --DECLARE @DatabaseName NVARCHAR(50) = (SELECT DbName FROM @tbl WHERE Id = @selectedRow) 
     DECLARE @ServerName sysname= (SELECT ServerName FROM @tbl WHERE Id = @selectedRow) 
     DECLARE @DatabaseName sysname = (SELECT DbName FROM @tbl WHERE Id = @selectedRow) 

     :SETVAR DatabaseName @DatabaseName 
     :SETVAR ServerName @ServerName 
     SELECT ServerName --This looks correctly 
     print CONVERT(NVARCHAR(100),@selectedRow) 
     :CONNECT ServerName 

      USE @DatabaseName 
      GO 
      SELECT * FROM [config].[LpsPlant] 
     GO 

     UPDATE @tbl SET IsDone = 1 WHERE Id = @selectedRow 
    END;

尝试从Powershell调用'sqlcmd'。 PS有'foreach'声明http://sqlvariant.com/2011/01/powershell-week-at-sql-university-post-5/，这很容易做到。 –

'SELECT *'是你需要执行的实际语句还是仅仅是一个例子？如果您需要导出数据，'bcp'是一个更好的选择（也可以从PowerShell中按照Ilya的建议执行）。 –

我认为这不是从像.NET这样的外部编程环境中调用的，因为您可以轻松地在该场景中创建每个命令。因此，我们需要知道：1）您使用的是存储过程，2）您如何确定服务器和表名称？（例如，它们是否传递给存储过程？是否在运行时确定？是否可以使用此信息设置永久表？） –

答

我只打算用这个INSERT/UPDATE或DELETE。所以即时通讯不使用存储过程。我喜欢用这对我们所有数据库

更新数据如果是这样的话，你可以考虑使用Registered Servers：

注册的服务器的好处

，注册服务器你可以：

注册服务器以保留连接信息。

确定注册的服务器是否正在运行。

轻松连接对象资源管理器和查询编辑器到注册服务器。

编辑或删除注册服务器的注册信息。

创建服务器组。

通过在注册服务器名称框中提供一个与服务器名称列表不同的值，为注册服务器提供用户友好的名称。

提供注册服务器的详细说明。

提供注册服务器组的详细说明。

导出已注册的服务器组。

导入注册的服务器组。

信息查看SQL服务器的在线或离线的情况下，SQL Server日志文件

Execute Statements Against Multiple Servers Simultaneously

您可以根据上创建多个组：服务器/数据库/环境（DEV/PROD/QA）。

另一种可能性是将注册的服务器导出到文件，放入源代码管理系统（SVN/Git）并与其他开发人员共享。

使用SSMS，您可以单击基于服务器组的新查询，并一次对多个数据库执行相同的查询。

^{图片来自：http://sqlmag.com/site-files/sqlmag.com/files/archive/sqlmag.com/content/content/142469/Greenwood-SQL2331-Fig5-sm.jpg}

我之所以不能使用它，是因为我希望能够将脚本发送给数据库管理员，他们可以在不确保注册服务器的情况下运行它！ – mortenstarck

@mortenstarck正如我之前所说，导出注册服务器配置非常容易。工作量非常简单：导出conf，放入控制系统或通过电子邮件发送，导入和运行查询。 [导入和导出注册的SQL Server到其他机器]（https://www.mssqltips.com/sqlservertip/2015/import-and-export-registered-sql-servers-to-other-machines/） – lad2025

答

请执行下列操作步骤：

1）创建链接服务器。下面的脚本运行

2）我已经修改了你的代码剪断，它

IF OBJECT_ID('tempdb..##Results') IS NOT NULL 
    Truncate TABLE ##Results 
else 
    CREATE TABLE ##Results 
    (id int identity ,ServerName nvarchar(50), DbName nvarchar(50), IsDone bit default(0)) 
     -- populat link server name and db name 
    insert into ##Results (ServerName,DbName) VALUES ('CZASQL-001', '[Lps_Hepper_CZ]') 
    insert into ##Results (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_DK]') 
    insert into ##Results (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_NY]') 
    insert into ##Results (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_Supplier]') 
    insert into ##Results (ServerName,DbName) VALUES ('LS_Hepper_372', '[Lps_Hepper_MO]') 
    insert into ##Results (ServerName,DbName) VALUES ('LS_Hepper_678', '[Lps_Hepper_678]') 

     WHILE (SELECT COUNT(*) FROM ##Results WHERE IsDone = 0) > 0 
     BEGIN 

     DECLARE @selectedRow INT = (SELECT TOP 1 Id FROM ##Results WHERE IsDone = 0) 
     DECLARE @sq1 varchar (100) ='SELECT * FROM ' ; -- 
     DECLARE @sql varchar (8000) = ''; 
     DECLARE @FROM varchar (100) ='[config].[LpsPlant]' ; 
       select @sql = @sq1 + ServerName + '.'+ DbName+ @FROM FROM ##Results WHERE Id = @selectedRow 

     print @sql 
     exec @sql 

     UPDATE ##Results SET IsDone = 1 WHERE Id = @selectedRow 
    END;

答

使用游标，则可能是：

CREATE TABLE #TEMP (ServerName nvarchar(50), DbName nvarchar(50)) 
insert into #TEMP (ServerName,DbName) VALUES ('CZASQL-001', '[Lps_Hepper_CZ]') 
insert into #TEMP (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_DK]') 
insert into #TEMP (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_NY]') 
insert into #TEMP (ServerName,DbName) VALUES ('LS_Hepper_DK', '[Lps_Hepper_Supplier]') 
insert into #TEMP (ServerName,DbName) VALUES ('LS_Hepper_372', '[Lps_Hepper_MO]') 
insert into #TEMP (ServerName,DbName) VALUES ('LS_Hepper_678', '[Lps_Hepper_678]') 

DECLARE @ServerName VARCHAR(100), 
@DatabaseName VARCHAR(100); 

DECLARE CRS CURSOR LOCAL 
FOR SELECT * FROM #TEMP 

FETCH NEXT FROM CRS INTO @ServerName, @DatabaseName; 

WHILE @@FETCH_STATUS=0 
BEGIN 
    print CONVERT(NVARCHAR(100),@selectedRow) 

    :CONNECT @ServerName 
    GO 
    USE @DatabaseName 
    GO 
    SELECT * FROM [config].[LpsPlant] 
    GO 

    FETCH NEXT FROM CRS INTO @ServerName, @DatabaseName; 
END 

DROP TABLE #TEMP

答

我还没有运行您的代码，但我看到了在使用不是SQL关键字的GO这个词时可能会出现问题，而是一个SQL Server/SqlCmd关键字，意思是“在这里结束一个脚本块，在其中运行所有内容并等待所有后果”。我不确定它可以在WHILE循环内工作，因为这一点。

然后我看到你试图

:CONNECT ServerName

但在documentation它我看到的语法是

:CONNECT $(ServerName)

还有你的循环问题。如果不使用ORDER BY，SQL Server不保证SELECT指令的顺序。如果您不关心执行说明的顺序，那么可以，否则您需要使用光标更改它，或者至少添加一个数字列并在选择TOP 1时按顺序进行排序。

更智能的方式使用SQLCMD动态调用

相关推荐