Linked server and XML

If you call a stored procedure in a linked server to return xml, you may not get what you expected.
Let’s define a linked server first:

exec

sp_addlinkedserver ‘MyServer’, , N‘SQLOLEDB’, ‘MyRemoteServer’, ,,‘Northwind’

exec

sp_addlinkedsrvlogin @rmtsrvname = ‘MyServer’, @useself = ‘true’

exec

sp_serveroption @Server=‘MyServer’, @optname =‘RPC’, @optvalue=‘TRUE’

exec

sp_serveroption @Server=‘MyServer’, @optname =‘RPC OUT’, @optvalue=‘TRUE’

In the remote server, create the procedure:

CREATE

PROCEDURE [dbo].[RunME] AS

SELECT

GETDATE() AS SystemDate

FROM

dbo.Categories AS Categories

FOR

XML AUTO

GO

EXEC

[dbo].[RunME]

GO

 
It returns the xml document: <Categories SystemDate="2008-05-22T15:38:35.740"/>
 
Now run the stored procedure from the local server via the linked server:

EXEC

MyServer.Northwind.[dbo].[RunME]

It returns:
0x440A530079007300740065006D004400610074006500440A430061007400650067006F00720069006500730001020201529B9A00009E61010143
Why it does not return the xml? It’s the stream of the xml with image data type. OLEDB for SQL Server provider makes the conversion.
To workaround this, we can define the linked server by OLEDB provider for ODBC instead of OLEDB for SQL Server
 

— Drop the server first

exec

master.dbo.sp_dropserver ‘MyServer’,‘droplogins’

EXEC

sp_addlinkedserver

@server

= N‘MyServer’,

@srvproduct

=N,

@provider

=‘MSDASQL’,   — OLEDB provider for ODBC 

@datasrc

= NULL,

@location

= NULL,

@provstr

= ‘DRIVER={SQL Server};SERVER=MyRemoteServer;UID=sa;PWD=pass;’,

@catalog

=‘Northwind’

exec

sp_serveroption @Server=‘MyServer’, @optname =‘RPC’, @optvalue=‘TRUE’

exec

sp_serveroption @Server=‘MyServer’, @optname =‘RPC OUT’, @optvalue=‘TRUE’

 
Now run the stored procedure from the local server via the new linked server:

EXEC

MyServer.Northwind.[dbo].[RunME]

Valid xml is returned:
<Categories SystemDate="2008-05-22T15:38:35.740"/>
 
Drop the server

exec

master.dbo.sp_dropserver ‘MyServer’,‘droplogins’

 
So if you need to return xml from a linked server in SQL server, you need to use OLEDB for ODBC provider to define the linked server. This applies both to SQL Server 2000 and 2005.
 
In SQL Server 2005, though it supports xml data type, it cannot be used in distributed query. By OLEDB for SQL server provider, one workaround is to save the xml to a local variable, convert it to  nvarchar(max) and return it.
 

CREATE

PROCEDURE [dbo].[RunME] AS

DECLARE

@x xml;

SET

@x=(

SELECT

TOP 1 GETDATE() AS SystemDate

FROM

sys.objects AS Categories

FOR

XML AUTO)

SELECT

CAST(@x as nvarchar(max))

Run it from the linked server by OLEDB for SQL Server, you can get the vcalid output as the linked server defined by OLEDB for ODBC provider .

 

 

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

2 Responses to Linked server and XML

  1. pkosrc says:

    Good tip.
    Thank you 😉

  2. Marcelo L. says:

    Good Work!! is “the” solution!! thanks!! regards from Cordoba, Argentine

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s