locked
Could not update the table from other server in sql server 2008 RRS feed

  • Question

  • User-133573814 posted

    Hi Friends,

     

    I am facing a issue with trigger. I need to update the table from one server to another server. I have used linkedserver concept. It works fine with select query but not with update. The table in the another server has xml column. So when i try to update it throws the error message as "Xml data type is not supported in distributed queries. Remote object 'stnpsql02v.IDM.dbo.person' has xml column(s)." Can someone share me if they faced the same issue and any workaround to solve this?

    Thanks,

    venkat V

    Thursday, January 19, 2012 2:49 AM

Answers

  • User-133573814 posted

    Hi,

    I found the solution. We need to open the server objects in that server and open the linked server. They we will find our linked server (stnpsql02v). Just to right click and give test connection. Once the test connection is succeeded, i can find the update on other server. Thanks for help (smirnov).

    Hope this will be useful.

    Thanks Everyone.

    Venkat V

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 19, 2012 6:35 AM

All replies

  • User1508394307 posted

    Please check this

    http://connect.microsoft.com/SQLServer/feedback/details/247204/tables-with-xml-columns-cannot-be-queried-using-distributed-llinked-server-queries

    Using OPENQUERY can solve that issue, for example

    INSERT #temp
    SELECT * FROM 
    (
    SELECT * FROM OPENQUERY(stnpsql02v,'SELECT ... FROM IDM.dbo.person')
    ) AS X
    Thursday, January 19, 2012 4:21 AM
  • User-133573814 posted

    Hi Smirnov,

    Thanks for the reply,

    I tried the same OpenQuery,

    UPDATE OPENQUERY ([STNPSQL02v],'select prism_email from [idm].[dbo].[person] WHERe id=67245')
    set prism_email='test@sm.com' .

    It shows 1 row(s) affected.

    It shows good

    select * from OPENQUERY ([STNPSQL02v],'select id,Modified_Date,prism_email from [idm].[dbo].[person] WHERe id=67245') ---> shows the updated value.

    But when i opened the server 'stnpsql02v' and used select query (select * from person where id=67245) the prism_email was not updated?

    Thanks,

    Venkat V

    Thursday, January 19, 2012 5:37 AM
  • User1508394307 posted

    Do you have more than one database with person table? Maybe you should call select * from idm..person? Is there any trigger? Since you were able to select the updated values using select * from OPENQUERY - it must be there.

    Try to insert a new record. Maybe it can help to find where the problem is.

    Thursday, January 19, 2012 5:55 AM
  • User-133573814 posted

    Hi,

    I found the solution. We need to open the server objects in that server and open the linked server. They we will find our linked server (stnpsql02v). Just to right click and give test connection. Once the test connection is succeeded, i can find the update on other server. Thanks for help (smirnov).

    Hope this will be useful.

    Thanks Everyone.

    Venkat V

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 19, 2012 6:35 AM