none
sql adapter error executing a procedure with cursors. RRS feed

  • Question

  • Hi,
    We are noticing a strange error with one of our biztalk interfaces in 2013 R2 environment.

    Submission of data on a .net webpage triggers a biztalk interface. This interface calls a MS SQL stored procedure which has two cursors, selects, Insert, and truncate statements.

    Tried both WCF-SQL, and WCF-Custom adapters.

    After the .net webpage is submitted, biztalk is throwing following error....

    Error Description: System.Data.SqlClient.SqlException (0x80131904): A cursor with the name 'MSsalProcedure1' does not exist.
    A cursor with the name 'MSsalProcedure1' does not exist.
    A cursor with the name 'MSsalProcedure1' does not exist.
    A cursor with the name 'MSsalProcedure1' does not exist.
    A cursor with the name 'MSsalProcedure2' does not exist.
    A cursor with the name 'MSsalProcedure2' does not exist.
    A cursor with the name 'MSsalProcedure2' does not exist.
    A cursor with the name 'MSsalProcedure2' does not exist.

    Server stack trace:
       at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

     
    Appreciate any pointers/help.

    Thursday, January 18, 2018 12:25 AM

Answers

  • Figured out that the schema wasn't matching when executed through the web page vs alternative way of processing.

    It's corrected now and the interface executes OK.

    • Marked as answer by AmanDev1 Friday, January 19, 2018 4:31 PM
    Friday, January 19, 2018 4:31 PM

All replies

  • Hi ,

    This is much more related to your sql stored procedure exception . To confirm this you can try to execute this stored procedure from SQL Management studio ,you will get the same exception .

    Try to check sql stored procedure logic and once fixed you can get response within BizTalk 


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    Thursday, January 18, 2018 2:57 AM
  • Thank you.

    I didn't mention earlier that the procedure executes OK in SQL Mgmt studio.

    Also the procedure works OK, when I create that .net page equivalent in xml file. The xml file is picked up and processed OK by BizTalk.

    The error occurs when the .net web page tries to invoke BizTalk interface which in turn fails with the stored procedure.

    Error Description: System.Data.SqlClient.SqlException (0x80131904): A cursor with the name 'MSsalProcedure1' does not exist.
    A cursor with the name 'MSsalProcedure1' does not exist.
    A cursor with the name 'MSsalProcedure1' does not exist.
    A cursor with the name 'MSsalProcedure1' does not exist.
    A cursor with the name 'MSsalProcedure2' does not exist.
    A cursor with the name 'MSsalProcedure2' does not exist.
    A cursor with the name 'MSsalProcedure2' does not exist.
    A cursor with the name 'MSsalProcedure2' does not exist.

    Server stack trace:
       at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

    Thursday, January 18, 2018 2:14 PM
  • Figured out that the schema wasn't matching when executed through the web page vs alternative way of processing.

    It's corrected now and the interface executes OK.

    • Marked as answer by AmanDev1 Friday, January 19, 2018 4:31 PM
    Friday, January 19, 2018 4:31 PM