none
SQL Adapter Error in Biztalk Server 2006 RRS feed

  • Question

  • Hi All..
    I am getting SQL Adapter error as

    The adapter "SQL" raised an error message. Details "HRESULT="0x80004005" Description="Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."".

    I developed solution in Biztalk Server 2006 where i send details to SAP and get voucher number and this voucher number i store in SQL database,..I checked the suspended message the data coming is OK..
    But don't know where to start the trace for error..
    the Xml which is suspended is

    <ns0:ReqSQL_Voucher xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003"><ns0:FPA_SAP_Voucher APP_SERIAL_NO="xxxx-xxx-11" SAP_VOUCHERNO="999999999"></ns0:FPA_SAP_Voucher></ns0:ReqSQL_Voucher>

    Pls Advice
    Sandeep

    Friday, April 17, 2009 8:10 AM

Answers

  • Hi Sandeep,

    Seems adapter is executing some nested query and the nested query is a problem. Can you enable tracing in adapter and share the logs?
    This can help to enable tracing:
    http://blogs.msdn.com/sonuarora/archive/2007/03/27/where-are-the-log-files-enable-tracing-in-net-wcf-lob-adapter.aspx

    Thanks
    Gaurav
    Saturday, April 18, 2009 8:25 AM

All replies

  • Hi,

    It looks like the query that is executed in SQL server is the problem. I think this is related to SQL and not to BizTalk. Did you try to execute the query manually in the SQL Management studio?

    HTH,

    Randal van Splunteren
    http://biztalkmessages.vansplunteren.net

    Please mark answered if this answers your question.

    Friday, April 17, 2009 1:17 PM
    Moderator
  • Hi Randal...
    Thanks for your reply..
    I did execute the store procedure by passing the same values and it sucessfully inserted the values in SQL table...Also i didn't used any sub query in the stored procedure..
    Don't know what could be the reason for this error...
    only this message is suspended in Biztalk...after that all other messages sucessfully executed and inserted the values in SQL table..

    Thanks ..
    Sandeep
    Friday, April 17, 2009 6:23 PM
  • Hi Sandeep,

    Seems adapter is executing some nested query and the nested query is a problem. Can you enable tracing in adapter and share the logs?
    This can help to enable tracing:
    http://blogs.msdn.com/sonuarora/archive/2007/03/27/where-are-the-log-files-enable-tracing-in-net-wcf-lob-adapter.aspx

    Thanks
    Gaurav
    Saturday, April 18, 2009 8:25 AM
  • "only this message is suspended in Biztalk...after that all other messages sucessfully executed and inserted the values in SQL table.."

    By stating the above do you mean, that it happened just once, and that it does not repro everytime

    OR

    it happens only for the first message when an attempt is made for inserting the voucher number, and then after all messages execute inserting the voucher number successfully, i.e. you can repro it anytime by just restarting the application.

    Also as Gaurav mentioned, it would be really helpful if you can also share the trace logs. :)

    Regards & Thanks
    Sharad
    Monday, April 20, 2009 8:01 AM