Cfrgtkky

I have two SQL Servers (running SQL Server 2008) named DATA01 and DATA02. DATA02 has a linked server definition LINK, that points at DATA01, with suitable user mapping set up. On DATA01 there is a database MyDatabase containing these two tables:

CREATE TABLE T_A (

    Id int

)



CREATE TABLE T_B (

    Id int,

    Stuff xml

)

When I run this command from DATA02, I get data returned as expected:

SELECT Id FROM LINK.MyDatabase.dbo.T_A;

However, when I run this command from DATA02, I get an error:

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

The error is

Xml data type is not supported in distributed queries. Remote object 'DATA02.MyDatabase.dbo.T_B' has xml column(s).

And strangely, this command:

SELECT Id FROM LINK.MyDatabase.dbo.T_B;

also gives the same error, even though I'm not SELECTing the xml column! What's going on?

This is a deficiency within SQL Server. The mere existence of an xml column on the table prevents it from participating in distributed queries (eg being queried through a linked server connection). This is mentioned in the documentation, though not particularly prominently. You can see the main Connect bug report here, and a similar report here. The latter gives two workarounds:

1. 
   

   
   

   Create [a] view without the XML column(s) on remote server and query that.

   
   

   
   
   

   In your example, this would involve adding a view to MyDatabase
   that looks like this:

   
   
   

   CREATE VIEW V_T_B AS SELECT Id FROM T_B;
   
   

   
   
   

   You could then query this view through the link to get the Id
   data. Note that something like

   
   
   

   SELECT Id FROM ( SELECT Id FROM T_B ) T_B;
   
   

   
   
   

   doesn't work.

   
   


2. 
   

   
   

   Use a pass-through query in the form

   
   
   

   SELECT * from OPENQUERY (... )
   
   

   
   

   
   
   

   This method has the advantage of not requiring any change to the
   source database; the downside is that it is no longer possible to
   use standard four-part naming for both local and linked data. The
   query would look like

   
   
   

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

   
   
   

   Note that if you actually do want the xml data, this method (along
   with casting to and from a non-xml datatype) will be required :

   
   
   

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

   
   

Note that the bug was first reported in SQL Server 2005, and remains unfixed in SQL Server 2014.

Try this:

• Create a view on the source side with xml cast to nvarchar(max):

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

• You can select it on the destination side with cast to xml

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

This solution works for me in 2008R2.

I found another way of doing this:

1. Create a Linked Server on DATA01, DATA02 or even some third server (could be local).


2. Execute your query using EXEC [linked_server].[sp_sqlexecute].

Why I prefer this method over creating Views or using OPENQUERY?

1. This method does not require to have any permissions on the Linked Server (you don't need to create Views).


2. You could pass a parameters to your query using the sp_sqlexecute syntax (find more information about sp_sqlexecute here). For OPENQUERY you must pass a STRING or TEXT_LEX, witch means that all the values must be typed right into this function.

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