使用包含xml max length的动态sql将sp结果插入临时表

问题描述：

以下示例sp是基于我的工作sp，我让您更容易理解，以更简单的方式找到解决方案。

1）第一个SP

Below sample sp's are based on my working sp's, i have made this easier for you to understand in a simpler way to find the solution.
1) First SP

CREATE PROCEDURE testsp
@doc varchar(max) AS

Declare @idoc int 

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *  FROM OPENXML (@idoc,'/OutLookContact/Contact',1)
          WITH (
                    FirstName varchar(50),
                    LastName varchar(50),
                    EmailAddress varchar(50)
                )
GO

2）第二个SP （在此sp中调用第一个sp）

2) Second SP ( calling first sp in this sp)

CREATE PROCEDURE testsp2
@login varchar(255),
@doc2 varchar(max)
as
create table #tbl_user_details
(
    id        varchar(1000) default (newid()),
    firstName varchar(255),
    lastName  varchar(255),
    EmailAddress varchar(255)
)

insert into #tbl_user_details (firstName,lastName,EmailAddress)
exec testsp @doc = @doc2

select *,@login as [login_name] from #tbl_user_details

Drop table #tbl_user_details
GO

现在我需要第二个sp插入带有输入子句的临时表作为xml具有超过8000个字符。但是我不能使用openrowset或openquery来做这件事。

我已经工作过并尝试了以下样本。

Now i need the second sp to insert in a temp table with input clause as xml have more than 8000 character. But I cant do this using openrowset or openquery.
I have worked and a tried below sample.

SELECT * into #tbl_user_details FROM OPENQUERY 
( vinsdt073, 
'SET NOCOUNT ON; exec innova..testsp2 @login = ''Ragul'',
 @doc2 = ''<OutLookContact>
            <Contact FirstName="Asif" LastName="Ghafoor" EmailAddress1="asifghafoor@my.web.pk" />
            <Contact FirstName="Rameez" LastName="Ali" EmailAddress1="rameezali@my.web.pk" />
            ---
            ---
            --- (More than 8000 char)
          </OutLookContact>''
WITH RESULT SETS
(
    (
        id        varchar(255),
        FirstName varchar(max),
        LastName  varchar(max),
        EmailAddress1  nvarchar(max),
        login_name varchar(255)
    )
);')

答

如果您阅读文档 [ ^ ]，原因很明显：

If you read the documentation[^], the reason would become clear:

OPENQUERY（Transact-SQL） [ ^ ]：

'query'是否在链接服务器中执行查询字符串。 字符串的最大长度为8 KB。

'query' Is the query string executed in the linked server. The maximum length of the string is 8 KB.

您需要找到一种不同的方式来做你做的事情正在努力实现。例如，您可以使用链接服务器 [ ^ ]。

使用远程服务器上的临时表的示例：

You'll need to find a different way to do what you're trying to achieve. For example, you could use a linked server[^].

Example using a staging table on the remote server:

-- Insert the data into a staging table:
DECLARE @ID uniqueidentifier = NEWID();
INSERT INTO vinsdt073.innova.dbo.YourStagingTable (ID, Data)
VALUES (@ID, '<OutlookContact>...</OutlookContact>');

-- Build the query to execute on the remote server:
DECLARE @Query varchar(max);
SET @Query = 'SET NOCOUNT ON;
DECLARE @doc2 nvarchar(max);

SELECT 
    @doc2 = Data 
FROM 
    innova.dbo.YourStagingTable 
WHERE 
    ID = ''' + CAST(@ID As nvarchar(36)) + '''
;

EXEC innova.dbo..testsp2 @login = ''Ragul'', @doc2 = @doc2 WITH RESULT SETS
(
    (
        id             varchar(255),
        FirstName      varchar(max),
        LastName       varchar(max),
        EmailAddress1  nvarchar(max),
        login_name     varchar(255)
    )
);';

-- Build the query to execute on the local server:
SET @Query = Replace(@Query, N'''', N'''''');
SET @Query = N'SELECT * into #tbl_user_details FROM OPENQUERY (vinsdt073, ''' + @Query + N''')';

-- Execute the query:
EXEC(@Query);

-- Clean up the staging data:
DELETE FROM vinsdt073.innova.dbo.YourStagingTable WHERE ID = @ID;

由于无法将变量传递给 OPENQUERY function。

It's complicated by the fact that you can't pass a variable to the OPENQUERY function.

使用包含xml max length的动态sql将sp结果插入临时表

相关推荐