Powershell SMO Problem

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.

10 thoughts on “Powershell SMO Problem

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

  • Johan Parlevliet

    Thanks a lot for this script. I have change QueryTimeout to ConnectionTimeout so I could use almost the same command as I used for invoked-sqlcmd -ConnectionTimeout 0 (which has the bu

    Invoke-Sqlcmd2 -Query “select * from xxxx;” -Database xxxx -Server localhost -ConnectionTimeout 0

  • Johan Parlevliet

    Hi @Grant
    It is on a server with SQL Server 2014 Service Pack 2 (SP2) from 2016-07-11.
    De Invoke-Sqlcmd cmdlet is from Module SQLPS “C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\”

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.