Querying tables with XML columns via linked server

Here is something I stumbled upon recently, although evidently this bug has been around for years, and is unlikely to get resolved any time soon.

Say you want to query a table on another server, quite straight forward if you have the privileges to set up a linked server, however if the table you want to query has an XML data typed column in it, your query will error.  Even if your queries doesn’t touch the XML column, its mere existence is enough to upset SQL server.

Lets have a look at an example; the ExecutionLogStorage table in a standard ReportServer DB, this table shows us the success/failures of reports which users have tried to run. As you can see from the screenshot below, the last column (“AdditionalInfo) is of an XML data type.

image

So assume I have already set up a linked server from “SQL…02” to “SQL…01”, now I want to write a simple SELECT statement on “SQL…02” to select a couple of columns from the ExecutionLogStorage table on “SQL…01” like so:

image

You can see from my first screenshot  that the none of the columns queried (LogEntryID, InstanceName, Username, Status and TimeStart) contain an xml data type, but the mere presence of that final column in our table, is causing problems when trying to query it via a linked server.

The solution?  Firstly you could create a view on the database which the table sits on, querying only the non xml columns and then write your actual query against that view.  However I am not a fan of adding objects to out of the box application databases.  So my proposed solution is to use the built in open query function and re-write the query as follows:

image

Perhaps not the nicest solution, but it keeps us from adding objects to our ReportServer database and gets around a bug Microsoft are in no hurry to fix.

Information on the bug itself can be found here.

Advertisements

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