Using a orchestration to subscribe to schemas which have been published as a web service

I came across this gotcha a few days ago, I wanna document it very badly before I forget.

So you published your schemas as a web service, built a orchestration and a C# program to test your web service. And then BAAAMM, you got the following error.

A message received by adapter “SOAP” on receive location “<YourReceiveLocation>” with URI “<YourWebServiceURI>” is suspended.
Error details: The published message could not be routed because no subscribers were found. This error occurs if the subscribing orchestration or send port has not been enlisted, or if some of the message properties necessary for subscription evaluation have not been promoted.

You checked your orchestration and the web service and failed to identify the problem. Now it’s the time to blame biztalk, as it gives no help for resolving this issue.

As a general note, you can view your subscriptions from Biztalk Administration Console -> Biztalk Group -> New Query -> Search for subscriptions -> Double click on the orchestration.

Ok, in the expressions tab, there is a method Name row. This method name row has to be the same as the operation name of the webservice you are using. For example, if you are calling an operation in a web service called “calculate”, the orchestration port subscribing to this web service must use the name “calculate” as it’s “Operation” not the default value “Operation_1”. SO next time don’t forget to set your operation name when subscribing to a web service. 🙂

I will post a guide on how to publish a web service and write a C# windows application to test the web service when I have time.

How to call a stored procedure with parameters and retrieve the response

One of the tasks given to me last week was to build a biztalk solution to communicate with a stored procedure on a SQL server DB. I’ve played around the SQL adapter in biztalk for a bit and I’m going write a step-by-step guide to show you how I built my solution. It might not be the only way nor the correct way to do it, but it worked for me, please do tell me if I made any stupid mistakes.

First, Click the Add Generated item to bring up the “Add Adapter Wizard”. This wizard will generate a schema to use for the SQL interactions. In this case, it will generate 2 schemas, one for sending the request and one for retrieving the response.

Here, you set up your SQL related information including Server name, Login, Target DB.

And in the following window,

Add Generated Item wizard

Select Send port as we are sending a request first and then receive a response.

Type in the corresponding namespace and root names in the fields.

(Note: The namespace and root names here is the namespace and root names of the generated schemas.)

In the next window, select Stored Procedure.

In the “Statement information” window, select the stored procedure you want to call and click generate (make sure the value column is un-checked).

(Note: Here, the SQL adapter communicates with the SQL database and retrieves the format of the return value of the stored procedure you want to call and it constructs the response schema by looking at the return value’s format. Hence, to generate a schema, the return value must be in XML format. If you get a incorrect syntax error, refer to the “SQL Adapter – Add generated item” entry)

Click Finish on the next window.

Now, the SQL adapter is set. If you look at your project folder, there are 2 new items, the SQL service and an empty orchestration.

Expand SQLSerive schema, confirm that the request and response schemas are set correctly. Notice that the request schema contains the stored procedure as a child record and it’s child field elements are the parameters for that stored procedure.

Now we need to set up the orchestration to use the SQL adapter.

Create 2 messages in orchestration for the request and response schemas.

Drag a Send shape to the orchestration where you want the SQL stored procedure call to be made.

Drag a Receive shape to the orchestration where you want the stored procedure response to be returned.

Create a Request-Response port for sending and receiving messages from the SQL stored procedure and click Next.

Config port wizard

In the next window, select “I will be send request and receiving response.” Click Next and click Finish to close the wizard

The request message must be constructed before going into the Send shape. We can use a map to assign values for the fields in the request schema. As mentioned before, the fields in the request schema are the parameters of the stored procedure.

Now we can deploy the solution and continue the configuration in the Biztalk administration console.

In the Biztalk console, create a 2-way send port for your application. Select “SQL” for the transport type and click configure.

SQL send port

Set up the connection string and type in the namespace and response root element name.

(Note: The namespace is the namespace of your SQLSerive schema and the root element name is the root name of the response schema in SQLService.)

From the drop-down menus, select XMLTransmit and XMLReceive from the pipeline options and click OK.

AND WE ARE DONE!