cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query via Powershell against multiple SQL servers do not return values

SutterKane
Level 4

Hello.

I try to query two different SQL Servers for a summary of all archived items between a specific time window.

In total, we have 8 EV Servers 1-6 on SQL Server1 ,  7+8 on SQL Server2. The query itself runs fine and it is giving no error messages.
However I get results for server 2-6 and 8 but not for server 1 and 7. The last both are the first EV servers the powershell script queries the SQL servers for , after opening the connection. I can change both servers to different ones, the result is the same: The first  query on each SQL server returns no result.

Also increasing the timeout value did not change anything.

To make it easier to understand: Here is the code:

 

###### Get Current date (-1) #####
$a = Get-Date
$f=$a.day
$b=$a.AddDays(-1)
#$b=$b.ToShortDateString()
$c=$b.day
$d=$B.month
$e=$b.year


#####################################################################################################################################################################################

$server = "dcesqlcl01\sql_emarc"
$database = "SEV01MBX01_1"
$query = "SELECT COUNT(*) AS 'No. of Items Archived' FROM Saveset  WHERE archiveddate > '$e-$d-$c 18:00:00.000' and archiveddate < '$e-$d-$f 06:00:00.000'"

$extractFile = @"
C:\EVitemcount1.csv
"@

$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection
$command.CommandTimeout = 600000

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | export-csv $extractFile -notypeinformation
Import-Csv EVItemcount1.csv | Select-Object *,@{Name='Servername';Expression={'EVMBX01'}} | Export-Csv EVItemcount1_1.csv -NoTypeInformation

#####################################################################################################################################################################################

$server = "dcesqlcl01\sql_emarc"
$database = "SEV02MBX01_3"
$query = "SELECT COUNT(*) AS 'No. of Items Archived' FROM Saveset  WHERE archiveddate > '$e-$d-$c 18:00:00.000' and archiveddate < '$e-$d-$f 06:00:00.000'"

$extractFile = @"
C:\EVitemcount2.csv
"@


$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection
$command.CommandTimeout = 600000

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | export-csv $extractFile -notypeinformation
Import-Csv EVItemcount2.csv | Select-Object *,@{Name='Servername';Expression={'EVMBX02'}} | Export-Csv EVItemcount2_2.csv -NoTypeInformation
#$extractFile | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append "EVitemcount.csv"

################################################################################################

$server = "dcesqlcl01\sql_emarc"
$database = "SEV03MBX01_5"
$query = "SELECT COUNT(*) AS 'No. of Items Archived' FROM Saveset  WHERE archiveddate > '$e-$d-$c 18:00:00.000' and archiveddate < '$e-$d-$f 06:00:00.000'"

$extractFile = @"
C:\EVitemcount3.csv
"@


$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection
$command.CommandTimeout = 600000

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | export-csv $extractFile -notypeinformation
Import-Csv EVItemcount3.csv | Select-Object *,@{Name='Servername';Expression={'EVMBX03'}} | Export-Csv EVItemcount3_3.csv -NoTypeInformation
#$extractFile | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append "EVitemcount.csv"

################################################################################################

$server = "dcesqlcl01\sql_emarc"
$database = "SEV04MBX01_7"
$query = "SELECT COUNT(*) AS 'No. of Items Archived' FROM Saveset  WHERE archiveddate > '$e-$d-$c 18:00:00.000' and archiveddate < '$e-$d-$f 06:00:00.000'"



$extractFile = @"
C:\EVitemcount4.csv
"@


$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection
$command.CommandTimeout = 600000

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | export-csv $extractFile -notypeinformation
Import-Csv EVItemcount4.csv | Select-Object *,@{Name='Servername';Expression={'EVMBX04'}} | Export-Csv EVItemcount4_4.csv -NoTypeInformation
#$extractFile | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append "EVitemcount.csv"

################################################################################################

$server = "dcesqlcl01\sql_emarc"
$database = "SEV05MBX01_10"
$query = "SELECT COUNT(*) AS 'No. of Items Archived' FROM Saveset  WHERE archiveddate > '$e-$d-$c 18:00:00.000' and archiveddate < '$e-$d-$f 06:00:00.000'"



$extractFile = @"
C:\EVitemcount5.csv
"@


