Powershell SMO Problem
April 16, 2010 at 10:14 am (PowerShell, SQL Server 2008, SQLServerPedia Syndication) (bug, connect, enterprise policy management framework, microsoft connect, pbm, policy based management, PowerShell)
We’ve been running the Enterprise Policy Management tools available from Codeplex for a few months now (Thanks to Buck Woody’s (blog | twitter) session at the PASS Summit). They’re honestly great. It’s a fantastic way to use Policy Based Management on 2000 and 2005 servers. We did hit some issues with timeouts and looking at the script, it made a call to invoke-sqlcmd, but didn’t pass the -querytimeout value. That means it default to 30 seconds and the import to database process was taking more than a minute for some of our queries. I did a little looking around and decided to just disable the timeout by passing a value of zero (0). But, I still got timeouts. Finally, after a bit of searching around, I found a closed (because it was posted in the wrong place) Connect item. It’s pretty simple to test. If you want to see a good run, do this:
Invoke-Sqlcmd “waitfor delay ’00:00:29′” -Database master -ServerInstance SomeServer -Querytimeout 0
It’ll work fine. Change it to this:
Invoke-Sqlcmd “waitfor delay ’00:00:31′” -Database master -ServerInstance SomeServer -Querytimeout 0
You’ll get a timeout. I don’t know if this is a bug or by design, but it’s a bit of a pain that you can’t simply bypass the timeout. There is a max value (a huge max value) 65535, but what happens if I run a sql command that runs longer than that?. Please go and vote on the new Connect item.
Home of the Scary DBA 
Buck Woody said,
April 16, 2010 at 10:38 am
Very interesting – I had not seen this. I’ll be interested to hear in the status for this.
Chad Miller said,
April 16, 2010 at 11:27 am
You could implement your own Invoke-SqlCmd wiht a QueryTimeout that works…
function Invoke-Sqlcmd2
{
param(
[string]$ServerInstance,
[string]$Database,
[string]$Query,
[Int32]$QueryTimeout=30
)
$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString=”Server={0};Database={1};Integrated Security=True” -f $ServerInstance,$Database
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$ds.Tables[0]
$conn.Close()
}
scarydba said,
April 16, 2010 at 12:26 pm
Whoa! Cool. I clearly have LOTS to learn.
Chad Miller said,
April 16, 2010 at 1:09 pm
So do I–I wasn’t aware of the Invoke-sqlcmd bug until you pointed it out. BTW line wrapping PowerShell code in blog posts/comments is somewhat of a pain point. Here’s a link to the code on PoshCode script repository:
http://poshcode.org/1789
Bill said,
April 18, 2010 at 6:09 pm
30 second timeout is the default behaviour for the SqlCommand object http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx Lots of guessing here but my suspicion is PS didn’t wire the parameter up correctly. Chad clearly seems to have the problem under control though so I’d use his solution until they fix it.
scarydba said,
April 19, 2010 at 7:43 am
Yeah, his solution is pretty thorough.