Export (0) Print
Expand All

Using Capture Mode

SMO programs can capture and record the equivalent Transact-SQL statements issued by the program in place of, or in addition to, the statements that are executed by the program. You enable capture mode by using the ServerConnection object, or by using the ConnectionContext property of the Server object.

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see "How to: Create a Visual Basic SMO Project in Visual Studio .NET" or "How to: Create a Visual C# SMO Project in Visual Studio .NET" in SQL Server Books Online.

This code example enables capture mode, and then displays the Transact-SQL commands held in the capture buffer.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Set the execution mode to CaptureSql for the connection.
srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql
'Make a modification to the server that is to be captured.
srv.UserOptions.AnsiNulls = True
srv.Alter()
'Iterate through the strings in the capture buffer and display the captured statements.
Dim s As String
For Each s In srv.ConnectionContext.CapturedSql.Text
    Console.WriteLine(s)
Next
'Execute the captured statements.
srv.ConnectionContext.ExecuteNonQuery(srv.ConnectionContext.CapturedSql.Text)
'Revert to immediate execution mode. 
srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql


This code example enables capture mode, and then displays the Transact-SQL commands held in the capture buffer.

{ 
// Connect to the local, default instance of SQL Server. 
Server srv; 
srv = new Server(); 
// Set the execution mode to CaptureSql for the connection. 
srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql; 
// Make a modification to the server that is to be captured. 
srv.UserOptions.AnsiNulls = true; 
srv.Alter(); 
// Iterate through the strings in the capture buffer and display the captured statements. 
string s; 
foreach ( String p_s in srv.ConnectionContext.CapturedSql.Text ) { 
   Console.WriteLine(p_s); 
} 
// Execute the captured statements. 
srv.ConnectionContext.ExecuteNonQuery(srv.ConnectionContext.CapturedSql.Text); 
// Revert to immediate execution mode. 
srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql; 
}

Community Additions

ADD
Show:
© 2014 Microsoft