$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection
$command.CommandTimeout = 600000

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | export-csv $extractFile -notypeinformation
Import-Csv EVItemcount5.csv | Select-Object *,@{Name='Servername';Expression={'EVMBX05'}} | Export-Csv EVItemcount5_5.csv -NoTypeInformation
#$extractFile | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append "EVitemcount.csv"

################################################################################################

$server = "dcesqlcl01\sql_emarc"
$database = "SEV06MBX01_12"
$query = "SELECT COUNT(*) AS 'No. of Items Archived' FROM Saveset  WHERE archiveddate > '$e-$d-$c 18:00:00.000' and archiveddate < '$e-$d-$f 06:00:00.000'"

$extractFile = @"
C:\EVitemcount6.csv
"@


$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection
$command.CommandTimeout = 600000

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | export-csv $extractFile -notypeinformation
Import-Csv EVItemcount6.csv | Select-Object *,@{Name='Servername';Expression={'EVMBX06'}} | Export-Csv EVItemcount6_6.csv -NoTypeInformation
#$extractFile | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append "EVitemcount.csv"

 ################################################################################################

$server = "DCESQLCL02\SQL_EMARC"
$database = "SEV07MBX01_14"
$query = "SELECT COUNT(*) AS 'No. of Items Archived' FROM Saveset  WHERE archiveddate > '$e-$d-$c 18:00:00.000' and archiveddate < '$e-$d-$f 06:00:00.000'"



$extractFile = @"
C:\EVitemcount7.csv
"@


$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection
$command.CommandTimeout = 600000 

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | export-csv $extractFile -notypeinformation
Import-Csv EVItemcount7.csv | Select-Object *,@{Name='Servername';Expression={'EVMBX07'}} | Export-Csv EVItemcount7_7.csv -NoTypeInformation
#$extractFile | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append "EVitemcount.csv"

################################################################################################
### SQL SERVER 2 ######

$server = "DCESQLCL02\SQL_EMARC"
$database = "SEV08MBX01_16"
$query = "SELECT COUNT(*) AS 'No. of Items Archived' FROM Saveset  WHERE archiveddate > '$e-$d-$c 18:00:00.000' and archiveddate < '$e-$d-$f 06:00:00.000'"


$extractFile = @"
C:\EVitemcount8.csv
"@

$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection
$command.CommandTimeout = 600000 

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | export-csv $extractFile -notypeinformation
Import-Csv EVItemcount8.csv | Select-Object *,@{Name='Servername';Expression={'EVMBX08'}} | Export-Csv EVItemcount8_8.csv -NoTypeInformation
#$extractFile | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append "EVitemcount.csv"
 
 ################################################################################################
 
###### Copy all nice CSVs do the EVITEM diretory #####

copy *_*.csv c:\evitem\

 

Thanks for your support !!

1 ACCEPTED SOLUTION

Accepted Solutions

FreKac2
Level 6
Partner Accredited Certified

I doubt that you'll get any response from a non-dev forum to help you troubleshoot your code.

 

I would suggest that you take one of those that doesn't work, copy/paste them into the Powershell ISE and go line by line (F8) to see were it fails.

Add some outputs between each action and see the results while troubleshooting.

I would also suggest that you create a function for the whole process as you basically only need it once and just provide different values for the unique items.

It will be easier to read and easier to troubleshoot as you only have to do it once.

Also no reason as far as I can see that you need a multiline variable for "extractfile" as it's only a path and filename.

 

 

View solution in original post

2 REPLIES 2

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

EV comes with builtin reports that you can run to get this data. you can use SSRS to have them emailed to you or run on a schedule or whatever.

FreKac2
Level 6
Partner Accredited Certified

I doubt that you'll get any response from a non-dev forum to help you troubleshoot your code.

 

I would suggest that you take one of those that doesn't work, copy/paste them into the Powershell ISE and go line by line (F8) to see were it fails.

Add some outputs between each action and see the results while troubleshooting.

I would also suggest that you create a function for the whole process as you basically only need it once and just provide different values for the unique items.

It will be easier to read and easier to troubleshoot as you only have to do it once.

Also no reason as far as I can see that you need a multiline variable for "extractfile" as it's only a path and filename.