Execute Query Against Multiple Servers With A Variable Date Depending On Table Name

Question

I have a Local Server Group with seven different folders in it. Each folder has a list of registered servers, and each registered server points to a specific database. I want to write a query that can be executed against all of the registered servers/databases in all seven (002 - 007) folders. My problem is that my query has a date criteria that differs depending on which server/database I am querying. For example, my code may use a date of '2022-10-14' when querying against the server/database 002-5650, but when running the same code against 002-5660 the date may need to be '2022-10-16'. Is it possible to write a query that detects which server I am connected to before code execution that I can write a case statement to modify the date in the code?

this is pseudocode...
case when connected to server/database 002-5650 then populate date variable @apl = '2022-10-14'
else when connected to server/database 002-5660 then populate date variable @apl = '2022-10-64'
end

something like that?

Answer 1

Yes, that might be possible. You would submit something like:

   DECLARE @date date  
     
   SELECT @date = CASE @@servername  
                WHEN '002-5660' THEN '2022-10-14'  
                WHEN '002-5670' THEN '2022-10-16'  
             ...  
       END  
     
   SELECT * FROM tbl WHERE date = @date  

However, I am not sure that I see this as a tenable solution. Most likely you will need have these dates configured somewhere, for instance in a table. Obviously, you read that table in your query, but then you need to have a linked server on every server back to the instance where you have your table.

So I would rather look at a different solution altogether. For instance, I would write a script in Powershell or similar that reads the server configuration from the source server, and then connects to each server to run the query.

Answer 2

Thank you Erland, I'll give your solution a try next week. Have a great weekend :)

Answer 3

Adding to Erland's answer, below is an example Powershell script that is similar the SSMS multi-server query. The query is parameterized with the date value for each server and the query includes the server name in the results like SSMS shows in multi-server query results.

$serverConfig =  
@'  
[  
    {  
    "serverName":"Server1",  
    "databaseName":"Database1",  
    "date":"2022-10-14"  
    }  
    ,{  
    "serverName":"Server2",  
    "databaseName":"Database2",  
    "date":"2022-10-24"  
    }  
]  
'@  
  
$query = @"  
SELECT @@SERVERNAME AS ServerName, *  
     FROM dbo.YourTable  
     WHERE YourDate >= @date;  
"@  
  
try {  
  
    $serverList = ConvertFrom-Json $serverConfig  
    $dataTable = New-Object System.Data.DataTable  
    foreach($server in $serverList) {  
        $connectionString = "Data Source=$($server.serverName);Initial Catalog=$($server.databaseName);Integrated Security=SSPI"  
        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)  
        $command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)  
        $command.Parameters.Add("@date", [System.Data.SqlDbType]::Date).Value = $server.date  
        $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)  
        [void]$dataAdapter.Fill($dataTable)  
    }  
  
    # display query results  
    $dataTable | Out-GridView  
  
}  
catch {  
    throw  
}  

Answer 4

Is there a limit to the number of servers/databases that can be queried? I have been trying to run a very simple query selecting (set rowcount 10) 10 records from 113 different database tables (one table per database) spread across 7 different servers and am getting the following error:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I tried inserting the below code but it didn't help:

$dataAdapter.SelectCommand.CommandTimeout = 120