JB4375
asked on
Second half of Powershell Script Ignored
My script writes the UserName from SQL records to a CSV file where the Disable date is less than my check date by 90 days.
After this portion is complete, I want to update the 'Completed' field to 'True' for these same records. The problem is that it appears that the second SqlQuery that does the update is just being ignored.
1. If I run the same Update statement at the top of the script in place of the Select statement, it works fine.
2. Placing obivous errors in the Update statement when it's at the bottom of the script, such as a fake table name, doesn't even generate an error.
3. I've used write-host to display everything and it's showing the correct info.
Thanks,
JB
$SQLServer = "Server"
$SQLDBName = "database"
$AttachmentPath = "C:\file.csv"
# Select records to CSV that are older than than 90 days and have not been processd
$d = (Get-Date).adddays(-90).To String('yy yy-MM-dd')
$SqlQuery = "SELECT * from Test WHERE DisableDate < '$d' AND Completed='False'"
$SqlConnection = New-Object System.Data.SqlClient.SqlC onnection
$SqlConnection.ConnectionS tring = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
#Instantiates the Command Object
$SqlCmd = New-Object System.Data.SqlClient.SqlC ommand
#Command Text Using Variable
$SqlCmd.CommandText = $SqlQuery
#Create Connection
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlD ataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
#Populate Hash Table
$objTable = $DataSet.Tables[0]
#Populate Hash Table, and export to CSV File
#$DataSet.Tables[0] | Export-Csv -NoTypeInformation $AttachmentPath
#Populate Hash Table, export to CSV, and change column headers
$DataSet.Tables[0] | select @{l='Alias'; e={$_.Username}} | Export-Csv -NoTypeInformation $AttachmentPath
$SqlConnection.Close()
$SqlQuery2 = "Update Test SET Completed='True' WHERE DisableDate < '$d' AND Completed='False'"
# Write-Host "SqlQuery2: " $SqlQuery2
# Write-Host "Check Date: " $d
$SqlConnection2 = New-Object System.Data.SqlClient.SqlC onnection
$SqlConnection2.Connection String = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
# Write-Host "SqlConnection2.Connection String: " $SqlConnection2.Connection String
#Instantiates the Command Object
$SqlCmd2 = New-Object System.Data.SqlClient.SqlC ommand
#Command Text Using Variable
$SqlCmd2.CommandText = $SqlQuery2
#Create Connection
$SqlCmd2.Connection = $SqlConnection2
$SqlAdapter2 = New-Object System.Data.SqlClient.SqlD ataAdapter
$SqlAdapter2.SelectCommand = $SqlCmd2
$SqlConnection2.Close()
After this portion is complete, I want to update the 'Completed' field to 'True' for these same records. The problem is that it appears that the second SqlQuery that does the update is just being ignored.
1. If I run the same Update statement at the top of the script in place of the Select statement, it works fine.
2. Placing obivous errors in the Update statement when it's at the bottom of the script, such as a fake table name, doesn't even generate an error.
3. I've used write-host to display everything and it's showing the correct info.
Thanks,
JB
$SQLServer = "Server"
$SQLDBName = "database"
$AttachmentPath = "C:\file.csv"
# Select records to CSV that are older than than 90 days and have not been processd
$d = (Get-Date).adddays(-90).To
$SqlQuery = "SELECT * from Test WHERE DisableDate < '$d' AND Completed='False'"
$SqlConnection = New-Object System.Data.SqlClient.SqlC
$SqlConnection.ConnectionS
#Instantiates the Command Object
$SqlCmd = New-Object System.Data.SqlClient.SqlC
#Command Text Using Variable
$SqlCmd.CommandText = $SqlQuery
#Create Connection
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlD
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
#Populate Hash Table
$objTable = $DataSet.Tables[0]
#Populate Hash Table, and export to CSV File
#$DataSet.Tables[0] | Export-Csv -NoTypeInformation $AttachmentPath
#Populate Hash Table, export to CSV, and change column headers
$DataSet.Tables[0] | select @{l='Alias'; e={$_.Username}} | Export-Csv -NoTypeInformation $AttachmentPath
$SqlConnection.Close()
$SqlQuery2 = "Update Test SET Completed='True' WHERE DisableDate < '$d' AND Completed='False'"
# Write-Host "SqlQuery2: " $SqlQuery2
# Write-Host "Check Date: " $d
$SqlConnection2 = New-Object System.Data.SqlClient.SqlC
$SqlConnection2.Connection
# Write-Host "SqlConnection2.Connection
#Instantiates the Command Object
$SqlCmd2 = New-Object System.Data.SqlClient.SqlC
#Command Text Using Variable
$SqlCmd2.CommandText = $SqlQuery2
#Create Connection
$SqlCmd2.Connection = $SqlConnection2
$SqlAdapter2 = New-Object System.Data.SqlClient.SqlD
$SqlAdapter2.SelectCommand
$SqlConnection2.Close()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok... so the answer was that I'd left the following two lines off the second portion of the script because I thought these two line pertained more to the setting up of the hash table.
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
Thanks Raj!!
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
Thanks Raj!!
ASKER
Not that it matters since these methods no longer work with exchange mail accounts. Still, it would seem that there would be a more efficient way.
If anyone can elaborate on how do this within the same script, I'd prefer that over having multiple scripts to do one task.
Thanks!!