Apply Get Latest to stored procedures from a Linked Server

When using the Get Latest command in dbForge Source Control to update stored procedures in a local database with changes from a Linked Server that does not support distributed transactions, you may encounter the following error:

‘Stored_procedure_name’: The operation could not be performed because OLE DB provider “MSOLEDBSQL” for linked server “server_name” was unable to begin a distributed transaction. OLE DB provider “MSOLEDBSQL” for linked server “server_name” returned message “The partner transaction manager has disabled its support for remote/network transactions.” Located at: full_path_to_the_stored_procedure

where:

• Stored_procedure_name is the name of the stored procedure you want to update from a Linked Server.
• server_name is the Linked Server name.
• path_to_the_stored_procedure is the full path of the stored procedure in the local repository.

This occurs because dbForge Source Control defaults to the SERIALIZABLE transaction isolation level, while a Linked Server requires READ COMMITTED or READ UNCOMMITTED. The tool cannot automatically detect and adjust the required isolation level.

As a workaround to deploy this stored procedure, use Schema Compare for SQL Server where you can set up the transaction isolation level option.

The following example demonstrates the error and provides a workaround to resolve it.

Prerequisites

For demo purposes, the following has been prepared:

• A configured Linked Server - demo-mssql\SQLEXPRESS02.
• A database with a stored procedure - AdventureWorks2022.
• A cloned local copy of the remote repository - D:\AdventureWorks_GIT. For instructions on cloning a repository, see Clone the repository using the command line.
• A stored procedure (AddDepartment) that includes a reference to a Linked Server:

CREATE PROCEDURE AddDepartment
    @Name dbo.Name,
    @GroupName dbo.Name = NULL
AS
BEGIN
    SET NOCOUNT ON;
 
    INSERT INTO HumanResources.Department (Name, GroupName, ModifiedDate)
    VALUES (@Name, @GroupName, GETDATE());

    INSERT INTO [DBFSQLSRV\SQL2017].Company.dbo.Department (Name, GroupName, ModifiedDate)
    VALUES (@Name, @GroupName, GETDATE());
  
END;
GO

Worked example

The workflow is as follows:

• Reproduce the error
• Resolve the issue

Reproduce the error

1. Link the AdventureWorks2022 database to the repository - D:\AdventureWorks_GIT.

2. In the Remote changes section, select the checkbox next to the AddDepartment stored procedure and select Get Latest.

3. The Get Latest operation fails because the following error occurs:

4. To close the window, select OK.

The next step is to resolve the issue using dbForge Schema Compare for SQL Server as a workaround.

Resolve the issue

1. Launch dbForge Schema Compare for SQL Server.

2. Open the New Schema Comparison wizard by doing one of the following:

• On the toolbar, select New Schema Comparison.
• On the Start page, select New Schema Comparison.

3. On the Source and Target page of the wizard, specify the source and target details and then select Compare to run the schema comparison:

• Under Source, select Source Control as a source type and the D:\AdventureWorks_GIT repository.
• Under Target, select Database as a target type, the demo-mssql\SQLEXPRESS02 connection, and the AdventureWorks2022 database.

4. In the Schema Comparison document that opens, select the checkbox next to the AddDepartment stored procedure and select Synchronize objects to the target database.

5. In the New Schema Synchronization wizard that opens, switch to the Options page.

6. Under Transactions, select next to the Set transaction isolation level to SERIALIZABLE option.

7. In the Edit Value window that opens, select the READ COMMITTED transaction isolation level and select OK to save the changes.

8. The option is now Set transaction isolation level to READ COMMITTED.

9. Select Synchronize to run the schema synchronization and generate a synchronization script.

10. To run the script, on the toolbar, select Execute or press F5.

The script has been successfully executed as shown in the screenshot.

The AddDepartment stored procedure is created without an error.