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

Status : 

 


41
0
Sign in
to vote
ID 247204 Comments
Status Active Workarounds
Type Bug Repros 15
Opened 12/20/2006 3:13:32 PM
Access Restriction Public
Primary Feedback Item 338953

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.  
Sign in to post a comment.
Posted by mortsdn on 3/24/2015 at 11:59 AM
Same issue in SQL Server 2014 Enterprise Edition.. This bug is now 10 years old!
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.