Invoke-SqlCmd QueryTimeout

Question

Does anyone know how to set Invoke-SqlCmd QueryTimeout more than 65535?

Microsoft said that they have fixed it in Denali but we are still using SQL 2008 R2 with latest service packs.

http://connect.microsoft.com/SQLServer/feedback/details/551799/invoke-sqlcmd-querytimeout-0-still-times-out

Basically, we are trying to backup or restore the database using powershell. Some of our databases are very large so it takes more than 65535 to complete the job.

Some suggested that we should use ADO.NET with timeout in powershell. But I wonder if we have any workaround for Invoke-SqlCmd...

Are you determined to use Invoke-SqlCmd? If you can't upgrade your client to SQL 2012, you could try working around this by falling back to executing SQLCMD directly using cmd /c or carrying out your backup/restore using SMO. — Ed Harper
– Ed Harper, Commented Nov 1, 2012 at 8:30
Setting the query timeout to zero should cause it to wait until the command completes no matter how long it takes. — JamieSee
– JamieSee, Commented Jun 12, 2017 at 18:06
The version of the PS module is what's important, not the version of the server. Recent versions of Windows/PowerShell allow you to Update-Module SqlServer and use -QueryTimeout 0. — brianary
– brianary, Commented Mar 6, 2019 at 18:38

Niranjan Dharmarajan · Accepted Answer · 2019-05-23 16:38:31Z

9

Invoke-Sqlcmd -query 'select * from largeDb' -QueryTimeout 0

-QueryTimeout 0 : will make your cmdlet from timing out.

AFAIK By soon, this bug will be resolved.

answered May 23, 2019 at 16:38

Niranjan Dharmarajan

2615 silver badges11 bronze badges

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

2 Comments

Max Power Over a year ago

Doesn't work for me when adding an index from a script. Still defaults to 60 seconds

Max Power Over a year ago

'IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_TableName_ColumnName') BEGIN CREATE NONCLUSTERED INDEX IX_TableName_ColumnName ON [dbo].[TableName] ([ColumnName]) INCLUDE ([ColumnName2],[ColumnName3]) END GO ' - That was in my SQL script.

Venkatakrishnan · Accepted Answer · 2016-10-30 03:41:33Z

You can use the below function to customize your own Query time out and session time out limits (while defining the parameters you can give you own desired values)

function Invoke-SqlCommand 
{ 
    [CmdletBinding()] 
    param( 
    [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance, 
    [Parameter(Position=1, Mandatory=$false)] [string]$Database, 
    [Parameter(Position=2, Mandatory=$false)] [string]$Query, 
    [Parameter(Position=3, Mandatory=$false)] [string]$Username, 
    [Parameter(Position=4, Mandatory=$false)] [string]$Password, 
    [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600, 
    [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15, 
    [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile, 
    [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow" 
    ) 

    if ($InputFile) 
    { 
        $filePath = $(resolve-path $InputFile).path 
        $Query =  [System.IO.File]::ReadAllText("$filePath") 
    } 

    $conn=new-object System.Data.SqlClient.SQLConnection 

    if ($Username) 
    { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout } 
    else 
    { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } 

    $conn.ConnectionString=$ConnectionString 

    #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller 
    if ($PSBoundParameters.Verbose) 
    { 
        $conn.FireInfoMessageEventOnUserErrors=$true 
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} 
        $conn.add_InfoMessage($handler) 
    } 

    $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) 
    $conn.Close() 
    switch ($As) 
    { 
        'DataSet'   { Write-Output ($ds) } 
        'DataTable' { Write-Output ($ds.Tables) } 
        'DataRow'   { Write-Output ($ds.Tables[0]) } 
    } 

}

Hope it HElps.

Darin Strait · Accepted Answer · 2012-11-01 13:26:09Z

0

You can write your own version of Invoke-SqlCmd that directly uses the System.Data.SqlClient object and do anything that you want to with it. There are a bunch of examples of how to do this to be found, including invoke-sqlcmd2, which was specifically written to get around the QueryTimeout bug and is hosted on Microsoft's scripting gallery. If you don't want to deploy such a script, you can just integrate the relevant code directly into your backup script.

Alternatively, you should be able to use SMO to backup the database. IIRC, the querytimeout bug does not affect SMO.

answered Nov 1, 2012 at 13:26

Darin Strait

3162 silver badges6 bronze badges

2 Comments

Chad Miller Over a year ago

SMO query timeout which includes backups is 10 minutes by default. If you want longer you need to set it.

Jeremy Thompson Over a year ago

Hey, 6 years later is there anything better? MS has updated SQLPS to $moduleName = "SqlServer" ... Import-Module $moduleName -Verbose yet still I'm getting a time out on a 10MB dB.

Collectives™ on Stack Overflow

Invoke-SqlCmd QueryTimeout

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related