Exec SP on Linked server and put that in temp table

Question

Need some help on the below issue:

Case 1 : stored procedure is on server 1 - call is from server1

declare @tempCountry table (countryname char(50))
insert into @tempCountry
    exec [database1_server1].[dbo].[getcountrylist]
Select * from @tempCountry

Result: successful execution

Case2 : iIf this same stored procedure is being called from a different server using linked server like this :

declare @tempCountry table (countryname char(50))
insert into @tempCountry
    exec [database2_server2].[database1_server1].[dbo].[getcountrylist]
Select * from @tempCountry

Result

Msg 7391, level 16, state 2, line 2
The operation could not be performed because OLEDB provider "SQLNCLI" for linkedserver "Server2_Database2" was unable to begin a distributed transaction.

Case 3

But when tried to execute the stored procedure separately [without temp table insertion] like below

exec [database2_server2].[database1_server1].[dbo].[getcountrylist]

Result: that is executing the stored procedure without any error and returning data.

---

I forgot to mention that am using SQL Server 2005. As per the server administrator, the feature you've suggested that I use is not available in 2005.

Answer 1

You have (I believe) two options here:

1. To try to avoid the usage of MSDTC (and all these not pleasant things related to Distributed Transactions) by using OPENQUERY rowset function

   /assume (here and below) that [database2_server2] is the name of the linked server/

   declare @tempCountry table (countryname char(50)) insert into @tempCountry select * from openquery([database2_server2], '[database1_server1].[dbo].[getcountrylist]') select * from @tempCountry

OR

2. You can set the linked server's option Enable Promotion Of Distributed Transaction to False in order to prevent the local transaction to promote the distributed transaction and therefore use of MSDTC:

   EXEC master.dbo.sp_serveroption @server = N'database2_server2', @optname = N'remote proc transaction promotion', @optvalue = N'false'

   and your original query should work fine:

   declare @tempCountry table (countryname char(50)) insert into @tempCountry exec [database2_server2].[database1_server1].[dbo].[getcountrylist] select * from @tempCountry

   

Answer 2

It is possible to avoid Linked Servers altogether. You can create a SQLCLR stored procedure that makes a standard connection to the remote instance (i.e. Database1).

The C# code below is for a SQLCLR Stored Procedure that:

• allows for an optional database name. If empty the current database will be the default database, or if provided it will change to that database after connecting (so that the current database can be different than the default database)

• allows for optionally using Impersonation. Without impersonation (the default behavior) the connections are made by the Windows Login that the SQL Server Service is running under (i.e. the "Log On As" account in "Services"). This might not be desired as it does typically provide an elevated level of permissions than the caller usually has. Using Impersonation will maintain the security context of the Login executing the stored procedure, if that Login is associated with a Windows Login. A SQL Server Login does not have a security context and will hence get an error if attempting to use Impersonation.

  The ability to toggle Impersonation on and off in the code provided here is for testing purposes so it is easier to see the differences between using Impersonation and not using it. When using this code in a real project, there usually would not be a reason to allow the end-user (i.e. the caller) to change the setting. It is generally safer to use Impersonation. But, the main difficulty in using Impersonation is that it is restricted to the local machine, unless the Windows Login is enabled for Delegation in Active Directory.

• should be created on the instance that will be calling Server1: Server2 in Database2

• requires a PERMISSION_SET of EXTERNAL_ACCESS. This is best handled by:

  • signing the Assembly in Visual Studio
  • in [master], create an Asymmetric Key from the DLL
  • in [master], create a Login from this new Asymmetric Key
  • GRANT the EXTERNAL ACCESS ASSEMBLY permission to the new Key-based Login
  • in [Database2], execute the following:
    ALTER ASSEMBLY [NoLinkedServer] WITH PERMISSION_SET = EXTERNAL_ACCESS;

• should be executed as:
  EXEC dbo.RemoteExec N'Server1', N'Database1', 0;

  and:
  EXEC dbo.RemoteExec N'Server1', N'Database1', 1;

  After each execution, run the following and pay attention to those first two fields:

  SELECT [login_name], [original_login_name], *
  FROM sys.dm_exec_sessions
  WHERE LEFT([program_name], 14) = N'Linked Server?';
  

The C# code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Security.Principal;
using Microsoft.SqlServer.Server;

public class LinkedServersSuck
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RemoteExec(
        [SqlFacet(MaxSize = 128)] SqlString RemoteInstance,
        [SqlFacet(MaxSize = 128)] SqlString RemoteDatabase,
                                  SqlBoolean UseImpersonation)
    {
        if (RemoteInstance.IsNull)
        {
            return;
        }

        SqlConnectionStringBuilder _ConnectionString =
            new SqlConnectionStringBuilder();
        _ConnectionString.DataSource = RemoteInstance.Value;
        _ConnectionString.Enlist = false;
        _ConnectionString.IntegratedSecurity = true;
        _ConnectionString.ApplicationName =
            "Linked Server? We don't need no stinkin' Linked Server!";

        SqlConnection _Connection =
            new SqlConnection(_ConnectionString.ConnectionString);
        SqlCommand _Command = new SqlCommand();
        _Command.CommandType = CommandType.StoredProcedure;
        _Command.Connection = _Connection;
        _Command.CommandText = @"[dbo].[getcountrylist]";

        SqlDataReader _Reader = null;
        WindowsImpersonationContext _SecurityContext = null;

        try
        {
            if (UseImpersonation.IsTrue)
            {
                _SecurityContext = SqlContext.WindowsIdentity.Impersonate();
            }

            _Connection.Open();

            if (_SecurityContext != null)
            {
                _SecurityContext.Undo();
            }

            if (!RemoteDatabase.IsNull && RemoteDatabase.Value != String.Empty)
            {
                // do this here rather than in the Connection String
                // to reduce Connection Pool Fragmentation
                _Connection.ChangeDatabase(RemoteDatabase.Value);
            }

            _Reader = _Command.ExecuteReader();

            SqlContext.Pipe.Send(_Reader);
        }
        catch
        {
            throw;
        }
        finally
        {
            if (_Reader != null && !_Reader.IsClosed)
            {
                _Reader.Close();
            }

            if (_Connection != null && _Connection.State != ConnectionState.Closed)
            {
                _Connection.Close();
            }
        }

        return;
    }
}