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.
Very interesting – I had not seen this. I’ll be interested to hear in the status for this.
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()
}
Whoa! Cool. I clearly have LOTS to learn.
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
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.
Yeah, his solution is pretty thorough.
Connect item marked as fixed.
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
Hmmm… If you’re using the latest, or even Post-2016, version of PowerShell, you shouldn’t be seeing the problem any more. This is very old stuff.
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\”