Solved

Second half of Powershell Script Ignored

Posted on 2013-01-31
3
279 Views
Last Modified: 2013-02-01
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).ToString('yyyy-MM-dd')
$SqlQuery = "SELECT * from Test WHERE DisableDate < '$d' AND Completed='False'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

#Instantiates the Command Object
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

#Command Text Using Variable
$SqlCmd.CommandText = $SqlQuery

#Create Connection
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$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.SqlConnection
$SqlConnection2.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

# Write-Host "SqlConnection2.ConnectionString: " $SqlConnection2.ConnectionString

#Instantiates the Command Object
$SqlCmd2 = New-Object System.Data.SqlClient.SqlCommand

#Command Text Using Variable
$SqlCmd2.CommandText = $SqlQuery2

#Create Connection
$SqlCmd2.Connection = $SqlConnection2

$SqlAdapter2 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter2.SelectCommand = $SqlCmd2

$SqlConnection2.Close()
0
Comment
Question by:JB4375
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Rajitha Chimmani earned 500 total points
ID: 38840511
Try to run the second set of actions to update in a separate script. Run the select script first and then update script. I guess its the problem with SQL connections to the table. I see you are closing the SQL connections. Still try running separately and see how it goes.
0
 
LVL 1

Author Comment

by:JB4375
ID: 38840571
Yeah.... that was my next step. I've got VBScripts that are 500 lines plus with a half dozen sub routines.

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!!
0
 
LVL 1

Author Closing Comment

by:JB4375
ID: 38843545
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!!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now