연결된 서버에서 비 xml 데이터를 쿼리 할 때 "Xml 데이터 형식은 분산 쿼리에서 지원되지 않습니다"라는 오류가 발생하는 이유는 무엇입니까?

연결된 서버에서 비 xml 데이터를 쿼리 할 때 "Xml 데이터 형식은 분산 쿼리에서 지원되지 않습니다"라는 오류가 발생하는 이유는 무엇입니까?

---

두 개의 SQL Server (SQL Server 2008 실행) DATA01와 DATA02. 을 가리키는 DATA02연결된 서버 정의가 있으며 적절한 사용자 매핑이 설정되어 있습니다. 에 데이터베이스가 두 테이블을 포함하는이 :LINKDATA01DATA01MyDatabase

CREATE TABLE T_A (
    Id int
)

CREATE TABLE T_B (
    Id int,
    Stuff xml
)

에서이 명령을 실행하면 DATA02예상대로 데이터가 반환됩니다.

SELECT Id FROM LINK.MyDatabase.dbo.T_A;

그러나에서이 명령을 실행 DATA02하면 오류가 발생합니다.

SELECT Id, Stuff FROM LINK.MyDatabase.dbo.T_B;

오류는

분산 쿼리에서는 XML 데이터 형식이 지원되지 않습니다. 원격 개체 'DATA02.MyDatabase.dbo.T_B'에 xml 열이 있습니다.

이상하게도이 명령은 다음과 같습니다.

SELECT Id FROM LINK.MyDatabase.dbo.T_B;

xml 열을 사용 하지 않더라도SELECT 동일한 오류가 발생 합니다! 무슨 일이야?

---

이것은 SQL Server의 결함입니다. 단순한 존재 (예 : 연결된 서버 연결을 통해 조회되는) 분산 쿼리에 참여에서 테이블을 방지 그것을에서 XML 열의. 이것은 특별히 눈에 띄지는 않지만 문서에 언급되어 있습니다. 여기 에서 주요 Connect 버그 보고서를 볼 수 있으며 여기 에서 유사한 보고서를 볼 수 있습니다 . 후자는 두 가지 해결 방법을 제공합니다.

1. 원격 서버에서 XML 열없이 [a]보기를 만들고 쿼리합니다.

   귀하의 예에서 이것은 MyDatabase다음과 같은 뷰를 추가하는 것을 포함 합니다.

   CREATE VIEW V_T_B AS SELECT Id FROM T_B;
   

   그런 다음 링크를 통해이 뷰를 쿼리하여 Id데이터 를 가져올 수 있습니다. 다음과 같은 것에 유의하십시오.

   SELECT Id FROM ( SELECT Id FROM T_B ) T_B;
   

   작동하지 않습니다 .

2. 양식에서 통과 쿼리 사용

   SELECT * from OPENQUERY (... )
   

   이 방법은 소스 데이터베이스를 변경할 필요가 없다는 장점이 있습니다. 단점은 로컬 및 링크 된 데이터 모두에 대해 표준 네 부분 이름 지정을 더 이상 사용할 수 없다는 것입니다. 쿼리는 다음과 같습니다.

   SELECT Id FROM OPENQUERY(DATA02, 'SELECT Id FROM T_B') T_B;
   

   당신이 실제로 경우주의 할 (비 XML 데이터 형과에서 캐스팅과 함께) XML 데이터,이 방법을 원하는됩니다 필요합니다 :

   SELECT Id, CAST(Stuff AS XML) Stuff 
   FROM OPENQUERY(DATA02, 'SELECT Id, CAST(Stuff AS nvarchar(max)) Stuff 
                           FROM T_B') T_B;
   

이 버그는 SQL Server 2005에서 처음보고되었으며 SQL Server 2014에서 수정되지 않은 상태로 남아 있습니다.

---

이 시도:

• xml을 nvarchar (max)로 캐스트하여 소스 측에보기를 만듭니다.

CREATE VIEW vXMLTest AS SELECT cast (Stuff as nvarchar (max)) as STUFF FROM T_B

• xml로 캐스트하여 대상 측에서 선택할 수 있습니다.

OPENQUERY (DATA02, 'SELECT Stuff FROM vXMLTest')에서 Cast (Stuff as XML)를 항목으로 선택

이 솔루션은 2008R2에서 저에게 효과적입니다.

---

이 작업을 수행하는 다른 방법을 찾았습니다.

1. 크리에이트 Linked Server에 DATA01, DATA02또는 심지어 세 번째 서버를 (로컬 될 수있다).
2. 를 사용하여 쿼리를 실행합니다 EXEC [linked_server].[sp_sqlexecute].

뷰를 생성하거나 사용하는 것보다이 방법을 선호하는 이유는 무엇 OPENQUERY입니까?

1. 이 방법은 연결된 서버에 대한 권한이 필요하지 않습니다 (보기를 만들 필요가 없음).
2. sp_sqlexecute구문을 사용하여 쿼리에 매개 변수를 전달할 수 있습니다 ( sp_sqlexecute에 대한 자세한 내용은 여기 ). 들어 OPENQUERY당신은 통과해야합니다 STRING또는 TEXT_LEX모든 값이 바로이 기능에 입력해야 함을, 마녀 수단.

Here is the example:

DECLARE @UserID UNIQUEIDENTIFIER = ''

DECLARE @SearchForUserParams NVARCHAR(MAX) = N'@UserID UNIQUEIDENTIFIER';  
DECLARE @SearchForUserQuery NVARCHAR(MAX) = 
N'SELECT
    UserID,
    Username,
    Email,
    CONCART(NVARCHAR(MAX), UserDataXml) AS UserDataXml
FROM User
WHERE UserID = @UserID
'

EXEC [linked_server].[dbo].[sp_executesql] 
    @SearchForUserQuery,
    @SearchForUserParams,
    @UserID = @UserID

ReferenceURL : https://stackoverflow.com/questions/14442039/why-do-i-get-the-error-xml-data-type-is-not-supported-in-distributed-queries-w