SQL Server Performance

Urgent help - FOR XML clause

Discussion in 'SQL Server 2005 General Developer Questions' started by cncncn, Jan 22, 2009.

  1. cncncn New Member

    Hi -
    We are calling a stored procedure on a linked server B through a synonym from server A.. like this..

    exec @ret = dbo.SYN1

    synonym definition is
    CREATE SYNONYM [dbo].[SYN1] FOR .[DB].[dbo].[SP_A]

    this returns data in weird format...
    XML_F52E2B61-18A1-11d1-B105-00805F49916B
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    0x440952006F0077004E0075006D0062006500720044046E0061006D006500440373006500710044056C006100620065006C0044047400790070006500440965007000690073006F00640065004900440044046E0061006D006500440373006500710044056C006100620065006C004404740079007000650044096500700069
    0x334D6F64656C023542010000000238460102394601023A4601023B410000023E10E40400000000023F4200000000024042000000004381470147022010E40400001600494344416E644C65616453657269616C4E756D626572022110E4040000010034022310E404000008005374616E646172640224420000000041014B02
    0x10E404000013004C6561644D6561737572656D656E7444617465021C10E4040000010033021E10E404000008005374616E64617264021F420000000041014B023410E40400002A004D6F7374526563656E744D6561737572656D656E745375627468726573686F6C6454696D655374616D7002354201000000023746000238
    0x6E61626C65023542010000000238460102394601023A4601023B410000023E10E404000002004F6E023F42000000000240420000000043014B023410E4040000290056465468657261707935446566696272696C6C6174696F6E44656C6976657261626C65456E65726779023542020000000238460102394601023A460102
    0x00446574656374696F6E456E61626C65022110E4040000010031022210E40400000900446574656374696F6E022310E404000008005374616E646172640224420000000041014B023410E404000011005654446574656374696F6E456E61626C65023542010000000238460102394601023A4601023B410000023E10E40400
    0x6D656F75744475726174696F6E02354206000000023610E404000011004869676820526174652054696D656F75740238460102394601023A4601023B410000023D10E404000003006D696E023E10E4040000010030023F42000000000240420000000043814681450145021610E404000015004272616479506172616D6574
    0x024042000000004381458144814381428141


    the statement which returns the data inside the SP is
    SELECT
    *
    FROM tblname
    ORDER BY [grp!1!RowNumber!hide]
    FOR XML EXPLICIT

    We are using same service pack 3 , same collation in both the servers...
    But I am able to see the data when I query the table directly using
    SELECT * FROM .[DB].dbo.tblname ORDER BY [grp!1!RowNumber!hide]
    FOR XML EXPLICIT
    Does that mean we cannot resturn data using FOR XML clause using a remote SP ? The only thing I know of is
    xml datatype is not supported in distributed queries.
    Any help is appreciated
  2. satya Moderator

    Yes there is limitation of xml support from SQL server 2005. Xml data type is not supported in distributed queries. So the workaround is touse openquery. But is there a workaround to insert/update the table with XML through linked server.

Share This Page