Execute Stored Procedures in SQL Server using the SQL adapter
The Transact-SQL and CLR stored procedures in SQL Server are surfaced as operations in Microsoft BizTalk Adapter for SQL Server under the Procedures node while using the Consume Adapter Service Add-in or Add Adapter Service Reference Plug-in. The operation names exposed by the SQL adapter are the same as the name of the stored procedure in SQL Server. All the parameters in the stored procedure are exposed in the corresponding operation. The OUT parameter contains the return value of the stored procedure. The result set of the stored procedure is an array of DataSet. For more information about DataSet, see https://go.microsoft.com/fwlink/?LinkId=196853. The schema information of the target object is obtained as part of the response message at run time.
However, if you want to obtain the schema information of the target object at design time, you must generate schemas for the procedures under the Strongly-Typed Procedures node in the Consume Adapter Service Add-in or Add Adapter Service Reference Plug-in. Note that the same stored procedures are surfaced under the Procedures and the Strongly-Typed Procedures node. The return value of the stored procedure is strongly typed, and not just an array of DataSet. As the schema information is available at the design time, you can use it to map the schema of the stored procedure to another schema for a different operation. For example, you can map the schema generated for a strongly-typed procedure to the schema generated for the Insert operation on a database table.
Note
You will not be able to view the schema information at design time for a strongly-typed stored procedure if:
- You are using a cursor, which is a return value of another stored procedure, as the input parameter for the strongly-typed stored procedure.
- It is a CLR stored procedure that performs some operations on a table.
Support for Stored Procedures with FOR XML Clause
The SQL adapter also enables you to execute stored procedures that have a SELECT statement with a FOR XML clause. A FOR XML clause is used in a SELECT statement to return the results as XML instead of a rowset. For more information about the FOR XML clause, see https://go.microsoft.com/fwlink/?LinkId=131402.
Note
The “native” SQL adapter available with BizTalk Server supports only those stored procedures that return XML, that is, have the FOR XML clause in the SELECT statement. With the support for stored procedures with FOR XML clause, you can execute these stored procedures using the WCF-based SQL adapter without making any changes to the stored procedure definition.
Support for Stored Procedures with Temporary Tables
The SQL adapter enables you to generate metadata for stored procedures that contain temporary tables in their definitions. However, for such stored procedures you must generate metadata by selecting the stored procedures only from the Procedures node, while using the Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in. The adapter does not support generating metadata for such stored procedures from under the Strongly-Typed Procedures node.
Support for Result Sets Containing Columns Without Names or With Same Names
The following table lists how the SQL adapter handles columns without names and same names in the result sets for stored procedures and strongly-typed stored procedures.
| Result set contains… | Stored Procedure | Strongly-Typed Stored Procedure |
|---|---|---|
| Columns without names | The SQL adapter generates a name for the column in the following way: a unique ID (GUID) is generated for the column without "-" (hyphen), and then the GUID string is prefixed by "C" because the generated GUID might start with a digit but an XML tag name cannot. | The SQL adapter generates the following name for the column: “UnNamedColumn[column_index]”, where column_index starts from ‘0’. |
| Columns with same names | The names of the columns other than the first one are appended with “” (underscore) followed by a random GUID without "-" (hyphen). For example: “\[GUID]”. | The SQL adapter does not support columns with same names in the result sets, and throws an exception. Important: You must ensure that the column names in a result set have unique names. |
Note
In general, it is recommended that all the columns in a result set for stored procedures and strongly-typed stored procedures must be named, and have unique names.
For more information about:
How to execute stored procedures, see Execute stored procedures in SQL Server using BizTalk Server.
How to execute stored procedures having a FOR XML clause, see Execute stored procedures having a FOR XML clause in SQL Server using BizTalk Server.
Message schemas for the stored procedures, see Message Schemas for Procedures and Functions.