### Example URN Filter Source: https://github.com/microsoft/sqlmanagementobjects/wiki/Refresh-vs-ClearAndInitialize An example of a URN (Uniform Resource Name) used as a filter query to select specific server objects, in this case, file tables. ```C# [@IsFileTable = 1] ``` -------------------------------- ### URN Filtering Example Source: https://github.com/microsoft/sqlmanagementobjects/wiki/Refresh-vs-ClearAndInitialize Demonstrates how to use the filterQuery parameter with an XPath-style URN to select specific objects, such as file tables, and load custom properties. ```APIDOC ## URN Filtering Example ### Description This example shows how to use the `ClearAndInitialize` method with a `filterQuery` to retrieve only file tables and load their `Name` and `FileTableDirectoryName` properties. ### Method `ClearAndInitialize(string filterQuery, IEnumerable extraFields)` ### Endpoint This method is typically called on a collection object, e.g., `database.Tables.ClearAndInitialize(...)` ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body - **filterQuery** (string) - "[@IsFileTable = 1]" - Filters for objects where the IsFileTable property is true. - **extraFields** (IEnumerable) - `new string[] { nameof(Table.Name), nameof(Table.FileTableDirectoryName) }` - Specifies that only the `Name` and `FileTableDirectoryName` properties should be loaded for each table. ### Request Example ```csharp var server = new Server("myserver"); var database = server.Databases["mydatabase"]; database.Tables.ClearAndInitialize("[@IsFileTable = 1]", new string[] { nameof(Table.Name), nameof(Table.FileTableDirectoryName) }); foreach (var table in database.Tables.Cast()) { Console.WriteLine($"Found file table {table.Name} using directory {table.FileTableDirectoryName}"); } ``` ### Response #### Success Response (200) After execution, the `database.Tables` collection will contain only file tables, and each `Table` object will have its `Name` and `FileTableDirectoryName` properties populated. ``` -------------------------------- ### Clone Transition Event Subclasses Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Enumerates subclass types for clone transition events, including install, uninstall, lock, commit, activate, and deactivate operations. ```c // Event sub class for clone transition enum CloneTransitionEventSubClass { CLONE_TRANSITION_INSTALL = 1, CLONE_TRANSITION_UNINSTALL = 2, CLONE_TRANSITION_LOCK = 3, CLONE_TRANSITION_COMMIT = 4, CLONE_TRANSITION_ACTIVATE = 5, CLONE_TRANSITION_DEACTIVATE = 6, }; ``` -------------------------------- ### Configure Database Collection Generation Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/src/Codegen/README.md Example configuration for generating a 'Database' collection. This specifies the mapped type variable, namespace, key type, collection template, parent object, and remaining macros for code generation. ```xml database Microsoft.SqlServer.Management.Smo string $(SmoDirectory)\generic_collection.cs Server /DSEALED /DDATABASE /DITEM_BY_ID ``` -------------------------------- ### Configure ColumnEncryptionKeyValue Collection Generation Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/src/Codegen/README.md Example configuration for generating a 'ColumnEncryptionKeyValue' collection using a specific template. This configuration defines the mapped type, namespace, key type, template path, parent, and remaining macros. ```xml columnEncryptionKeyValue Microsoft.SqlServer.Management.Smo int $(SmoDirectory)\columnencryptionkeyvalue_generic_collection.cs ColumnEncryptionKey /DSEALED ``` -------------------------------- ### Initialize SMO Hierarchy in Design Mode Source: https://github.com/microsoft/sqlmanagementobjects/wiki/Design-Mode Use this code to initialize a SMO hierarchy in design mode. Setting ServerVersion allows setting Database.Parent without a server query to get the version. Set TrueName to ensure the URN that identifies the Server is complete. Design mode requires an offline connection. ```C# using Microsoft.SqlServer.Management.Sdk.Sfc; using Microsoft.SqlServer.Management.Smo; // setting ServerVersion allows setting Database.Parent without a server query to get the version // set TrueName to ensure the URN that identifies the Server is complete var serverConnection = new ServerConnection() { ServerVersion = new ServerVersion(15, 0), TrueName="designMode" }; var server = new Management.Smo.Server(serverConnection); // design mode requires an offline connection (server as ISfcHasConnection).ConnectionContext.Mode = SfcConnectionContextMode.Offline; var database = new Database() { Parent = server }; ``` -------------------------------- ### Example URN for SMO Object Hierarchy Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/README.md Illustrates the Uniform Resource Name (URN) format used to identify objects within the SQL Server hierarchy in SMO. This format is crucial for navigating and referencing specific database components. ```plaintext Server/Database[@Name='mydatabase']/Table[@Name='mytable'] ``` -------------------------------- ### Full-Text Event Classes Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Constants for full-text indexing events, covering crawl start, end, and error conditions. ```C# FULLTEXT_CRAWL_START_EVENT_CLASS = 155, // nimishk FULLTEXT_CRAWL_END_EVENT_CLASS = 156, // nimishk FULLTEXT_CRAWL_ERROR_EVENT_CLASS = 157, // nimishk ``` -------------------------------- ### GetChildType Role Mapping Example Source: https://github.com/microsoft/sqlmanagementobjects/wiki/The-road-to-Ahead-of-Time-optimization-(AoT) An example from the `GetChildType` function's switch statement showing how it maps the URN name 'Role' to 'ServerRole' or 'DatabaseRole' based on the parent name. ```C# Type GetChildType { ... case "Role": if (parentName == "Server") { realTypeName = "ServerRole"; } else { realTypeName = "DatabaseRole"; } break; ... ``` -------------------------------- ### Error Handling: Catching ExecutionFailureException Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md This example shows how to handle SQL execution errors using a try-catch block. ExecutionFailureException wraps the underlying SqlException, providing details about the error that occurred on the server. ```csharp try { await serverConnection.ExecuteNonQueryAsync("INVALID SQL"); } catch (ExecutionFailureException ex) { // Contains SqlException details SqlException sqlEx = ex.InnerException as SqlException; Console.WriteLine($"Error {sqlEx?.Number}: {sqlEx?.Message}"); } ``` -------------------------------- ### SMO URN Examples for WideWorldImporters Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/examples/GetSmoObject/README.md Examples of Uniform Resource Names (URNs) used to identify specific database objects like tables and stored procedures within the WideWorldImporters database. ```plaintext Server/Database[@Name='WideWorldImporters']/Table[@Name='SystemParameters' and @Schema='Application'] ``` ```plaintext Server/Database[@Name='WideWorldImporters']/StoredProcedure[@Name='AddRoleMemberIfNonexistent' and @Schema='Application'] ``` -------------------------------- ### AzureDevOpsFederatedTokenCredential Setup in C# Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/src/FunctionalTest/Identity/README.md Configure a `SecretClient` with a `ChainedTokenCredential` that includes `AzureDevOpsFederatedTokenCredential`. This allows authentication using federated enterprise application credentials from Azure DevOps, falling back to `DefaultAzureCredential` and client certificates. ```csharp // prefer local user on dev machine over the certificate var credentials = new List() { new DefaultAzureCredential()}; foreach (var thumbprint in CertificateThumbprints ?? Enumerable.Empty()) { var certificate = FindCertificate(thumbprint); if (certificate != null) { credentials.Add(new ClientCertificateCredential(AzureTenantId, AzureApplicationId, certificate)); } break; } credentials.Add(new AzureDevOpsFederatedTokenCredential(new AzureDevOpsFederatedTokenCredentialOptions() { TenantId = AzureTenantId, ClientId = AzureApplicationId })); var credential = new ChainedTokenCredential(credentials.ToArray()); secretClient = new SecretClient(new Uri($"https://{KeyVaultName}.vault.azure.net"), credential); ``` -------------------------------- ### Using ClearAndInitialize to Fetch File Tables Source: https://github.com/microsoft/sqlmanagementobjects/wiki/Refresh-vs-ClearAndInitialize Demonstrates how to use ClearAndInitialize to fetch only file tables and populate specific properties like Name and FileTableDirectoryName. ```C# var server = new Server("myserver"); // the indexer implicitly initializes the Databases collection with only the Name and ID properties var database = server.Databases["mydatabase"]; // ClearAndInitialize fetches only file tables with the given properties prepopulated database.Tables.ClearAndInitialize("[@IsFileTable = 1]", new string[] {nameof(Table.Name), nameof(Table.FileTableDirectoryName)}); foreach (var table in database.Tables.Cast
()) { Console.WriteLine($"Found file table {table.Name} using directory {table.FileTableDirectoryName}"); } ``` -------------------------------- ### ETraceStatus Enum Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Enumeration defining the possible statuses of a trace: Stopped, Started, Close, and Pause. Used to represent the current state of a trace. ```c++ enum ETraceStatus { eTraceStopped, eTraceStarted, eTraceClose, eTracePause, }; ``` -------------------------------- ### SMO Transaction Support with Async Methods Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Shows how to manage transactions using SMO's asynchronous methods. Async methods correctly respect the existing transaction context when called within a transaction. ```csharp // Async methods respect existing transaction context serverConnection.BeginTransaction(); try { await serverConnection.ExecuteNonQueryAsync("INSERT INTO Test VALUES (1)"); await serverConnection.ExecuteNonQueryAsync("INSERT INTO Test VALUES (2)"); serverConnection.CommitTransaction(); } catch { serverConnection.RollBackTransaction(); throw; } ``` -------------------------------- ### Migrate Sync SMO to Async SMO Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Demonstrates the migration from synchronous SMO methods (e.g., `ExecuteNonQuery`) to their asynchronous counterparts (e.g., `ExecuteNonQueryAsync`). Note the change in return type for `ExecuteWithResultsAsync` from `DataSet` to `DataTable`. ```csharp // Before (Sync): int rows = serverConnection.ExecuteNonQuery("INSERT INTO Test VALUES (1)"); DataSet results = serverConnection.ExecuteWithResults("SELECT * FROM Test"); object scalar = serverConnection.ExecuteScalar("SELECT COUNT(*) FROM Test"); ``` ```csharp // After (Async): int rows = await serverConnection.ExecuteNonQueryAsync("INSERT INTO Test VALUES (1)"); DataTable results = await serverConnection.ExecuteWithResultsAsync("SELECT * FROM Test"); // Note: DataTable not DataSet object scalar = await serverConnection.ExecuteScalarAsync("SELECT COUNT(*) FROM Test"); ``` -------------------------------- ### ENUM_TRACE_DATA_TYPES Enum Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Enumeration defining the data types used in SQL traces, corresponding to XVT_* values. Includes integer, datetime, byte arrays, strings, and GUIDs. ```c++ typedef enum ETraceDataTypes { TRACE_I4 = 1, // XVT_I4 TRACE_DATETIME, // XVT_SSDATE TRACE_I8, // XVT_I8 TRACE_BYTES, // XVT_SSBYTES TRACE_WSTR, // XVT_VARWSTR TRACE_NTEXT, // XVT_NTEXT TRACE_GUID, // XVT_SSGUID } ENUM_TRACE_DATA_TYPES; ``` -------------------------------- ### MatrixDB Clone Refresh Event Subclasses Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Defines subclass identifiers for MatrixDB clone refresh events, covering start, transition, scan, batch, and end phases, as well as errors. ```c // Event subclass for MatrixDB Clone Refresh #define CLONE_REFRESH_START_SUBCLASS 1 #define CLONE_REFRESH_TRANSITION_START_SUBCLASS 2 #define CLONE_REFRESH_TRANSITION_END_SUBCLASS 3 #define CLONE_REFRESH_SCAN_START_SUBCLASS 4 #define CLONE_REFRESH_SCAN_END_SUBCLASS 5 #define CLONE_REFRESH_BATCH_START_SUBCLASS 6 #define CLONE_REFRESH_DELETED_STALE_ROWS_SUBCLASS 7 #define CLONE_REFRESH_BATCH_END_SUBCLASS 8 #define CLONE_REFRESH_END_SUBCLASS 9 #define CLONE_REFRESH_ERROR_SUBCLASS 10 ``` -------------------------------- ### SMO Statement Splitting with GO Batches Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Illustrates how SMO automatically splits scripts containing `GO` batches into separate commands when executing asynchronously. ```csharp // GO batches are automatically split string script = @" CREATE TABLE Test1 (Id INT) GO CREATE TABLE Test2 (Id INT) GO "; await serverConnection.ExecuteNonQueryAsync(script); // Executes as two separate commands ``` -------------------------------- ### Rollback and Begin Transaction Event Types Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trcdef.txt Array of USHORT values for 'Rollback and Begin' transaction sub-events. This helps in identifying and grouping rollback-related trace events. ```c++ const USHORT x_rgusEventsRollbackAndBeginTranType[] = { PRE_XACTEVENT_ROLLBACK_TRAN_EVENT_CLASS, // 185 POST_XACTEVENT_ROLLBACK_TRAN_EVENT_CLASS,// 186 0, // keep this as the last entry. }; ``` -------------------------------- ### Captured SQL Mode Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Demonstrates how to capture SQL commands without executing them by setting the SqlExecutionModes to CaptureSql. ```APIDOC ## PUT /api/serverConnection/SqlExecutionModes ### Description Enables or disables specific SQL execution modes. When `SqlExecutionModes.CaptureSql` is enabled, commands are captured but not executed. ### Method PUT ### Endpoint /api/serverConnection/SqlExecutionModes ### Parameters #### Request Body - **mode** (SqlExecutionModes) - Required - The execution mode to set. Use `CaptureSql` to capture commands. ### Request Example ```json { "mode": "CaptureSql" } ``` ## GET /api/serverConnection/CapturedSql ### Description Retrieves the SQL commands that have been captured when `SqlExecutionModes.CaptureSql` is enabled. ### Method GET ### Endpoint /api/serverConnection/CapturedSql ### Response #### Success Response (200) - **capturedSql** (string) - The text of the captured SQL commands. #### Response Example ```json { "capturedSql": "SELECT 1" } ``` ``` -------------------------------- ### CONTROL_EVENTS Enum Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Enumeration for special control events within a trace, such as Start, Stop, Error, Skipped, No Operation, Pause, Header, and Rollover. These events manage the trace's lifecycle and data flow. ```c++ typedef enum { TRACE_START_EVENT = 0XFFFE, TRACE_STOP_EVENT = 0XFFFD, TRACE_ERROR_EVENT = 0XFFFC, TRACE_SKIPPED_EVENT = 0XFFFB, TRACE_NOP = 0XFFFA, TRACE_PAUSE_EVENT = 0xFFF9, TRACE_HEADER_EVENT = 0xFFF8, TRACE_ROLLOVER_EVENT = 0xFFF7, } CONTROL_EVENTS; ``` -------------------------------- ### Configure Endpoint Collection Generation with Partial Keyword Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/src/Codegen/README.md Configuration for generating an 'Endpoint' collection, utilizing the 'partial' keyword to allow for merged class definitions. This is useful for collections that require custom implementations. ```xml endpoint Microsoft.SqlServer.Management.Smo string $(SmoDirectory)\generic_collection.cs Server /DSEALED /DITEM_BY_ID /DPARTIAL_KEYWORD=partial ``` -------------------------------- ### Batch Execution Behavior Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Illustrates the behavior of executing batches of commands, particularly when cancellation occurs mid-batch. ```APIDOC ## POST /api/serverConnection/ExecuteNonQueryAsync (Batch with Cancellation) ### Description Executes a batch of SQL commands. If cancellation occurs, subsequent commands in the batch are not executed. No automatic rollback is performed. ### Method POST ### Endpoint /api/serverConnection/ExecuteNonQueryAsync ### Parameters #### Request Body - **commands** (List) - Required - The list of SQL commands to execute. - **cancellationToken** (CancellationToken) - Required - Token to monitor for cancellation requests. ### Request Example ```json { "commands": [ "INSERT INTO Test VALUES (1)", "WAITFOR DELAY '00:01:00'", "INSERT INTO Test VALUES (2)" ], "cancellationToken": "[CancellationToken object]" } ``` ### Response #### Error Response (499) - **OperationCanceledException** - Thrown if the operation is cancelled via the token. The first command may have succeeded, but subsequent commands are not executed. #### Note No automatic rollback is performed in case of cancellation. ``` -------------------------------- ### Safely Get Optional SMO Property Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/README.md When scripting SMO objects, use GetPropertyOptional to safely access properties that might not be set. Check the returned property's Value for null before scripting to avoid exceptions during object creation. ```csharp GetPropertyOptional(propName) ``` -------------------------------- ### System Process Definition Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Defines a constant to identify system processes. ```c // Is system process? #define SYSTEM_PROCESS 1 ``` -------------------------------- ### Error Handling Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Details how to handle exceptions, specifically `ExecutionFailureException`, which wraps `SqlException` for SQL-related errors. ```APIDOC ## POST /api/serverConnection/ExecuteNonQueryAsync (Error Handling) ### Description Demonstrates how to catch and handle SQL execution errors using a try-catch block. `ExecutionFailureException` is thrown for invalid SQL or other execution problems. ### Method POST ### Endpoint /api/serverConnection/ExecuteNonQueryAsync ### Parameters #### Request Body - **command** (string) - Required - The SQL command to execute. ### Request Example ```json { "command": "INVALID SQL" } ``` ### Response #### Error Response (500) - **ExecutionFailureException** - Thrown for SQL execution errors. The `InnerException` property contains the `SqlException`. #### Error Example ```json { "error": { "type": "ExecutionFailureException", "message": "SQL execution failed.", "innerException": { "type": "SqlException", "number": 102, "message": "Incorrect syntax near 'INVALID'." } } } ``` ``` -------------------------------- ### Scripting Preferences for Existence Check Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/README.md Demonstrates how scripting preferences, specifically `IncludeScripts.ExistenceCheck`, can affect the behavior of methods like `ScriptCreate` and `ScriptDrop`. This allows for the inclusion of existence checks in generated DDL. ```csharp ScriptCreate ScriptDrop ``` -------------------------------- ### SMO Handling Multiple Result Sets with ExecuteReaderAsync Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Demonstrates how to use `ExecuteReaderAsync` and `NextResultAsync` to process multiple result sets returned from a single query. ```csharp // Use ExecuteReaderAsync for multiple result sets string query = "SELECT 1 AS Col1; SELECT 2 AS Col2"; using (var reader = await serverConnection.ExecuteReaderAsync(query)) { // First result set while (await reader.ReadAsync()) { Console.WriteLine(reader.GetInt32(0)); } // Move to next result set await reader.NextResultAsync(); // Second result set while (await reader.ReadAsync()) { Console.WriteLine(reader.GetInt32(0)); } } ``` -------------------------------- ### Commit and Begin Transaction Event Types Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trcdef.txt Array of USHORT values for 'Commit and Begin' transaction sub-events. Used for categorizing specific transaction-related trace events. ```c++ const USHORT x_rgusEventsCommitAndBeginTranType[] = { PRE_XACTEVENT_COMMIT_TRAN_EVENT_CLASS, // 185 POST_XACTEVENT_COMMIT_TRAN_EVENT_CLASS,// 186 0, // keep this as the last entry. }; ``` -------------------------------- ### GetSmoObject Application Usage Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/examples/GetSmoObject/README.md Command-line usage for the GetSmoObject application. Specify the server name, database name, and a URN to identify the object for scripting. ```bash GetSmoObject serverName databaseName "Server/Database[@Name='databaseName']/Table[@Name='tableName' and Schema='schemaName']" ``` -------------------------------- ### ClearAndInitialize Method Source: https://github.com/microsoft/sqlmanagementobjects/wiki/Refresh-vs-ClearAndInitialize The ClearAndInitialize method is an alternative to implicit initialization and recreation via Refresh. It clears old objects and initializes the collection, replacing existing objects with new versions. This method ensures all objects in the collection have a complete set of desired properties. ```APIDOC ## ClearAndInitialize Method ### Description Clears old objects and initializes the collection. Unlike Refresh(), any objects already listed in the collection will be replaced with new versions. Use this method to assure all the objects in the collection have the complete set of properties you want. ### Method `ClearAndInitialize(string filterQuery, IEnumerable extraFields)` ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body - **filterQuery** (string) - Optional - An XPath-style expression to filter the objects by properties. If null or empty, no filter is applied. - **extraFields** (IEnumerable) - Optional - A list of fields to be loaded for each object. If null or empty, only default fields are included. ### Request Example ```csharp // Example usage for filtering tables and loading specific properties var server = new Server("myserver"); var database = server.Databases["mydatabase"]; database.Tables.ClearAndInitialize("[@IsFileTable = 1]", new string[] { nameof(Table.Name), nameof(Table.FileTableDirectoryName) }); ``` ### Response #### Success Response (200) This method does not return a value directly but modifies the collection it is called upon. #### Response Example None ``` -------------------------------- ### Helper Methods for Async Operations Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md This snippet shows signatures for internal helper methods used in asynchronous SQL operations, including populating DataTables, handling exceptions, and validating databases. ```csharp // Async DataTable population from SqlDataReader private async Task PopulateDataTableAsync( SqlCommand command, CancellationToken cancellationToken) // Async exception handling with retry private async Task<(bool shouldReturn, object result)> HandleExecuteExceptionAsync( SqlException exc, ExecuteTSqlAction action, object execObject, bool catchException, CancellationToken cancellationToken) // Async database validation private async Task IsDatabaseValidAsync( SqlConnection sqlConnection, string dbName, CancellationToken cancellationToken) ``` -------------------------------- ### Service Queue Activation Event Schema Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/eventsdef.txt Defines the schema for the ON_ACTIVATION event for service queues. This event is asynchronous only. ```C++ { ETYP_ON_ACTIVATION, x_eet_Activation, EOBJTYP_SVCQ, EFLAG_ASYNC_ONLY, L"QUEUE_ACTIVATION", EventTag_ObjectEvent | EventTag_DatabaseName | EventTag_SchemaName, NULL, 4, EGROUP_ALL, 0 } ``` -------------------------------- ### Trace Begin Record Data Structure Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Structure for the data associated with a trace begin record. It includes the event ID and record length. Note that 'u_lRecordLength' is unaligned and requires careful handling. ```c typedef struct TrcBeginRecordData { USHORT usEventId; LONG u_lRecordLength; // unaligned!! // We use MEMCOPY to prevent Win64 data misalignment fault. LONG LRecordLength() const; void SetRecordLength(LONG lRecordLength); } TrcBeginRecordData; ``` -------------------------------- ### ClearAndInitialize Method Signature Source: https://github.com/microsoft/sqlmanagementobjects/wiki/Refresh-vs-ClearAndInitialize Signature for the ClearAndInitialize method, which clears old objects and initializes the collection with optional filtering and extra fields. ```C# /// /// Clears old objects and initializes the collection. Unlike Refresh(), any objects already listed in the collection will be replaced with new versions. /// Use this method to assure all the objects in the collection have the complete set of properties you want. /// /// the xpath to filter the objects by properties /// (e.g. setting the filter to [(@IsSystemObject = 0)] will exclude the system objects from the result. /// By setting the parameter to null or empty string, no filter will be applied to the result /// the list of fields to be loaded in each object. /// (e.g. setting the extraFields to "new string[] { "IsSystemVersioned" })" when calling this method for TableCollection /// will include "IsSystemVersioned" property for each table object. /// By setting the parameter to null or empty array, only the default fields will be included in the result public void ClearAndInitialize(string filterQuery, IEnumerable extraFields) ``` -------------------------------- ### SMO Unit Test Pattern with Captured SQL Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md This unit test pattern uses `SqlExecutionModes.CaptureSql` to avoid needing a real database. It verifies that the correct SQL is captured and that the method returns the expected value in capture mode. ```csharp [TestMethod] public async Task MyAsyncTest() { var conn = new ServerConnection(new SqlConnection(connectionString)); conn.SqlExecutionModes = SqlExecutionModes.CaptureSql; // No real DB needed var result = await conn.ExecuteNonQueryAsync("SELECT 1"); Assert.AreEqual(0, result); // Captured mode returns 0 Assert.IsTrue(conn.CapturedSql.Text.Contains("SELECT 1")); } ``` -------------------------------- ### Defining a New Object's URN Suffix Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/README.md Shows how to define a static `UrnSuffix` property for a new SMO object. This suffix is used in constructing the object's URN and is essential for its identification within the SMO hierarchy. ```csharp public static string UrnSuffix => ""; ``` -------------------------------- ### Online Index Event Classes Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Constants for tracking the progress of online index operations. ```C# ONLINE_INDEX_PROGRESS_EVENT_CLASS = 190, // weyg ``` -------------------------------- ### Database Mirroring Event Classes Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Constants for database mirroring events, including connection status and state changes. ```C# DBMIRRORING_CONNECTION_EVENT_CLASS = 151, // remusr, steveli ``` ```C# DBMIRRORING_STATE_CHANGE_EVENT_CLASS = 167, // steveli ``` -------------------------------- ### Build SMO Collections Code Generation Project Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/src/Codegen/README.md Build the collections_codegen.proj project manually when a collection needs to be created or updated. This command initiates the code generation process. ```bash msbuild collections_codegen.proj ``` -------------------------------- ### Backup Tape Mount Event Subclasses Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Defines subclass identifiers for backup tape mount events, indicating request, completion, or cancellation. ```c // Event subclass definitions for BACKUP_TAPE_MOUNT_EVENT_CLASS #define BACKUP_TAPE_MOUNT_REQUEST_SUBCLASS 1 // mount for tape drive is pending operator intervention #define BACKUP_TAPE_MOUNT_COMPLETE_SUBCLASS 2 // mount for tape drive was completed successfully #define BACKUP_TAPE_MOUNT_CANCEL_SUBCLASS 3 // mount for tape drive was cancelled ``` -------------------------------- ### Include SQL Trace Schema Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/eventsdef.txt Includes the schema definitions for SQL Trace events. This directive should be placed after all regular event definitions. ```C++ #include "schema.inc" ``` -------------------------------- ### CREATE LOGIN Event Schema Definition Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/eventsdef.txt Defines the event schema for the CREATE LOGIN operation. This event applies to server objects and is transactional. ```C++ //----------------------------------------------- // CREATE LOGIN Event Schema //----------------------------------------------- { ETYP_ON_CREATELOGIN, x_eet_Create_Login, EOBJTYP_SERVER, EFLAG_SAME_TRANS, L"CREATE_LOGIN", EventTag_LoginEvent, NULL, 7, EGROUP_DDL_LOGIN, 0 }, ``` -------------------------------- ### V8 Trace File Header Structure Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Defines the structure for the header of a V8 trace file, including version, size, options, and column order/aggregation arrays. ```c typedef struct V8TrcFileHeader { long lVersion; ULONG lHdrSize; ULONG lOptions; USHORT ColumnOrder[TOTAL_COLUMNS_V8]; USHORT ColumnAggregation[TOTAL_COLUMNS_V8]; } V8TrcFileHeader; ``` -------------------------------- ### Trace Header Options Enum Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Enumeration for trace file header options, indicating events like file rollovers or the first file in a sequence. ```c enum eTraceHeaderOptions { eTraceFileRolledOver = 0x1, eTraceFirstFile = 0x2, }; ``` -------------------------------- ### Statement Performance Statistics Subclasses Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Enumerates subclass types for statement performance statistics, covering SQL, plans, and statistics for different object types. ```c // Subclass for perf stat event class enum STMT_PERFSTAT_SUBCLASSES { STMT_PERFSTAT_SQL_SUBCLASS = 0, STMT_PERFSTAT_SPPLAN_SUBCLASS = 1, STMT_PERFSTAT_BATCHPLAN_SUBCLASS = 2, STMT_PERFSTAT_STAT_SUBCLASS = 3, STMT_PERFSTAT_PROC_STAT_SUBCLASS = 4, STMT_PERFSTAT_TRIG_STAT_SUBCLASS = 5 }; ``` -------------------------------- ### Traced Options Data Structure Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Structure to hold traced options data, including various option flags, maximum size settings, and stop time. ```c typedef struct TracedOptionsData { BYTE betpOptions; ULONG u_ulOptions; // unaligned BYTE betpMaxSize; INT64 u_i64MaxSize; // unaligned BYTE betpStopTime; SYSTEMTIME stStopTime; ``` -------------------------------- ### Cancellation: Using CancellationTokenSource Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Demonstrates how to cancel an asynchronous SQL operation using a CancellationTokenSource. The operation can be cancelled after a specified delay or by external events. Catches OperationCanceledException for client-side cancellation or ExecutionFailureException if SQL Server reports cancellation. ```csharp var cts = new CancellationTokenSource(); cts.CancelAfter(5000); // Cancel after 5 seconds try { await serverConnection.ExecuteNonQueryAsync("WAITFOR DELAY '00:01:00'", cts.Token); } catch (OperationCanceledException) { // Query was cancelled } catch (ExecutionFailureException) { // SQL Server reported the cancellation } ``` -------------------------------- ### Transaction Event Classes Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Constants for tracking transaction events, including begin, commit, rollback, and save points, with pre and post event types. ```C# PRE_XACTEVENT_BEGIN_TRAN_EVENT_CLASS = 181, // pingwang, ganakris POST_XACTEVENT_BEGIN_TRAN_EVENT_CLASS = 182, // pingwang, ganakris PRE_XACTEVENT_PROMOTE_TRAN_EVENT_CLASS = 183, // pingwang, ganakris POST_XACTEVENT_PROMOTE_TRAN_EVENT_CLASS = 184, // pingwang, ganakris PRE_XACTEVENT_COMMIT_TRAN_EVENT_CLASS = 185, // pingwang, ganakris POST_XACTEVENT_COMMIT_TRAN_EVENT_CLASS = 186, // pingwang, ganakris PRE_XACTEVENT_ROLLBACK_TRAN_EVENT_CLASS = 187, // pingwang, ganakris POST_XACTEVENT_ROLLBACK_TRAN_EVENT_CLASS = 188, // pingwang, ganakris ``` ```C# PRE_XACTEVENT_SAVE_TRAN_EVENT_CLASS = 191, // pingwang, ganakris POST_XACTEVENT_SAVE_TRAN_EVENT_CLASS = 192, // pingwang, ganakris ``` -------------------------------- ### Constants and Global Variables Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Defines constants related to trace data sizes and an array of trace event information. ```APIDOC ## Constants and Global Variables ### Constants - `g_cbMaxTraceColumnSize (0x40000000)`: Maximum size of a trace column in bytes (1GB). - `x_cbMaxTVPSize (g_cbMaxTraceColumnSize/4)`: Maximum size of a TVP (Table-Valued Parameter) trace binary format. ### Global Variables - `g_rgTraceEventInfo`: An external array of `TRACE_EVENT_INFO` structures, mapping event classes to their information. ``` -------------------------------- ### Implicit Collection Initialization with SetDefaultInitFields Source: https://github.com/microsoft/sqlmanagementobjects/wiki/Refresh-vs-ClearAndInitialize Optimize collection enumeration by pre-fetching specific properties using Server.SetDefaultInitFields. This ensures that objects within the collection are populated with necessary properties in a single query, reducing subsequent queries during iteration. ```C# var server = new Server("myserver"); // the indexer implicitly initializes the Databases collection with only the Name and ID properties var database = server.Databases["mydatabase"]; // Ensure the IsFileTable and FileTableDirectoryName properties are fetched during collection initialization server.SetDefaultInitFields(typeof(Table), nameof(Table.Name), nameof(Table.IsFileTable), nameof(Table.FileTableDirectoryName)); // The enumerator implicitly initializes the Tables collection foreach (var table in database.Tables.Cast
()) { if (table.IsFileTable) { Console.WriteLine($"Found file table {table.Name} using directory {table.FileTableDirectoryName}"); } } ``` -------------------------------- ### Cancellation Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Shows how to cancel asynchronous SQL operations using a CancellationToken. ```APIDOC ## POST /api/serverConnection/ExecuteNonQueryAsync with Cancellation ### Description Demonstrates how to cancel an ongoing asynchronous SQL operation by passing a `CancellationToken`. ### Method POST ### Endpoint /api/serverConnection/ExecuteNonQueryAsync ### Parameters #### Request Body - **command** (string) - Required - The SQL command to execute. - **cancellationToken** (CancellationToken) - Required - Token to monitor for cancellation requests. ### Request Example ```json { "command": "WAITFOR DELAY '00:01:00'", "cancellationToken": "[CancellationToken object]" } ``` ### Response #### Error Response (499) - **OperationCanceledException** - Thrown if the operation is cancelled via the token. - **ExecutionFailureException** - Thrown if SQL Server reports the cancellation. ``` -------------------------------- ### Broker Connection Event Subclasses Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Defines subclass identifiers for broker connection events. ```c #define BROKER_CONNECTION_ACCEPT_SUBCLASS 6 #define BROKER_CONNECTION_SEND_ERROR_SUBCLASS 7 #define BROKER_CONNECTION_RECEIVE_ERROR_SUBCLASS 8 ``` -------------------------------- ### CREATE SERVERROLE Event Schema Definition Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/eventsdef.txt Defines the event schema for the CREATE SERVER ROLE operation. This event applies to server objects and is transactional. ```C++ //----------------------------------------------- // CREATE SERVERROLE Event Schema //----------------------------------------------- { ETYP_ON_CREATESERVERROLE, x_eet_Create_ServerRole, EOBJTYP_SERVER, EFLAG_SAME_TRANS, L"CREATE_SERVER_ROLE", EventTag_ServerEvent | EventTag_SID, NULL, BitCount64(EventTag_ServerEvent | EventTag_SID), EGROUP_DDL_SERVER_SECURITY, 0 }, ``` -------------------------------- ### Performance and Compilation Event Classes Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Constants for performance statistics, statement recompilations, and query plan compilation events. ```C# SHOWPLAN_XML_STATISTICS_EVENT_CLASS = 146, // pingwang, alexisb SQLOS_XML_DEADLOCK_EVENT_CLASS = 148, // alexverb TRACE_FILE_CLOSE_EVENT_CLASS = 150, // jayc ``` ```C# STMT_PERFSTAT_EVENT_CLASS = 165, // ganakris, jayc STMT_RECOMPILE_EVENT_CLASS = 166, // eriki, ganakris ``` ```C# SHOWPLAN_XML_COMPILE_EVENT_CLASS = 168, // alexisb, pingwang SHOWPLAN_ALL_COMPILE_EVENT_CLASS = 169, // alexisb, pingwang ``` -------------------------------- ### Define Singleton Supported Versions Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/src/Codegen/README.md Defines an enumeration for SQL Server version flags used to map server versions to supported properties. Update this enum when SQL Server versions are updated. ```C# private enum SingletonSupportedVersionFlags { NOT_SET = 0, v7_0 = 1, v8_0 = 2, v9_0 = 4, v10_0 = 8, v10_50 = 16, v11_0 = 32, v12_0 = 64, v13_0 = 128, v14_0 = 256, v15_0 = 512, v16_0 = 1024, } private static KeyValuePair[] m_SingletonSupportedVersion = { new KeyValuePair(new ServerVersion(7,0), (int)SingletonSupportedVersionFlags.v7_0), new KeyValuePair(new ServerVersion(8,0), (int)SingletonSupportedVersionFlags.v8_0), new KeyValuePair(new ServerVersion(9,0), (int)SingletonSupportedVersionFlags.v9_0), new KeyValuePair(new ServerVersion(10,0), (int)SingletonSupportedVersionFlags.v10_0), new KeyValuePair(new ServerVersion(10,50), (int)SingletonSupportedVersionFlags.v10_50), new KeyValuePair(new ServerVersion(11,0), (int)SingletonSupportedVersionFlags.v11_0), new KeyValuePair(new ServerVersion(12,0), (int)SingletonSupportedVersionFlags.v12_0), new KeyValuePair(new ServerVersion(13,0), (int)SingletonSupportedVersionFlags.v13_0), new KeyValuePair(new ServerVersion(14,0), (int)SingletonSupportedVersionFlags.v14_0), // The build number should probably be 65535 for all the above // However, that does not matter for two reasons: // - if there is another line after this one, we are safe: any M.m.b with b>0 observed in // in object xml files (the ones with the object definitions) will be considered // "supported" by matching the next entry. // - we rarely seem to rely on min_build/max_build attributes. new KeyValuePair(new ServerVersion(15,0,ushort.MaxValue), (int)SingletonSupportedVersionFlags.v15_0), new KeyValuePair(new ServerVersion(16,0,ushort.MaxValue), (int)SingletonSupportedVersionFlags.v16_0), }; ``` -------------------------------- ### Trace File Header Structure (Yukon) Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Defines the structure for the trace file header, commonly used in Yukon versions. Includes Unicode tag, header size, version, provider name, server name, and repeat base settings. ```c typedef struct TrcFileHeader { USHORT usUnicodeTag; // 0xFEFF (2) USHORT usHeaderSize; // sizeof(struct TrcFIleHeader) (4) USHORT usTraceVersion; // Trace file format version (6) WCHAR wszProviderName[128]; // L"Microsoft SQL Server" (262) WCHAR wszDefinitionType[64]; // Unused (390) BYTE bMajorVersion; // (391) BYTE bMinorVersion; // (392) USHORT usBuildNumber; // (394) ULONG u_ulHeaderOptions; // UNALLIGNED (398) WCHAR wszServer[128]; // server name (654) USHORT usRepeatBase; // spid (656) // We use MEMCOPY to prevent Win64 data misalignment fault. void SetHeaderOptions(ULONG ulHeaderOption); ULONG UlHeaderOptions() const; } TrcFileHeader; ``` -------------------------------- ### Maximum Trace File Path Definition Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Defines the maximum length for a trace file path on the server. Ensures file paths do not exceed server limitations. ```c++ #define MAX_TRACE_FILE_PATH 245 ``` -------------------------------- ### Performance Logging Configuration Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md When performance logging is enabled, the system logs query execution times, warns about long-running queries (over 5 seconds), and records timeout events. No specific code is shown for enabling this, but it's a configurable feature. ```csharp // Logs query execution time // Warns on long-running queries (>5 seconds) // Logs timeout events ``` -------------------------------- ### Trace Column Information Structure Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Defines the structure for trace column information, including its ID, data type, filterability, repeatability, and name. Note that 'bFilterable' must be false if 'eDataType' is TRACE_BYTES. ```c typedef struct trace_column_info { USHORT id; ETraceDataTypes eDataType; BYTE bFilterable; BYTE bRepeatable; BYTE bRepeatedBase; WCHAR* pwszColumnName; bool fPreFilterable:1; bool fMandatory:1; bool fDisabled:1; } TRACE_COLUMN_INFO; ``` -------------------------------- ### Initialize Child Level with Exception Handling in SMO Source: https://github.com/microsoft/sqlmanagementobjects/wiki/The-road-to-Ahead-of-Time-optimization-(AoT) This snippet attempts to retrieve a child collection. If an exception occurs (like `ArgumentException` or `InvalidCastException`), it sets a flag `isNonCollection` to true, indicating that the child might be a singleton. ```C# AbstractCollectionBase childColl = null; bool isNonCollection = false; try { childColl = GetChildCollection(currentSmoObject, levelFilter, filterIdx, GetServerObject().ServerVersion); } catch (Exception e) { // The old InitChildLevel caller just wants to throw here. It never handled singletons here anyhow. if (urnList == null) { throw; } if (!(e is ArgumentException || e is InvalidCastException)) { throw; } // We come here if we ask for a child collection for a level that isn't really a collection like singletons. // Since we still want to get into AdvanceInitRec with them, we need to process them differently. // Only do this for the Object Query case (urnList != null), not the old InitChildLevel cases. isNonCollection = true; } ``` -------------------------------- ### Performance Logging Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Information about performance logging, which tracks query execution times and warns about long-running queries or timeouts. ```APIDOC ## Performance Logging ### Description When enabled, performance metrics for SQL query executions are logged. This includes query execution time and warnings for long-running queries or timeouts. ### Logging Details - Logs the execution time of each query. - Issues warnings for queries exceeding a predefined threshold (e.g., 5 seconds). - Logs events related to query timeouts. ### Configuration Performance logging is typically enabled via configuration settings or specific API calls not detailed here. Refer to the main SQL Management Objects documentation for configuration details. ``` -------------------------------- ### LOGON Event Schema Definition Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/eventsdef.txt Defines the event schema for the LOGON event. This event applies to server objects, is transactional, and synchronous only. It captures login type, SID, client host, and pooling information. ```C++ //----------------------------------------------- // LOGON Event Schema //----------------------------------------------- { ETYP_ON_LOGON, x_eet_Logon, EOBJTYP_SERVER, EFLAG_SAME_TRANS | EFLAG_SYNC_ONLY, L"LOGON", EventTag_LoginType | EventTag_SID | EventTag_ClientHost | EventTag_IsPooled, NULL, 4, EGROUP_ALL, 0 }, ``` -------------------------------- ### Batch Execution Behavior with Cancellation Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/docs/ASYNC_API_REFERENCE.md Illustrates batch execution where cancellation occurs mid-batch. The first command executes successfully, but subsequent commands are not executed after cancellation. Note that there is no automatic rollback for partial batch execution. ```csharp var commands = new List { "INSERT INTO Test VALUES (1)", // Executes "WAITFOR DELAY '00:01:00'", // Gets cancelled "INSERT INTO Test VALUES (2)" // Does NOT execute }; var cts = new CancellationTokenSource(); cts.CancelAfter(500); try { await serverConnection.ExecuteNonQueryAsync(commands, cts.Token); } catch (OperationCanceledException) { // First INSERT succeeded, second INSERT never ran // No automatic rollback } ``` -------------------------------- ### ETraceOptions Enum and Macros Source: https://github.com/microsoft/sqlmanagementobjects/blob/main/SmoBuild/DdlEvents/trccomn.txt Enumeration for trace options that can be set on a server, including rowset destination, rollover, shutdown on error, and flight recorder mode. Macros are provided to extract normal and extended parts of the options. ```c++ enum ETraceOptions { eTraceDestRowset = 0x1, // data written to a rowset eTraceRollover = 0x2, // rollover files at some max file size eTraceShutdown = 0x4, // shutdown server on write error eTraceFlightRec = 0x8, // Trace is being used as the flight recorder. // Extended option occupies high word, and is sequential number. // Combined option would look like 0x10002 eTraceNoExtended = 0, // No extended option this time }; #define TRACEOPTIONS_NORMAL (eTraceDestRowset|eTraceRollover|eTraceShutdown|eTraceFlightRec) #define TRACEOPTIONS_EXTENDED_MAX (eTraceNoExtended) inline ULONG UlTraceOptionNormalPart(__in ULONG ulOptions) { return (ulOptions & 0xFFFF); } inline ULONG UlTraceOptionExtendedPart(__in ULONG ulOptions) { return (ulOptions >> 16); } inline BOOL FIsTraceOptionWithinValidRange(__in ULONG ulOptions) { return ((UlTraceOptionNormalPart(ulOptions) & (~TRACEOPTIONS_NORMAL)) == 0) && (UlTraceOptionExtendedPart(ulOptions) <= TRACEOPTIONS_EXTENDED_MAX); } ```