10/14/2015

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!