To control how generated statements are going to be executed, you need to set the SqlExecutionMode explicitly on the ConnectionContext object as follows: serverInstance.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql; This enumeration property has several possible values: ExecuteSql, CaptureSql, and ExecuteAndCaptureSql. The first value is set by default to execute all generated T-SQL statements immediately. The second value enables you to capture generated T-SQL in a special buffer where it can later be accessed, like this: Visual Basic .NETDim scriptBatches As StringCollection scriptBatches = serverInstance.ConnectionContext. CapturedSql.Text; C# .NETStringCollection scriptBatches = serverInstance.ConnectionContext.CapturedSql.Text; Tip The reason captured T-SQL is retrieved as a StringCollection is to avoid the need for parsing different batches, as each batch is represented by its own string entry in the collection. Actually, the SMO ServerConnection object enables you to execute a collection of batches directly as follows: serverInstance.ConnectionContext.ExecuteNonQuery (scriptBatches); To use StringCollection do not forget to add a reference to the System.Collections.Specialized namespace. Finally, the ExecuteAndCaptureSql option enables the user to run the DDL and capture it at the same time for examination and running later against a different server or via other means. |