Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mathias
Posting Yak Master
119 Posts |
 Posted - 2009-10-08 : 05:42:59
|
| insert into serverA.DB.bcl.FS_S_BCL_SEND_QUEUE( [BCL_FILE_NAME] ,[CREATION_DATE] ,[REPORT_HEADER_REC_NUM] ,[XML_FILE] ,[STATUS_] ,[XML_ERROR] ,[XML_STATUS] ,[POSTED_TIME])select [BCL_FILE_NAME] ,[CREATION_DATE] ,[REPORT_HEADER_REC_NUM] ,cast([XML_FILE] as varchar(max)) as XML_FILE ,[STATUS_] ,[XML_ERROR] ,[XML_STATUS] ,[POSTED_TIME]from FS_S_BCL_SEND_QUEUEI receive the following errorMsg 9514, Level 16, State 1, Line 1Xml data type is not supported in distributed queries. Remote object 'serverA.DB.bcl.FS_S_BCL_SEND_QUEUE' has xml column(s).any idea |
|
|
Sachin.Nand
2937 Posts |
Posted - 2009-10-08 : 05:52:20
|
| I dont know maybe it sounds stupid.You can change the [XML_FILE] column data type to varchar & then after change it back to xml.PBUH |
 |
|
|
sanoj_av
Posting Yak Master
118 Posts |
Posted - 2009-10-08 : 06:05:02
|
quote:
cast([XML_FILE] as varchar(max)) as XML_FILE
Casting the column in your distributed query wont help you, as you cannot call that table when in contains an XML datatype, even if you are not selecting that column in your query. The nearest alternative I see is to create a view on the source server casting the xml to varchar(max) and query against that view |
|
|
|
Mathias
Posting Yak Master
119 Posts |
Posted - 2009-10-08 : 07:15:26
|
| I managed to do it by creating an extra table select [BCL_FILE_NAME],[CREATION_DATE],[REPORT_HEADER_REC_NUM],cast([XML_FILE] as varchar(max)) as XML_FILE,[STATUS_],[XML_ERROR],[XML_STATUS],[POSTED_TIME]into FS_S_BCL_SEND_QUEUE2from FS_S_BCL_SEND_QUEUEI could then use the FS_S_BCL_SEND_QUEUE2 from the second server and insert the data directly into the xml field too. |
|
|
|
|
|
|
|
|
Copy table with XML field from one server to new