在链接服务器上执行SP并将其放在临时表中

Need some help on the below issue:

需要一些关于以下问题的帮助:

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

情况1:存储过程在服务器1上 - 来自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 :

案例2:如果使用链接服务器从不同的服务器调用相同的存储过程,如下所示:

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.

消息7391,级别16,状态2,行2无法执行操作,因为Linkedserver“Server2_Database2”的OLEDB提供程序“SQLNCLI”无法开始分布式事务。

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.

我忘了提到我使用的是SQL Server 2005.根据服务器管理员的说法,我建议使用的功能在2005年不可用。

2 个解决方案

#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

   尝试通过使用OPENQUERY行集函数来避免使用MSDTC(以及与分布式事务相关的所有这些不愉快的事情)

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

   /假设(此处和下面)[database2_server2]是链接服务器的名称/

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

   声明@tempCountry表(countryname char(50))插入@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:

   您可以将链接服务器的选项Enable Promotion Of Distributed Transaction设置为False,以防止本地事务提升分布式事务,从而使用MSDTC:

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

   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

   声明@tempCountry表(countryname char(50))插入@tempCountry exec [database2_server2]。[database1_server1]。[dbo]。[getcountrylist] select * from @tempCountry

   

#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).

可以完全避免链接服务器。您可以创建一个SQLCLR存储过程,该过程与远程实例(即Database1)建立标准连接。

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

下面的C#代码适用于SQLCLR存储过程:

• 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.

  允许可选地使用模拟。在没有模拟(默认行为)的情况下,连接由运行SQL Server服务的Windows登录(即“服务”中的“登录身份”帐户)进行。这可能不是所希望的,因为它通常提供比调用者通常具有的更高级别的权限。如果Login与Windows登录相关联,则使用Impersonation将维护Login执行存储过程的安全上下文。 SQL Server登录没有安全上下文,因此在尝试使用模拟时会出错。

  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.

  在此处提供的代码中打开和关闭模拟的功能是出于测试目的,因此更容易看到使用模拟和不使用模拟之间的差异。在实际项目中使用此代码时,通常没有理由允许最终用户(即调用者)更改设置。使用模拟通常更安全。但是,使用模拟的主要困难在于它仅限于本地计算机,除非在Active Directory中为委派启用了Windows登录。

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

  应该在将在Database2中调用Server1:Server2的实例上创建

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

  需要一个EXTERNAL_ACCESS的PERMISSION_SET。最好通过以下方式处理:

  • signing the Assembly in Visual Studio

  在Visual Studio中签署程序集

  • in [master], create an Asymmetric Key from the DLL

  在[master]中,从DLL创建一个非对称密钥

  • in [master], create a Login from this new Asymmetric Key

  在[master]中,从这个新的非对称密钥创建一个登录

  • GRANT the EXTERNAL ACCESS ASSEMBLY permission to the new Key-based Login

  授予EXTERNAL ACCESS ASSEMBLY新的基于密钥的登录权限

  • in [Database2], execute the following:
    ALTER ASSEMBLY [NoLinkedServer] WITH PERMISSION_SET = EXTERNAL_ACCESS;

  在[Database2]中,执行以下命令:ALTER ASSEMBLY [NoLinkedServer] WITH PERMISSION_SET = EXTERNAL_ACCESS;

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

  应该执行如下:EXEC dbo.RemoteExec N'Server1',N'Database1',0;

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

  和: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:

C#代码:

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