Powershell SQL server update query

Question

I'm trying to connect to a Microsoft SQL Database and update any record that the changed field is = to 'x'. I'm able to query the database but when I attempt to do an update I get this error:

Fill : Exception calling "Fill" with "1" argument(s): "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

This is the script I'm using:

#Create SQL Connection
$con = new-object "System.data.sqlclient.SQLconnection"

#Set Connection String
$con.ConnectionString =(“Data Source=server;Initial Catalog=IDCards;Integrated Security=SSPI”)
$con.open()

$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.connection = $con
$sqlcmd.CommandTimeout = 600000
#$sqlcmd.CommandText = “select * from tblPhotoID where changed = 'X'”
$sqlcmd.CommandText = “UPDATE dbo.tblPhotoID SET Changed = '1' WHERE Changed ='X'”
$adapter = New-Object system.data.sqlclient.sqldataadapter ($sqlcmd.CommandText, $con)
$set = New-Object system.data.dataset
$adapter.Fill($set)

There are about 4000 records that would updated currently. The script runs about 30 secs before timing out. I've tried increasing the command timeout and gotten the same results.

What happens if you try to update just one record?

Frank Schmitt
– Frank Schmitt

2013-10-14 14:42:23 +00:00
Commented Oct 14, 2013 at 14:42 — Frank Schmitt
– Frank Schmitt, Commented Oct 14, 2013 at 14:42

score 25 · Accepted Answer · 2013-10-14 15:50:17Z

25

Your update statement is not going to return a recordset so there is nothing to fill the dataset. You instead want to try the following:

#Create SQL Connection
$con = new-object "System.data.sqlclient.SQLconnection"

#Set Connection String
$con.ConnectionString =(“Data Source=sb-inft-orange.ads.iu.edu;Initial Catalog=IDCards;Integrated Security=SSPI”)
$con.open()

$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.connection = $con
$sqlcmd.CommandTimeout = 600000
$sqlcmd.CommandText = “UPDATE dbo.tblPhotoID SET Changed = '1' WHERE Changed ='X'”
$rowsAffected = $sqlcmd.ExecuteNonQuery()

edited Oct 14, 2013 at 15:50

answered Oct 14, 2013 at 14:54

user121489

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Finch Over a year ago

That helped in that i'm not getting the timeout. But the update doesnt actually update any of the data in the database.

user121489 Over a year ago

I double checked the code on my machine (PowerShell 3.0) and I found that I needed to add a set of parentheses after the ExecuteNonQuery method call otherwise I got an error.

Sid · Accepted Answer · 2018-10-09 15:32:42Z

0

In your code, you have used $adapter.Fill($set) FYI, It is used to add rows in the DataSet to match those in the data source.Instead you can use $adapter.Update($Set) I think this will solve your problem. Also you can use $sqlcmd.ExecuteNonQuery()

#Create SQL Connection

$con = new-object "System.data.sqlclient.SQLconnection"
#Set Connection String
$con.ConnectionString =(“Data Source=sb-inft-orange.ads.iu.edu;Initial 
Catalog=IDCards;Integrated Security=SSPI”)
$con.open()

$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.connection = $con
$sqlcmd.CommandTimeout = 600000
$sqlcmd.CommandText = “UPDATE dbo.tblPhotoID SET Changed = '1' WHERE Changed ='X'”
$sqlcmd.ExecuteNonQuery()

edited Oct 9, 2018 at 15:32

answered Oct 9, 2018 at 14:59

Sid

1091 silver badge7 bronze badges

Comments

Obsidian Age · Accepted Answer · 2017-07-24 21:56:52Z

-1

$cn = New-Object System.Data.SqlClient.SqlConnection ( "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog.......)
$q = "select ...  from .. "
$da = New-Object System.Data.SqlClient.SqlDataAdapter($q, $cn)
$da.SelectCommand.CommandTimeout = 60

edited Jul 24, 2017 at 21:56

Obsidian Age

42.5k11 gold badges54 silver badges76 bronze badges

answered Jul 24, 2017 at 21:47

Dmitriy S.

1

1 Comment

GoldBishop Over a year ago

This is based on Select...does not work as OP was expecting for an Update. An action that inherently has no need for a DataAdapter or DataSet

Collectives™ on Stack Overflow

Powershell SQL server update query

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related