Home > Biztalk, Biztalk 2010, WCF > Biztalk Configure WCF-SQL Adapter for Outbound Operation using Typed Stored Procedure

Biztalk Configure WCF-SQL Adapter for Outbound Operation using Typed Stored Procedure

  • Follow the steps in section Consume Adapter Service
  • Select sqlBinding from the dropdown
  • Click the Configure button

  • In the secure tab, select Windows from the “client credential type” drop down
  • In the URI Properties tab, enter the Server name, InstanceName and IntialCatalog. In the following figure the server is “LocalHost”, we are using the default instance, and IntialCatalog the name of the adventureworks2008R2 database

  • Click Binding Properties

  • Click Ok
  • Click the Connect button
  • Select Client (Outbound operations)
  • Click Strongly-Typed Procedures from the “Select a category” list
  • Select a stored procedure from the “Available categories and operations” list
  • Click the Add button. Notice that the stored procedure is now list in the “Added categories and operations”
  • You can click the properties button to view the schema that will be generated
  • You can add multiple stored procedures if you want to have them all under one schema

  • Click OK
  • Notice in the solution explorer window that two files are add; one is the xsd schema and the other is the binding xml file

    • You can add the schema to the Schmas or any other folder. Make sure you modify the name space
    • You can change the Type Name and File Name

    • Open the XSD file by double clicking on it
    • Select the Schema Node

    • Make sure that you DO NOT change the target Namespace
    • Make sure you DO NOT modify the elements’ names

  • Import port binding by following these steps
    • Open Biztalk admin console
    • Expand Biztalk server administration
    • Expand Biztalk groups
    • Expand Applications
    • Right click on the application you want to import the port to
    • Select Import à bindings
    • Select the generated xml file from step 16
    • Select the send ports node

    • Double click on the send port

    • Click Configure
    • If you only have one operation, Copy the value of the Action element, clear the textbox and past the copied value into the textbox

    • Click Ok

Consume Adapter Service in Biztalk Application

  • Right click on the Biztalk project
  • Click AddàAdd Generated Items

  • Click Consume Adapter Service

  • Click Add
Categories: Biztalk, Biztalk 2010, WCF Tags: , ,
  1. RachaRams
    July 21, 2011 at 3:00 pm


    One quick question -Can the PolleddataAvailableStmt and Polled DataStmt be the same?
    Also, is there any special syntax for Sproc in the PolleddataAvailableStmt


    • Lajak Technologies Inc
      July 21, 2011 at 3:17 pm

      Re can both be the same
      It depends what you are doing. Usually with the AvailableStatement you want to tell the adaptor that there the data is available and the PollingStatement to read that data. You polling statement needs to mark that record as read, whether by updating a column (e.g. isRead) or moving the column to a history table as explained in http://msdn.microsoft.com/en-us/library/dd788452%28v=bts.10%29.aspx. You also want avoid clogging the bandwidth with redundant data just to check if record do exist.

      Re is there a special syntax
      No, as long as it returns a resultset. For example do use the “Return” keyword. Use SELECT. But ideally you want to return a count of records. For example you may have some the following code

      IF(EXIST(SELECT ….From Table))
      SELECT 1 –Do NOT use Return 1
      SELECT 0

  2. Nehal
    October 20, 2011 at 10:25 am

    What if we dont want to use Polling ? I have scenario where i dont have to use polling. Is there any way that I can execute my stored proc without having pollingAvailabeStatement and PollingStatement ?

  3. Nehal
    October 26, 2011 at 11:41 am

    Thank you so much Ahmed for your reply. I’m having one more problem while receiving data back from stored procedure on receive response port. Its giving me
    “The request channel timed out while waiting for a reply after 00:19:59.3301210. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout.”

    I even tried to increase Timeout in web.config or app.config file and even in binding properties also.

    I have receive port(req and response) and send port(req and response). So, while sending data back its giving the above error. Any suggestion ?
    Thank you for your time and co-operation.

    • Lajak Technologies Inc
      October 27, 2011 at 11:15 pm

      Do you still have the issue? It could be a firewall issue. I would start with a simple POC first

  4. Nehal
    October 28, 2011 at 11:33 am

    Yes I’m still having the same issue. Previously, it was working fine when I deployed application to my “local”. And after that when I deployed application to actual server. Since then its not working. Its giving the same “Timeout” error on actual server. So, as per you suggest I have made public and private profile firewall “On” on server as it was “On” on my local and it was working previously on “local”. So, firewall is “On” on both local and server. But, still the same issue.

  5. Nehal
    October 28, 2011 at 11:44 am

    Its having still the same issue with both(local and server, I mean where ever I have deployed). Tried couple of times. Any more things/ideas/suggestion to get this resolve ?

  6. Nehal
    October 28, 2011 at 2:22 pm

    When I’m tracing the instance it gives me Document type “…dbo#Response” does not match any of the schemas. That’s my response schema. I’m configuring WCF-custom adapter at send port with REQ and RESP. REQ – XML Receive and also configuring properties for XML Receive and RESP – Passthrough. What could be the issue ?

  7. Nehal
    October 28, 2011 at 4:20 pm

    Yeah. WCF-Custom receive – XML Receive. configured the properties and provided document schema name as Response. Works great !!! Lets see I have lot of Stored Proc and now i’m damn sure i’ll get some crap to figure out with it if i’ll configure for all stored proc. Anyways.

    Thanks a ton!!! for your guidence and co-operation.

    • Lajak Technologies Inc
      November 1, 2011 at 8:57 am

      No problem my friend. My pleasure to help you and others. Have fun figuring out the other stuff and let me know if you need more help. Cheers

  8. August 28, 2013 at 4:48 pm

    Hey, just wanted to thank you for making this post. It was a huge help!

  9. Lajak Technologies Inc
    September 13, 2013 at 8:14 am

    No problem. Glad it helped you

  10. remy
    September 29, 2013 at 9:53 pm

    Good article….

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: