sql server - ¿Por qué aparece el error "El tipo de datos Xml no se admite en consultas distribuidas" al consultar un servidor vinculado para datos que no son XML?

sql-server linked-server

Question

Tengo dos servidores SQL (que ejecutan SQL Server 2008) llamados DATA01 y DATA02 . DATA02 tiene un DATA02 vinculado a la definición del servidor, que apunta a DATA01 , con una configuración de mapeo de usuario adecuada. En DATA01 hay una base de datos MyDatabase contiene estas dos tablas:

CREATE TABLE T_A (
    Id int
)

CREATE TABLE T_B (
    Id int,
    Stuff xml
)

Cuando ejecuto este comando desde DATA02 , me devuelven los datos como se esperaba:

SELECT Id FROM LINK.MyDatabase.dbo.T_A;

Sin embargo, cuando ejecuto este comando desde DATA02 , DATA02 un error:

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

El error es

El tipo de datos Xml no es compatible con consultas distribuidas. El objeto remoto ''DATA02.MyDatabase.dbo.T_B'' tiene columnas xml.

Y extrañamente, este comando:

SELECT Id FROM LINK.MyDatabase.dbo.T_B;

también da el mismo error, aunque no estoy seleccionando la columna xml! ¿Que esta pasando?

Answer 1

Encontré otra forma de hacer esto:

1. Cree un Linked Server en DATA01 , DATA02 o incluso un tercer servidor (podría ser local).
2. Ejecute su consulta utilizando EXEC [linked_server].[sp_sqlexecute] .

¿Por qué prefiero este método a la creación de vistas o utilizando OPENQUERY ?

1. Este método no requiere tener ningún permiso en el servidor vinculado (no es necesario crear vistas).
2. Puede pasar un parámetro a su consulta utilizando la sintaxis sp_sqlexecute (aquí encontrará más información sobre sp_sqlexecute ). Para OPENQUERY , debe pasar un STRING o TEXT_LEX , lo que significa que todos los valores deben escribirse directamente en esta función.

Aquí está el ejemplo:

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

Answer 2

Esta es una deficiencia dentro de SQL Server. La mera existencia de una columna xml en la tabla impide que participe en consultas distribuidas (por ejemplo, se consulta a través de una conexión de servidor vinculada). Esto se menciona en la documentación , aunque no es particularmente prominente. Puede ver el informe principal de errores de Connect aquí , y un informe similar aquí . El último da dos soluciones:

1. Cree una vista [a] sin la (s) columna (s) XML en el servidor remoto y pregunte eso.

   En su ejemplo, esto implicaría agregar una vista a MyDatabase que se vea así:

   CREATE VIEW V_T_B AS SELECT Id FROM T_B;

   A continuación, puede consultar esta vista a través del enlace para obtener los datos de Id . Tenga en cuenta que algo como

   SELECT Id FROM ( SELECT Id FROM T_B ) T_B;

   no funciona

2. Utilice una consulta de paso en el formulario.

   SELECT * from OPENQUERY (... )

   Este método tiene la ventaja de no requerir ningún cambio en la base de datos de origen; el inconveniente es que ya no es posible usar nombres de cuatro partes estándar para los datos locales y vinculados. La consulta se vería como

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

   Tenga en cuenta que si realmente desea los datos xml, este método (junto con la conversión desde y hacia un tipo de datos que no sea xml) será necesario :

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

Tenga en cuenta que el error se informó por primera vez en SQL Server 2005 y permanece sin resolver en SQL Server 2014.

Answer 3

Prueba esto:

• Cree una vista en el lado de origen con conversión de xml a nvarchar (máx.):

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

• Puede seleccionarlo en el lado de destino con conversión a xml

SELECCIONE Cast (Stuff as XML) como Stuff DESDE OPENQUERY (DATA02, ''SELECT Stuff FROM vXMLTest'')

Esta solución me funciona en 2008R2.