sp_xml_preparedocument无法在openquery语句中返回？ [英] sp_xml_preparedocument fails to return in openquery statement?

问题描述

示例Sp

 创建  procedure  testsp 
  as  
  DECLARE   @ idoc   int ， @ doc   varchar （ 1000 ）= 
  ' < OutLookContact> 
< Contact FirstName =AsifLastName =GhafoorEmailAddress1 =[e-mail]/> 
< Contact FirstName =RameezLastName =AliEmailAddress1 =[e-mail]/> 
< / OutLookContact>' 
 
  EXEC  sp_xml_preparedocument  @ idoc   OUTPUT ， @ doc  
 
  SELECT  *  FROM   OPENXML （ @idoc ，'  / OutLookContact / Contact'， 1 ）
  WITH （FirstName  varchar （ 50 ），LastName  varchar （ 50 ），EmailAddress1  varchar （ 50 ））

现在执行时

 选择 *  f rom   OPENQUERY （linkserver，'  Exec testsp '）

但它返回错误

错误消息：消息11529，级别16，状态1 ，过程sp_describe_first_result_set，第1行无法确定元数据，因为每个代码路径都会导致错误;查看其中一些错误。消息2812，级别16，状态62，过程sp_describe_first_result_set，第1行无法找到存储过程'testsp'。

解决方案

尝试指定存储过程的数据库和模式名称：

 选择 *  from   OPENQUERY （linkserver，'  Exec YOURDB.dbo.testsp'）

解决方法 - 使用结果集将SP包装到新SP：

  ALTER   PROCEDURE  testsp2 
  AS  
 
  EXEC  testsp 
 < span class =code-keyword> WITH  RESULT SETS 
（
（
 FirstName  nvarchar （max）， 
 LastName  nvarchar （max），
 EmailAddress1  nvarchar （max）
）
）; 

Sample Sp

create procedure testsp
as
DECLARE @idoc int,@doc varchar(1000)=
'<OutLookContact>
<Contact FirstName="Asif" LastName="Ghafoor" EmailAddress1="[e-mail]" />
<Contact FirstName="Rameez" LastName="Ali" EmailAddress1="[e-mail]" />
</OutLookContact>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

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

Now when executing

select * from OPENQUERY ( linkserver,'Exec testsp' )

But it returns error
Error Message: Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because every code path results in an error; see previous errors for some of these. Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 1 Could not find stored procedure 'testsp'.

解决方案

Try specifying the database and schema name for your stored procedure:

select * from OPENQUERY ( linkserver, 'Exec YOURDB.dbo.testsp' )

Workaround - wrap SP to new SP with result set:

ALTER PROCEDURE testsp2
AS

EXEC testsp
WITH RESULT SETS
(
(
FirstName nvarchar(max),
LastName nvarchar(max),
EmailAddress1 nvarchar(max)
)
);

这篇关于sp_xml_preparedocument无法在openquery语句中返回？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！