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!

SQL Adapter – Add generated item

I was trying to call a stored procedure using biztalk and to retrieve the response from the stored procedure.

I right clicked the project and clicked on “Add Generated item”.

After I set up everything correctly in the “Add Generated item” wizard and clicked “Generate” on the Statement information window,An error window popped up. “Failed o execute SQL Statement. Please ensure that the supplied syntax is correct.”

This Error occured because the response from the SQL database is not in XML format. To generate the response schema using “Add Generated Item”, biztalk must be able to retrieve an XML representation of the response schema.

To resolve this, simply add “XMLDATA” at the end of your SELECT statement in your store procedure.

SELECT ‘Things to return’ FOR XML AUTO, XMLDATA;

NOTE:  Make sure you remove ‘XMLDATA’ before you start your biztalk application. Otherwise the response you get from the SQL server might not be what you expect.