I have a script that simply executes a SQL query against a single table in a single DB that worked well, until the developers implemented DB sharding.
Now, there are 6 different tables I need to check on two different SQL servers.
I got something working but it is a total hack job, plus if they decide to add more servers the code isn't very extensible.
Here's what I have:
$valueBeingSearchedFor = 'someData' #DB Query Strings $Query = "Select * FROM [tableWithData].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'" $Query1 = "Select * FROM [tableWithData_1].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'" $Query2 = "Select * FROM [tableWithData_2].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'" $Query3 = "Select * FROM [tableWithData_3].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'" $Query4 = "Select * FROM [tableWithData_4].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'" $Query5 = "Select * FROM [tableWithData_5].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'" # DB connection strings $connection = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR5\I01;Initial Catalog=tableWithData;User ID=user;Password=Pa55word;') $connection1 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR5\I01;Initial Catalog=tableWithData_1;User ID=user;Password=Pa55word;') $connection2 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR6\I02;Initial Catalog=tableWithData_2;User ID=user;Password=Pa55word;') $connection3 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR6\I02;Initial Catalog=tableWithData_3;User ID=user;Password=Pa55word;') $connection4 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR6\I02;Initial Catalog=tableWithData_4;User ID=user;Password=Pa55word;') $connection5 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR6\I02;Initial Catalog=tableWithData_5;User ID=user;Password=Pa55word;') Write-host "Going to check for $valueBeingSearchedFor" #connects to SQL and grabs the data Write-host "Checking tableWithData" $connection.Open() $command = $connection.CreateCommand() $command.CommandText = $Query $dataSet = New-Object System.Data.DataSet $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command) $dataExistsInDB = $dataAdapter.Fill($dataSet) #outputs a 1 if the query returned rows $dataExistsInDB $connection.Close() Write-host "Checking tableWithData_1" $connection1.Open() $command1 = $connection1.CreateCommand() $command1.CommandText = $Query1 $dataSet1 = New-Object System.Data.DataSet $dataAdapter1 = New-Object System.Data.SqlClient.SqlDataAdapter($command1) $dataExistsInDB1 = $dataAdapter1.Fill($dataSet1) #outputs a 1 if the query returned rows $dataExistsInDB1 $connection1.Close() Write-host "Checking tableWithData_2" $connection2.Open() $command2 = $connection2.CreateCommand() $command2.CommandText = $Query2 $dataSet2 = New-Object System.Data.DataSet $dataAdapter2 = New-Object System.Data.SqlClient.SqlDataAdapter($command2) $dataExistsInDB2 = $dataAdapter2.Fill($dataSet2) #outputs a 1 if the query returned rows $dataExistsInDB2 $connection2.Close() Write-host "Checking tableWithData_3" $connection3.Open() $command3 = $connection3.CreateCommand() $command3.CommandText = $Query3 $dataSet3 = New-Object System.Data.DataSet $dataAdapter3 = New-Object System.Data.SqlClient.SqlDataAdapter($command3) $dataExistsInDB3 = $dataAdapter3.Fill($dataSet3) #outputs a 1 if the query returned rows $dataExistsInDB3 $connection3.Close() Write-host "Checking tableWithData_4" $connection4.Open() $command4 = $connection4.CreateCommand() $command4.CommandText = $Query4 $dataSet4 = New-Object System.Data.DataSet $dataAdapter4 = New-Object System.Data.SqlClient.SqlDataAdapter($command4) $dataExistsInDB4 = $dataAdapter4.Fill($dataSet4) #outputs a 1 if the query returned rows $dataExistsInDB4 $connection4.Close() Write-host "Checking tableWithData_5" $connection5.Open() $command5 = $connection5.CreateCommand() $command5.CommandText = $Query5 $dataSet5 = New-Object System.Data.DataSet $dataAdapter5 = New-Object System.Data.SqlClient.SqlDataAdapter($command5) $dataExistsInDB5 = $dataAdapter5.Fill($dataSet5) #outputs a 1 if the query returned rows $dataExistsInDB5 $connection5.Close() Write-host "Done checking DBs" # outputs the results if ($dataExistsInDB -eq 1) #if the query returned a result, query data { $objTable = $DataSet.Tables[0] $objTable | Out-GridView -Title "Query Results - found in tableWithData" } # end if elseif ($dataExistsInDB1 -eq 1) #if the query returned a result, query data { $objTable = $DataSet1.Tables[0] $objTable | Out-GridView -Title "Query Results - found in tableWithData_1" } # end if elseif ($dataExistsInDB2 -eq 1) #if the query returned a result, query data { $objTable = $DataSet2.Tables[0] $objTable | Out-GridView -Title "Query Results - found in tableWithData_2" } # end if elseif ($dataExistsInDB3 -eq 1) #if the query returned a result, query data { $objTable = $DataSet3.Tables[0] $objTable | Out-GridView -Title "Query Results - found in tableWithData_3" } # end if elseif ($dataExistsInDB4 -eq 1) #if the query returned a result, query data { $objTable = $DataSet4.Tables[0] $objTable | Out-GridView -Title "Query Results - found in tableWithData_4" } # end if elseif ($dataExistsInDB5 -eq 1) #if the query returned a result, query data { $objTable = $DataSet5.Tables[0] $objTable | Out-GridView -Title "Query Results - found in tableWithData_5" } # end if else # if the query did not return results, the data doesn't exist in the DB { [System.Windows.Forms.MessageBox]::Show("This data ID does not exist in any database" , "data ID does not exist in any DB!") }
Considering the server name, instance, and table is a little different for each shard, is there anything I could do to make this a bit more extensible or am I stuck with adding more if statements?