Exec SP on Linked server and put this in temp table

Question

Need help on the following issue:

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

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

Result: successful execution

Case2 : i If the same stored procedure is called from another server using the linked server as follows:

 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 completed because the OLEDB provider "SQLNCLI" for linkedserver "Server2_Database2" could not start the distributed transaction.

Case 3

But when you try to execute the stored procedure separately [without inserting a temporary table], as shown below

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

Result: a stored procedure is executed without any errors and data return.

---

I forgot to mention that I am using SQL Server 2005. According to the server administrator, the function that you proposed to use is not available in 2005.

Answer 1

You have (I think) two options:

• To avoid using MSDTC (and all these MSDTC things related to distributed transactions) using the OPENQUERY rowset function

  / suppose (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 Enable Promotion Of Distributed Transaction linked server option to False to prevent a local transaction from promoting a distributed transaction and therefore using MSDTC:

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

   and your original request should work fine:

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

   

Answer 2

You can generally avoid Linked Servers. You can create a SQLCLR stored procedure that makes a standard connection to a remote instance (i.e. Database1).

Below is the C # code for the SQLCLR stored procedure, which is:

• allows you to specify an optional database name. If empty, the current database will be the default database or if it will be changed to this database after connecting (so the current database may be different from the default database)

• allows you to optionally use avatar. Without impersonation (default behavior), connections are made using the Windows login that the SQL Server service is running on (that is, the "Logon" account on the "Services"). This may not be desirable since it usually provides a higher level of permissions than the caller usually has. Using impersonation will maintain the security context of the login that executes the stored procedure, if that login is associated with a Windows login. Logging on to SQL Server does not have a security context and therefore will receive an error when trying to use impersonation.

  The ability to enable and disable impersonation in the code given here is for testing purposes only, so it’s easier to see the differences between using impersonation and not using it. When using this code in a real project, there would usually be no reason to allow the end user (i.e., the caller) to change the setting. It's generally safer to use impersonation. But the main difficulty of using impersonation is that it is limited to the local machine, unless Windows is allowed to enter Active Directory.

• must be created on the instance that will call Server1 : Server2 in Database2

• PERMISSION_SET of EXTERNAL_ACCESS required. This is best used:

  • signing an assembly in Visual Studio
  • in [master] , create an asymmetric key from a DLL
  • in [master] , create a login from this new asymmetric key
  • Grant EXTERNAL ACCESS ASSEMBLY permission for a new key-based login
  • in [Database2] do the following:
    ALTER ASSEMBLY [NoLinkedServer] WITH PERMISSION_SET = EXTERNAL_ACCESS;

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

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

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

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

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; } }