Multiple ways to generate metadata / execute Stored Procedures in the SQL adapter

  • [アーティクル]

If you’ve used the SQL Adapter in CTP3 of the BizTalk Adapter Pack V2, you’ll notice that there are two nodes in the Metadata Hierarchy for Stored Procedures – one is named “Procedures”, and the other is named “Strongly-Typed Procedures”. What’s the difference, and what are the scenarios which they are meant to solve? What are their limitations? I’ll try to explain all this, in this post.

1. The first method of executing Stored Procedures which we added in the adapter, manifested as the operations under the “Procedures” node in the Metadata hierarchy. The operations here have the action “Procedure/<database_schema_name>/<procedure_name>”. For these operations, the adapter reads the System Tables in SQL Server, finds out the parameters to the procedure, and exposes them as IN or INOUT parameters in the WSDL. However, we yet need a way to return the result sets which the Stored Procedure can return at execution time. The adapter exposes these result sets as a return parameter, of type System.Data.DataSet[]. Being an array, multiple result sets can be returned.

At runtime, the adapter executes the Stored Procedure using the ADO.NET function SqlCommand::ExecuteReader(). Each result set returned by executing the Stored Procedure is serialized into its own DataSet; hence all of them together appear as DataSet[]. The response XML message contains both, the schema for each result set / DataSet, as well as the actual data in that result set / DataSet. When used in a .NET application, the schema + data together are used when deserializing the SOAP message into a DataSet object.

Advantages: Works for all stored procedures.

Limitations: The result sets are loosely typed (being a DataSet[]), and in BizTalk, are not helpful if you want to use the Mapper.

Workarounds: What you could do is, execute the procedure once, dump the XML message to a file location, and open it in notepad. Select the <schema> node within the result set section in the return parameter, copy-paste it into a new file, and save it with the .xsd extension. You now have a schema which you can deploy in your BizTalk orchestration/project. Also, use the Message Template feature in the WCF-Custom/WCF-SQL port configuration, using a XPath query to only select out the data (at runtime) for that particular result set (matching the XSD which you deployed); ignoring the other result sets (if any) and out parameters. You now have a XML blob being submitted to BizTalk, which conforms to the XSD which you deployed.

2. The next approach we took, was to try and expose the result sets as “strongly typed”, instead of the loosely typed DataSet[] above. These operations manifest as the operations under the “Strongly-Typed Procedures” node in the metadata hierarchy. The action is of the form “TypedProcedure/<database_schema_name>/<procedure_name>”. For these operations, the adapter reads the System Tables in SQL Server, finds out the parameters to the procedure, and exposes them as IN or INOUT parameters in the WSDL. In order to expose the returned result sets in a “strongly typed” fashion, the adapter needs to know what the metadata for the returned result sets will look like. For this purpose, at design time, the adapter executes the Stored Procedure using the ADO.NET function SqlCommand::ExecuteReader(CommandBehavior::SchemaOnly). This translates to the SET FMTONLY ON option being used. However, in order to execute the Stored Procedure, the adapter needs to pass in values to the parameters. For this purpose, the adapter uses DBNull.Value for each parameter. Upon execution, the adapter gets back multiple (empty) result sets, and from these, the adapter can obtain the metadata for the result sets which can potentially be returned at runtime.

Note – I use the word potentially. This is because, at runtime (FYI – the adapter uses the ADO.NET function SqlCommand::ExecuteReader() at runtime), a Stored Procedure can return different result sets based on the input values. For example, if an input parameter has the value 1, the Procedure could return a result set by performing the operation “SELECT * FROM TABLE1”. If the input parameter has the value 2, it might actually execute “SELECT * FROM TABLE2”. At runtime, only one of the two result sets will be returned, depending on whether the input parameter is 1 or 2. However, at design time, when the SET FMTONLY ON statement is used, both the (empty) result sets are returned. The adapter exposes them both as complex out parameters (and names them in the metadata as TypedProcedureResultSet1, TypedProcedureResultSet2, etc). At runtime, one will be null, while the other will have the appropriate data filled in.

Note – The current design of the WCF LOB Adapter SDK (on which the SQL Adapter is based) is that metadata is also required at runtime. Hence, at runtime too, the adapter executes the Stored Procedure using SET FMTONLY ON (just once) to obtain the metadata. Then, for every message being passed to the adapter, the adapter executes the Stored Procedure, and based on the result sets returned, tries to determine whether it should be serialized as TypedProcedureResultSet1, or TypedProcedureResultSet2, etc (for example) (based on the metadata it obtained earlier by using SET FMTONLY ON).

