Search code examples
sql-servervb.netsmo

How do I set the timeout for the execution of a set of statements when using the SQL Server SMO libraries?

I have the following bit of code that is timing out when I pass it a long running set of SQL statements. I'm using the Microsoft.SqlServer.Management.Smo library from SQL Server 2008.

Using cn As SqlConnection = GetConnection(_destinationCNS, True)

            Dim smoServer As New Microsoft.SqlServer.Management.Smo.Server(New ServerConnection(cn))
            Dim db As Microsoft.SqlServer.Management.Smo.Database = smoServer.Databases(_destinationDBName)

            db.ExecuteNonQuery(_script)

        End Using

I'm having trouble figuring out where to declare that timeout duration. What object and property should I be setting the timeout duration for the statement execution, not the connection timeout. On the SQLCommand object there is a convenient CommandTimeout property but I'm not seeing anything like that here.

Solution

  • Have you tried to set the ConnectionContext.StatementTimeout on the server object?

    smoServer.ConnectionContext.StatementTimeout = 6000;

    That would be 100 minutes.