Traverse multiple database servers in a single query

Traverse multiple database servers in a single query.

Suppose we have multiple cities data which are stored on different Transactional database servers separately as:

CityName	StateName	PostalCode	CountryName	DatabaseName	ServerName
BTX	TX	78520	US	TranBTX	[PC-Tran312]
HTX	TX	78551	US	TranHTX	[PC-Tran316]
HWI	WI	53211	US	TranHWI	[PC-Tran314]
CWI	WI	53211	US	TranCWI	[PC-Tran315]
VFL	FL	32204	US	TranVFL	[PC-Tran318]
LFL	FL	32216	US	TranLFL	[PC-Tran319]
CFL	FL	32068	US	TranCFL	[PC-Tran320]

For reporting or any other purpose, we want to fetch the data for all cities from their database servers to generate one result set by SQL query without creating a centralized data ware house so for this I have built a solution by writing a T-SQL Query to traverse all the database servers at run time to generate a single result set as:

Here, we have created one table “LocationInformation” which is having database name and database server information for each City so by using this table we can get the database server name for each city and in which database of that server we need to run our query because table’s name are same in each database server for each city.

So for traversing multiple database servers, first you must have are authorized to access all database servers and here we need to established a linked connection between all these server or we can do one thing choose a master database server which is linked to all rest of database servers so in my scenario, I have one master database server which is linked through all database server or we can established a linked server by below given query:

---->>> Query to check Link is exists between two Servers or not <<<------
Declare @servername varchar(50)

set @servername='PC-Tran315'

IF EXISTS ( SELECT 1 FROM sysservers WHERE srvname = @servername )

BEGIN

Select 'Yes,Server is linked already!'

End

---->>> Query to create a new Link between two Servers <<<------

Declare @servername varchar(50)

set @servername='PC-Tran315'

IF NOT EXISTS ( SELECT 1 FROM sysservers WHERE srvname =@servername  )

BEGIN

EXEC master.dbo.sp_addlinkedserver

    @server =@servername

End

So first I am fetching the Server name and Database name for each city and storing it to a temp table “#CityInfo” by below query:

Select row_number() OVER (ORDER BY Code) AS RowNumber,CityName,ServerName into #CityInfo from LocationInformation

Where CityName in  (@CityName)

Result will be as:

RowNumber	SiteName	ServerName
1	BTX	[PC-Tran311]
2	HTX	[PC-Tran311]
3	HWI	[PC-Tran314]

After that I have created another temp table for storing query result and some variables for assigning values while traversing through database servers and used WHILE mechanism here to traverse  for all cities given in the parameter.
So inside the WHILE LOOP, First I am getting city name and their database server information from #CityInfo table and after this I am checking for a link between servers (between current server or master server on which I am running this T-SQL query and that server where I need to run query for selected city) and after this I am building a dynamic query by adding servers and Database information just before the table’s name at run time and saving this dynamic query into a variable “@Query” and at the last I am executing this dynamic query which is stored inside the @Query variable through below query to insert result into the second temp table “#TaskDetails” which will be running at the last to give final result for all cities.
EXEC sp_executesql @Query

Same procedure I am following for all cities one by one through WHILE LOOP as:

---->>>> Query to retrieve data from Different Transactional servers <<<<----
DECLARE @WeekBegin varchar(20)
SET @WeekBegin = '03/15/2014'

IF OBJECT_ID('tempdb.[dbo].[#CityInfo]') IS NOT NULL DROP TABLE [dbo].[#CityInfo]

IF OBJECT_ID('tempdb.[dbo].[#TaskDetails]') IS NOT NULL DROP TABLE [dbo].[#TaskDetails]

---->>> Below Query to get Server information for each City for looping <<<------

 

Select row_number() OVER (ORDER BY Code) AS RowNumber,CityName,ServerName into #CityInfo from LocationInformation

Where CityName in  ('ACC','HTN','OLE')

---->>> Create a temp table for storing final result from WHILE LOOP <<<----

Create table #TaskDetails(CityName varchar(20),TaskLoggedDate varchar(20),TaskAssignedDate      varchar(20)

,TaskLoggedDayOfWeek    varchar(10),TotalRevenue  decimal )

Declare @City varchar(50)

Declare @i int

Declare @ServerName varchar(50)

Declare @Query nvarchar(max)

Set @i=1

-------------->>> WHILE LOOP For looping on each server <<<---------

While(@i<=(Select Max(Rownumber) from  #CityInfo))

Begin

      set @City=(Select CityName from  #CityInfo where rownumber= @i)

      set @ServerName=(Select ServerName from  #CityInfo where rownumber= @i)

 

-->> Query to establish a new Link between Servers if it doesn't exist <<--

      IF NOT EXISTS ( SELECT 1 FROM sysservers WHERE srvname =@ServerName  )

      BEGIN

            EXEC master.dbo.sp_addlinkedserver

            @server =@ServerName

      End

     ---->> Creating a dynamic query with server and database info <<----

      set @Query='SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

                  SELECT

                        CityName

                        ,TaskLoggedDate

                        ,TaskAssignedDate

                        ,TaskLoggedDayOfWeek

                        ,TotalRevenue     

 

FROM '+@ServerName+'.tran'+@City+'.dbo.TaskDetials TD WITH (NOLOCK)

      WHERE TD.CityName='''+@City+'''

AND (''<All>'' IN ('''+@WeekBegin+''') OR (DATEADD(DAY, 0-DATEPART(WEEKDAY, convert(varchar,TD.TaskLoggedDate,101)),

convert(varchar,TD.TaskLoggedDate,101))) IN ('''+@WeekBegin+'''))'

    
      Insert into #TaskDetails

      EXEC sp_executesql @Query   

      Set @i=@i+1

END

------------*** End of While loop ***-------------

Select * From #TaskDetails

So in the code I am fetching data from TaskDetials table from three different database servers and from the #TaskDetails table we will be getting combined result for all three cities at the last:

CityName	TaskLoggedDate	TaskAssignedDate	TaskLoggedDayOfWeek	TotalRevenue
ACC	Mar 18 2014 12:00AM	Dec 17 2013 10:02AM	Tuesday	6036
ACC	Mar 19 2014 12:00AM	Dec 18 2013  2:51PM	Wednesday	3018
HTN	Mar 20 2014 12:00AM	Dec 19 2013  7:44AM	Thursday	0
HTN	Mar 19 2014 12:00AM	Dec 19 2013  1:31PM	Wednesday	3018
OLE	Mar 20 2014 12:00AM	Dec 19 2013  1:47PM	Thursday	3018

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!