Умный способ использования SQLCMD с динамическим вызовом

У меня есть этот код SQLCMD MSSQL, который может входить в базу данных и в этом случае делать инструкцию SELECT:

   :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? У кого-нибудь есть предложение?

UPDATE: Я буду использовать это только для INSERT/UPDATE или DELETE. Поэтому я не использую хранимую процедуру. Мне нравится использовать это для обновления данных обо всех наших базах данных. Поэтому я буду использовать только таблицу temp, которая должна быть частью script.

Имена серверов и имена баз данных будут получены из таблицы temp выше. Название таблицы одинаково для всех баз данных, потому что у нас есть копии базы данных по всему миру для ограничения загрузки данных. Мне нужно убедиться, что когда я обновляю один, я также обновляю остальные.

Обновление 2: Я попытался поиграть с ним, но я продолжаю останавливаться, не имея возможности использовать значения в таблице temp. Это означает, что я не могу использовать: 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;

Ответ 1

Я буду использовать это только для INSERT/UPDATE или DELETE. Поэтому я не использую хранимую процедуру. Мне нравится использовать это для обновления данных обо всех наших базах данных.

Если это так, вы можете использовать Registered Servers:

Преимущества зарегистрированных серверов

С зарегистрированными серверами вы можете:

Зарегистрировать серверы, чтобы сохранить информацию о подключении.

Определите, работает ли зарегистрированный сервер.

Простое подключение Обозревателя объектов и Редактора запросов к зарегистрированному серверу.

Изменить или удалить регистрационную информацию для зарегистрированного сервера.

Создать группы серверов.

Обеспечьте удобные имена для зарегистрированных серверов, указав значение в поле имени зарегистрированного сервера, которое отличается от Список имен серверов.

Предоставьте подробные описания для зарегистрированных серверов.

Предоставьте подробное описание зарегистрированных групп серверов.

Экспорт зарегистрированных групп серверов.

Импортировать зарегистрированные группы серверов.

Просмотр файлов журнала SQL Server для онлайн-или автономных экземпляров SQL Server

Выполнять одновременные действия с несколькими серверами

Вы можете создать несколько групп на основе: server/database/environment (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}

Ответ 2

Выполните следующие действия:

1) Создайте сервер ссылок.

2) Запустите ниже script, я изменил ваш код snip-it

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;

Ответ 3

С помощью курсора это может быть:

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

Ответ 4

Я не запускаю ваш код, но вижу возможную проблему при использовании слова GO, которое не является ключевым словом SQL, но ключевое слово SQL Server/SqlCmd означает, что здесь заканчивается блок script, запускайте все в нем и ждать всех последствий ". Я не уверен, что он может работать внутри цикла WHILE из-за этого.

Затем я вижу, что вы пытаетесь

:CONNECT ServerName

но в документации для него я вижу, что синтаксис

:CONNECT $(ServerName)

Существует также проблема вашего цикла. SQL Server не гарантирует порядок инструкции SELECT, если вы не используете ORDER BY. Если вам не нравится порядок выполнения инструкций, тогда это ОК, в противном случае вам нужно изменить его с помощью курсора или хотя бы добавить числовой столбец и заказать его при выборе TOP 1.