Smarter way to use SQLCMD with dynamic calling

Question

I have this MSSQL SQLCMD code, which can logon the database and in thisjust do an SELECT statement:

   :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

This solution works okay, but requires me to copy paste the copy multiple times to change the database name. But can some one help me improve this by using an loop instead. I have tried by using a temporary table. Like so:

 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]')

But i cant figure out to to do the logic for iterate through the database an connect to the server and set the database name, when the data i have is as an nvarchar? Do anybody have an suggestion?

UPDATE: I'm only going to use this for INSERT/UPDATE or DELETE. So im not using a stored procedure. I like to use this for updating data on all our databases. That's why I'll only use the temp table which should be part of the script.

Servernames and database names will come from the temp table above. Table name are the same on all database's, due to we have copies of the database worldwide to limit data download i need to make sure that when i update one, that i also updates the rest.

Update 2: I have tried to play around with it, but i keep stalling, with not being able to use the values in the temp table. Meaning that i can't use the :SETVAR to set the servername:

  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;

Answer 1

I have not run your code, but I see a possible problem in using the word GO, which is not an SQL keyword, but an SQL Server/SqlCmd keyword meaning "here ends a script block, run everything in it and wait for all consequences". I am not certain it can work inside a WHILE loop because of that.

Then I see you are trying

:CONNECT ServerName

but in the documentation for it I see the syntax to be

:CONNECT $(ServerName)

There is also the issue of your loop. SQL Server does not guarantee the order of a SELECT instruction if you do not use ORDER BY. If you don't care the order in which you execute the instructions, then it's OK, otherwise you need to change it with a cursor or at least add a numeric column and order by it when you select TOP 1.

Answer 2

Using a cursor, it could be:

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

Answer 3

Do the following steps:

1) Create link server.

2) Run below script i have modified your code 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;

Answer 4

I'm only going to use this for INSERT/UPDATE or DELETE. So im not using a stored procedure. I like to use this for updating data on all our databases

If it is the case you could consider using Registered Servers:

Benefits of Registered Servers

With Registered Servers you can:

• Register servers to preserve the connection information.

• Determine if a registered server is running.

• Easily connect Object Explorer and Query Editor to a registered server.

• Edit or delete the registration information for a registered server.

• Create groups of servers.

• Provide user-friendly names for registered servers by providing a value in the Registered server name box that is different from the Server name list.

• Provide detailed descriptions for registered servers.

• Provide detailed descriptions of registered server groups.

• Export registered server groups.

• Import registered server groups.

• View the SQL Server log files for online or offline instances of SQL Server

• Execute Statements Against Multiple Servers Simultaneously

You could create multiple groups based on: server/database/environment(DEV/PROD/QA).

Another possibility is to export registered servers to file, put in source control system (SVN/Git) and share with other developers.

Using SSMS you click new query based on server group and execute the same query on multiple databases at once.

^{Image from: http://sqlmag.com/site-files/sqlmag.com/files/archive/sqlmag.com/content/content/142469/Greenwood-SQL2331-Fig5-sm.jpg}