Advantages: Strong typing of the result sets.

Limitations:

  • These operations cannot be used to execute “complicated” Stored Procedures – for example, a hypothetical procedure which returns multiple result sets (from potentially different tables) within a loop. This is because the adapter won’t be able to figure out which result set (at runtime) needs to be serialized as which complex type (TypedProcedureResultSet1, or TypedProcedureResultSet2, or something else).
  • These operations won’t work for Stored Procedures, when in the procedure code, a temporary table is created, and then, one of the returned result sets is obtained by doing a SELECT on that temporary table. The reason being, when the SET FMTONLY ON option is used, no temporary tables are created. However, when the SQL execution engine comes across the line SELECT * FROM #temptable, it throws an error, (something to the effect of it not finding an artifact named #temptable), since it never created this artifact in the first place (since SET FMTONLY ON is not supposed to make any changes on the server).
  • I mentioned above that during metadata retrieval time, the adapter needs to pass in values for all parameters to the Stored Procedure, and for this purpose it passes in DBNull. Now, if the Procedure internally calls a System Stored Procedure, and passes in one of the input parameters (which in our case is NULL), and if the System Stored Procedure returns an error if it sees the value as NULL, the adapter will get an exception at metadata retrieval time. NOTE – this won’t happen in your custom procedure if your code itself throws an error if it sees an input parameter having the value NULL, since for user procedures, when SET FMTONLY ON is true, the execution engine skips the evaluation of the if statements (this evaluation only happens in system stored procedures).

Workarounds:

3. In CTP4 (releasing end of October 2008), we’ve added one more mechanism to execute Stored Procedures. This was mainly done for backward compatibility.

The earlier SQL adapter required result sets returned from Stored Procedures to use the FOR XML syntax. This was because the old adapter used SQLXML; using FOR XML would instruct the SQL Server to return the result set as a single XML value, and the SQLXML code on the client would then parse the XML returned.

In methods 1 and 2 above, you’ve seen that the new adapter used the ADO.NET SqlCommand::ExecuteReader() function. If this function was used to execute a Stored Procedure which returned a result set using FOR XML, all the adapter would see is a single XML value (exposed as a string) – i.e., the adapter thinks that the returned result set only has one column. Even worse, if the XML value was large, it would get split into multiple rows. This is of course extremely cumbersome to work with.

For this purpose, a third mechanism was added, what I call “XML Procedures”. The action for such operations is “XmlProcedure/<database_schema_name>/<procedure_name>”.

There is no design time experience for such procedures. Generating metadata involves the following:

  • Generate metadata for the same procedure under the “Procedures” node – this generates the metadata for the request message as well as the response message. Here, we are only interested in the request message schema, since that’s the format in which the request message needs to be sent (though you need to use the “XmlProcedure” action).
  • In SQL Server Management Studio, edit your Stored Procedure, and add the XMLDATA keyword at the end of the FOR XML statement (similar to what you would have done if using the older SQL Adapter).
  • Execute your Stored Procedure from SQL Server Management Studio. Before the actual data, you should see a <schema> node which has the metadata for the result set. Copy-paste the schema into a .xsd file. Also, add a root node (with namespace) to encapsulate the nodes in the data/schema (you’ll see why later).
  • This .xsd file will serve as the schema for the response message in BizTalk.
  • Remember to remove the XMLDATA keyword from the Stored Procedure code after you're done with the above steps.

At runtime, you need to do this:

  • Specify values for the XmlStoredProcedureRootNodeName (mandatory) and XmlStoredProcedureRootNodeNamespace (optional) binding properties. The XML obtained by executing the “FOR XML” Stored Procedure will be wrapped within this root node, with this namespace.
  • Use the “XmlProcedure/<database_schema_name>/<procedure_name>” action during execution (instead of “Procedure/<database_schema_name>/<procedure_name>”).

When the adapter sees the XmlProcedure action, it uses the ADO.NET SqlCommand::ExecuteXmlReader() function.

Advantages: You can continue using FOR XML Stored Procedures side-by-side with the old SQL Adapter. Can work with all stored procedures which return XML.

Limitations: There is no way to generate the metadata using the adapter, since the WCF LOB Adapter SDK does not have a way to accept input parameter values during design time. The adapter cannot use NULL for the parameter values (unlike how it did in 2 above), since obtaining metadata involves actual execution of the Stored Procedure, and therefore all the Stored Procedure logic comes into play, which might throw an error if an unexpected value is seen for an input parameter.

Workarounds: Use one of the other Stored Procedure execution methods.