02-13-2015 04:03 AM
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 !!
Solved! Go to Solution.
03-12-2015 08:24 AM
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.
03-09-2015 02:13 PM
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.
03-12-2015 08:24 AM
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.