Querying a remote server w/ SQLCMD

So, sometimes you need a simple way to run (often the same) command on two different servers at the same time. Perhaps setting up a Linkserver is difficult, or simply too time consuming. Here’s an option. Try SQLCMD.

-- In SSMS, under the Query tab, see "SQLCmd Mode".  toggle it on.
-- This is good for interactive monitoring.  
-- Also, use full 3 part naming DB.Shema.Table/view, because you probably won't connect into the database you expect.
select 'qa02.gisLien_vw', count(*) from pamo.[PAMO].[gisLien_vw] union
select 'qa02.[PAMO].[gisVacant_CCD_vw]', count(*) from pamo.[PAMO].[gisVacant_CCD_vw] union
select 'qa02.[PAMO].[gisVacant_LLC_vw]', count(*) from pamo.[PAMO].[gisVacant_LLC_vw]
go
:connect YOURSERVERNAMEHERE
use pamo
go
select 'gisLien_vw', count(*)						from pamo.[PAMO].[gisLien_vw] union
select 'i2s.[PAMO].[gisVacant_CCD_vw]', count(*)	from [PAMO].[gisVacant_CCD_vw] union
select '[PAMO].[gisVacant_LLC_vw]', count(*)		from pamo.[PAMO].[gisVacant_LLC_vw]

About Steve Schneider

I was born (again) in June of 1983 when I came to know Jesus Christ as my saviour. You too, can know Him, and know Peace. We all have sinned, and fallen short of the grace of God. But, God demonstrates His love for us in this, that while we were still sinners, He died for us. Email me, and I can show you how to know Jesus. He is highly worth knowing!
This entry was posted in SQL 2008, SQL 2012, SQL Administration. Bookmark the permalink.

Leave a Reply...Really! I'll listen!