CODEWITHSUNDEEP
c#sql-serverpowershellsmo
Just a learner
Just a learner

Reputation: 28572

SQL Server SMO programming, where to set the CommandTimeout property?

I'm working on a PowerShell script with SMO to do some SQL Server programming. The simplified version of my script is listed below.

# Load SMO assembly, and if we're running SQL Server 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( "Microsoft.SqlServer.SMO");

if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | out-null;
}
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null;
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") ".\sql2014";
$server.Databases["master"].ExecuteWithResults("select * from sys.database_files");

My script now always times out and I googled around against the internet and found that I should set CommandTimeout property somewhere in my code.

However, the property CommandTimeout is for SqlCommand. The way I use SMO to execute query doesn't have a SqlCommand object. As you can see I can execute directly on $server.Databases["master"].

Can anyone help to point out where I should set the CommandTimeout property?

Upvotes: 1

Views: 915

Answers (1)

Jake Bruun
Jake Bruun

Reputation: 1323

$server.ConnectionContext.StatementTimeout should get the job done for you. As per TechNet this:

Gets or sets the number of seconds a statement is given to run before failing with a time-out error.

Upvotes: 2

Related Questions