[MSSQL] 분산 쿼리에서 Xml 데이터 형식을 사용할 수 없습니다. 오류 해결하기

이슈 상황

---

OPENROWSET 을 사용하여 실행계획 XML 타입을 수집하려고 하는 상황에서 오류가 발생하였습니다.

 

오류내용

[ErrorCode]9514 [ErrorMessage] 분산 쿼리에서 Xml 데이터 형식을 사용할 수 없습니다. 원격 개체 'a'에 xml 열이 있습니다.

쿼리

9번째 라인을 보면 XML 타입인 query_plan 을 OPENROWSET 을 사용하여 조회하려고 하고 있습니다.

set @query = 'SELECT
                @@SERVERNAME AS [ServerName],
                DB_NAME(t.[dbid]) AS [DBName],
                qs.plan_handle AS [PlanHandle],
                CASE WHEN object_name(t.objectid, t.dbid) is null
                  then t.text
                  else object_name(t.objectid,t.dbid) END [ObjectName], --SP가 아닌경우 text 전체
                qs.creation_time AS [CreationTime],
                qp.query_plan AS [QueryPlan]
              FROM
                sys.dm_exec_query_stats AS qs WITH (NOLOCK)
                  CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
                  CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
                INNER JOIN sys.databases db
                ON t.[dbid] = db.[database_id]
              OPTION (RECOMPILE, MAXDOP 1)'
 
set @ExQuery = 'SELECT a.ServerName, a.DatabaseName, a.plan_handle, a.ObjectName, a.CreationTime, CAST(a.QueryPlan AS XML) AS QueryPlan
                FROM OPENROWSET(''SQLOLEDB'', ''' + @ServerIP + ''';''Trusted_Connection=YES', ''' + replace(@Query, '''', '''''') + ''') AS a'

 

 

해결 방법

---

해결을 위해서는 2가지 방법이 있습니다.

 

첫 번째는 XML 을 NVARCHAR(MAX) 로 변경하여 OPENROWSET 으로 가져올 수 있습니다.

두 번째는 OPENROWSET 대신 OPENQUERY 를 사용하여 가지고 오는 방법입니다.

 

NVARCHAR(MAX) 로 가져오기

9번째 라인을 보면 XML 을 NVARCHAR(MAX) 타입으로 변환하고 있습니다.

이 쿼리를 OPENROWSET 으로 가지고 온 후,  SELECT 시 다시 XML 로 변환하여 조회할 수 있습니다.

set @query = 'SELECT
                @@SERVERNAME AS [ServerName],
                DB_NAME(t.[dbid]) AS [DBName],
                qs.plan_handle AS [PlanHandle],
                CASE WHEN object_name(t.objectid, t.dbid) is null
                  then t.text
                  else object_name(t.objectid,t.dbid) END [ObjectName], --SP가 아닌경우 text 전체
 qs.creation_time AS [CreationTime],
                CAST(qp.query_plan AS NVARCHAR(MAX)) AS [QueryPlan]
              FROM
                sys.dm_exec_query_stats AS qs WITH (NOLOCK)
                  CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
                  CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
                INNER JOIN sys.databases db
                ON t.[dbid] = db.[database_id]
              OPTION (RECOMPILE, MAXDOP 1)'
 
set @ExQuery = 'SELECT a.ServerName, a.DatabaseName, a.plan_handle, a.ObjectName, a.CreationTime, CAST(a.QueryPlan AS XML) AS QueryPlan
                FROM OPENROWSET(''SQLOLEDB'', ''' + @ServerIP + ''';''Trusted_Connection=YES', ''' + replace(@Query, '''', '''''') + ''') AS a'

 

OPENQUERY 로 가져오기

 

OPENROWSET 대신 OPENQUERY 를 사용하면 XML 타입의 데이터를 변환없이 가지고 올 수 있습니다.

OPENQUERY 를 사용하는 방법은 아래 URL 을 참고해 주시면 됩니다.

https://mozi.tistory.com/318

 

 

 

 

 

 

분산 쿼리에서 Xml 데이터 형식을 사용할 수 없습니다. 오류 해결하기