Home Dashboard Directory Help
Search

Tables with xml columns cannot be queried using distributed (llinked server) queries by Andrew Simms


Status: 

Active


37
0
Sign in
to vote
Type: Bug
ID: 247204
Opened: 12/20/2006 3:13:32 PM
Access Restriction: Public
Primary Feedback Item: 338953
3
Workaround(s)
view
13
User(s) can reproduce this bug

Description

I can contemplate the rationale, but that does not justify a total lack of support for this especially when a query is not projecting the xml columns. It also does not help that supporting documentation does appear anywhere in BOL discussions of linked servers (it is only mentioned here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0687bf85-810a-48cb-84eb-1a377f7da37d.htm).

The ability to query any table regardless of column types is a minimal requirement for distributed support.
Details
Sign in to post a comment.
Posted by Microsoft on 1/23/2012 at 10:39 AM
Hi,
Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. We also understand that the suggested workaround is not perfect but we believe it could be useful in many cases.
If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.
Thank you,
Jean-Sébastien – SQL Server
Posted by Microsoft on 1/23/2012 at 10:39 AM
Hi,
Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. We also understand that the suggested workaround is not perfect but we believe it could be useful in many cases.
If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.
Thank you,
Jean-Sébastien – SQL Server
Posted by ishmell on 10/23/2011 at 9:39 PM
Are there any workarounds for inserting/deleting records on a remote server? I'm in a situation where I need to move data from a "live" server to an "archive" server. I can run an SP on the archive server to pull the data across and insert it into the archive table, but removing or updating those records on the live server to indicate they have been archived is not possible. Any workarounds are welcome.
Posted by yrushka on 11/4/2010 at 3:31 AM
Yes i ran into this bug in SQL 2008 (not only 2005) as well. It looks like the bug was not fixed in 2008.

I wrote about the whole resolution in this post:

http://yrushka-dba.blogspot.com/2010/11/sql-server-fix-error-msg-9514-level-16.html
Posted by mmcnary on 6/15/2010 at 5:40 AM
I find it interesting that not only can you not reference the xml column without a convert or cast, it is not possible to delete from a table that contains an xml column.
Posted by artemds on 3/13/2009 at 2:34 AM
insert into server.dbo.table also generate this error... :(
Posted by Microsoft on 5/27/2008 at 5:23 PM
Just a quick update on this bug. We have closed it since we consider it a duplicate of Connect Feedback ID: 338953.

We currently recommend the following work-arounds:

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

2. Use a pass-through query in the form

SELECT * from OPENQUERY (... )


Best regards
Michael
Posted by Microsoft on 1/3/2007 at 12:30 PM
Thanks for your feedback. The problem you found is a known limitation in SQL Server 2005. We are considering improving support for distributed queries and XML data type in the next version of SQL Server.
Sign in to post a workaround.
Posted by MustafaH on 1/22/2014 at 6:25 AM
I think to create a view and exclude those columns with XML datatype is a better and less complicated option.
Posted by JingZhang on 3/29/2011 at 5:38 PM
cast the XML column to nvarchar(max) or nvarbinary(max)
Posted by Andrew Simms on 12/20/2006 at 3:32 PM
Using a view to project out non-xml columns allows access via SELECT.