Powershell SMO Problem

April 16, 2010 at 10:14 am (PowerShell, SQL Server 2008, SQLServerPedia Syndication) (, , , , , , )


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.

6 Comments

  1. Buck Woody said,

    Very interesting – I had not seen this. I’ll be interested to hear in the status for this.

  2. Chad Miller said,

    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()
    }

  3. scarydba said,

    Whoa! Cool. I clearly have LOTS to learn.

  4. Chad Miller said,

    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

  5. Bill said,

    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.

  6. scarydba said,

    Yeah, his solution is pretty thorough.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: