none
Are Table parameters supported in BT2006R2 RRS feed

  • Question

  • Hello,

    We are using BizTalk2006 R2.

    We really really need to insert data with a parent-child relationship. So I did something similar to what's mentioned in this blog :

    http://blogs.msdn.com/b/biztalkcpr/archive/2009/10/05/inserting-parent-child-records-with-identity-column-using-wcf-sql-adapter-in-one-transaction.aspx

    This works with Table Types and Table-Valued Parameters. So I created a stored procedure and tried to consume that with BizTalk (2006R2)
    Consumption in VS2005 works nice, schema's are generated and everything looks really cool. But then I got my solution finished and I tried to send a message to the DB. But then I got the following error....

    An error occurred while processing the message, refer to the details section for more information
    Message ID: {3B57B587-5C6E-4FBC-828B-DFF4BE7C32CC}
    Instance ID: {E7E71A19-6BCC-49FB-A169-F97D4C513874}
    Error Description: System.NotSupportedException: The SqlDbType "" is not supported. Modify your table, view, stored procedure, or function definition to avoid having parameters or columns of this type.

    Server stack trace:
       at System.ServiceModel.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)

    So I thought, well we are using table types here, probably the SQL2005 client that we are using at the moment doesn't understand that, so I installed the SQL 2008 client on the Dev machine, but i still get the same error.

    My guess is that still somewhere a configuration exists for this adapter to use the 2005 client. But I don't know where to set / change it. Or maybe I am wrong here ,and I am doing something wrong.

    Anyway I really need you help !

    Please

     


    Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread
    Wednesday, March 2, 2011 10:34 AM

Answers

  • Hi Well,

    Have you checked the solution in the discussion following the blog?

    Here is the words from Tom Abraham:

    I also faced the same SqlDbType issue but got it resolved. It turned out to be a security permissions issue. Ensure that the account that is used to access the stored proc also has permissions on the Table type. Further I used the WCF-Custom adapter with Sql binding.

    Thanks!
    Thursday, March 3, 2011 5:21 AM

All replies

  • Hi Well,

    Have you checked the solution in the discussion following the blog?

    Here is the words from Tom Abraham:

    I also faced the same SqlDbType issue but got it resolved. It turned out to be a security permissions issue. Ensure that the account that is used to access the stored proc also has permissions on the Table type. Further I used the WCF-Custom adapter with Sql binding.

    Thanks!
    Thursday, March 3, 2011 5:21 AM
  • Thanks Andrew!

    This answer saved me!!

    Sean Boman

    Wednesday, November 30, 2016 8:38 PM