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!