Connect to multiple servers and databases and run the same SQL statements

Question

I have a fixed list of SQL Server 2019 (v15) Express databases, distributed across the country. I am hoping to script a way of querying them in a T-SQL batch. Have gotten as far as enabling SQLCMD and running this.

DECLARE @tsql nvarchar(500) = 'SELECT value FROM DB.dbo.Tablename'
    
:Connect EMS2410
GO

EXECUTE sp_executesql @tsql

:Connect    EMS2411
GO

EXECUTE sp_executesql @tsql

Without the GO, the connect statement does not work, with it I lose my variable. Looking for any ideas people may have for this situation. I could do a search and replace to patch in the actual SQL but that seems messy.

Did you tried add linked server and use sp_executesql remotely (exec linkedserver.master.dbo.sp_executesql @tsql)? You cannot change server connection in one session (its bounded to instance). — Deadsheep39, Jul 23 '21 at 21:06
How about looping through your databases in a PowerShell script? https://stackoverflow.com/questions/8423541/how-do-you-run-a-sql-server-query-from-powershell — Isaac, Jul 23 '21 at 21:23

JOakland2021 · Answer 1 · 2021-07-24T01:07:34.350

I've never tried to connect to a different server with tsql. You can try testing this. Set up a table for the server names.

CREATE TABLE [dbo].[ServerName](
    [SvrName] [varchar](50) NOT NULL,
 CONSTRAINT [PK__Svr] PRIMARY KEY CLUSTERED 
(
    [SvrName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[ServerName] ([SvrName]) VALUES (N'Server1')
INSERT [dbo].[ServerName] ([SvrName]) VALUES (N'Server2')
INSERT [dbo].[ServerName] ([SvrName]) VALUES (N'Server3')
INSERT [dbo].[ServerName] ([SvrName]) VALUES (N'Server4')
INSERT [dbo].[ServerName] ([SvrName]) VALUES (N'Server5')
GO

The carriage return isn't displaying it as I would like but it should work.

declare @sql1 varchar(max) = ''
declare @sql2 varchar(max) = ''
; with SvrNames as (
    SELECT [SvrName]
      FROM [DBName].[dbo].[ServerName]
)
select @sql1 = STRING_AGG(' :Connect ' + [SvrName] + ' GO ' + char(10) + char(13) + 'EXECUTE sp_executesql @tsql ' + char(10) + char(13), '')
from SvrNames

select @sql2 = ' DECLARE @tsql nvarchar(500) = ''SELECT value FROM DB.dbo.Tablename''' + @sql1
--select @sql2
EXECUTE (@sql2)

The above script generates and executes the following.

DECLARE @tsql nvarchar(500) = 'SELECT value FROM DB.dbo.Tablename' 
 :Connect Server1 GO   
 EXECUTE sp_executesql @tsql    
 
 :Connect Server2 GO   
 EXECUTE sp_executesql @tsql    
 
 :Connect Server3 GO   
 EXECUTE sp_executesql @tsql    
 
 :Connect Server4 GO   
 EXECUTE sp_executesql @tsql    
 
 :Connect Server5 GO   
 EXECUTE sp_executesql @tsql

score 1 · Answer 2 · answered Jul 25 '21 at 15:08

The easiest method would be to create a separate script file - and then use that script file in each section:

:connect servername
:r {script file}

You can also set it up so your SQLCMD file builds the script - then each section executes the script - and at the end you delete the script:

:out C:\Temp\TempScript.sql
Set NoCount On;
Declare @sqlCommand nvarchar(max) = '';
Set @sqlCommand = '
   Set NoCount On;
   {build your script}
';

Select @sqlCommand;
Go

Calling that script file:

:setvar ScriptFile "C:\Temp\TempScript.sql"

:connect servername
:r '$(ScriptFile)'
Go

Then - delete the temp script file:

!!del '$(ScriptFile)'

With all that said - it would be much easier to use Powershell and Invoke-SqlCmd to loop over the list of servers and execute the query against each server.

Connect to multiple servers and databases and run the same SQL statements

2 Answers2