using-capture-mode.md

description	Using Capture Mode
title	Using Capture Mode | Microsoft Docs
ms.custom
ms.date	08/06/2017
ms.prod	sql
ms.prod_service	database-engine
ms.reviewer
ms.technology
ms.topic	reference
helpviewer_keywords	SQL Server Management Objects, capture mode capture mode [SMO] SMO [SQL Server], capture mode
ms.assetid	ace29bf0-705a-434f-82e4-db99d01c5008
author	markingmyname
ms.author	maghan
monikerRange	=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Using Capture Mode

[!INCLUDE SQL Server ASDB, ASDBMI, ASDW ]

SMO programs can capture and record the equivalent [!INCLUDEtsql] 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 xref:Microsoft.SqlServer.Management.Common.ServerConnection object, or by using the xref:Microsoft.SqlServer.Management.Smo.Server.ConnectionContext%2A property of the xref:Microsoft.SqlServer.Management.Smo.Server object.

Example

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 Create a Visual C# SMO Project in Visual Studio .NET.

Enabling Capture Mode in Visual Basic

This code example enables capture mode, and then displays the [!INCLUDEtsql] 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

Enabling Capture Mode in Visual C#

This code example enables capture mode, and then displays the [!INCLUDEtsql] 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;   
}