Configuring SQLAdapter

The main purpose of using SQL Adapter is to poll the SQLSERVER Database for the required data by executing the query /stored procedure.
We can configure SQL adapter in two ways:1) Receive2) Send
Receive Adapter:
We can use this to extract data from SQL SERVER. This receives data from SQLSERVER. Therefore only select and stored procedure is available.
Restrictions:
1. This adapter can be bound to one-way receive ports2. Supports only select statements3. we cannot return more than one result sets using stored procedures.
Send Adapter:
We can use this adapter to enrich the incoming content by getting the remaining data from SQLSERVER.
We can insert, update and delete data from the tables. We can parameterize and the parameter value dynamically. It can be bound to one-way or two-way solicit response.
General Structure of XSD files:<rootelementname><sync><before><nameoftable fieldname1="’FieldValue1’" fieldname2="’FieldValue2’"></before><after><nameoftable fieldname1="’FieldValue1’" fieldname2="’FieldValue2’"></after></sync></rootelementname>
Insert operations will have only block.Update operations will have both and block.Delete operations will have only block.
How to add the structure and configure SQL Adapter:
Adding:
1. Right click the Biztalk project and select add generated items2. Select add adapter.3. Select the sql adapter4. Click next and set the sqlserver connection string5. Check next and fill up the target namespace and root element name.6. Select between the receive and send adapter of SQL.7. Kindly notice that when you select receive, you will be asked to provide document root element name whereas when you select send, you will be asked to provide request root element name and response element name.
Note: Same name cannot be provided for request and response.
8. Click next and select the type of statement [updategram/storedprocedure].9. If you are selecting stored procedure, select the stored procedure and finish.10. If you are selecting update gram, choose the option between insert/delete/update and finish.
11. Now you can see an orchestration and xsd file.
Configuring:
1. Open the Biztalk Explorer2. Add the receive port and add a receive location3. Select the transport type as SQL4. Select the receive handler and receive pipeline5. You can configure the polling gap by seconds, minutes and hours by selecting polling unit of measure6. You can also specify the polling interval7. Set the connection string8. Enter the document root element name and target namespaceClick on SQL Command ellipse and select the project and schema. You can select only if you have deployed it already.
Tips:
You have forgotten the root element name and namespace. How to retrieve that?Instead of entering the document root element name and target namespace, go and select the project and schema through the SQL Command ellipse.
Sample Stored Procedure creation
CREATE PROCEDURE [SP_GetPODetails] @ID char (1) ASSELECT * FROM Purchase Details where OrderID = @ID for xml auto, xmldataGO
Xml auto:
To provide the result in xml format. This will return table name as the element name and all the columns as its attributes.
<Orders OrderID=”ORD1” Category = “Electronics” /><Orders OrderID=”ORD2” Category = “Hardware” />
Xml auto, elements:
This will provide the result set as a xml with table name as element and columns as child elements
<orders><orderid>ORD1</orderid><category>Electronics</category><orders>
XmlData:
XML DATA is used to generate the information required by SQLADAPTER
Important Note: Once the SQL SCHEMA is generated, remove the xmldata immediately.
Tips:
Are you getting the following error?

The reason is the query you are trying to add in not having XML AUTO