Calling stored procedures from BizTalk (and other applications) and the FMTONLY flag

Most developers will run into the issue of not being able to generate metadata from a stored procedure even though it is perfectly valid and can be accessed and run without a hitch from the SQL Server Management Studio or directly from code. A lot of the time there will be an error message on the lines of

Error while retrieving or generating the WSDL. Adapter message: Retrieval of Operation Metadata has failed while building WSDL at 'TypedProcedure/dbo/FetchTestData'

Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at 'TypedProcedure/dbo/FetchTestData' ---> System.Data.SqlClient.SqlException: Invalid object name '#temp01'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
...

In some cases there will be no error at all (SSIS is prone to this for example).

The above error message will give us something to go on though. When reviewing the stored procedure we can see that a temporary table is created and filled with data. Later on this table is used in a select in order to return data to the client. Nothing unusual, still it fails. The stored procedure looks like this (never mind the necessity of the temp table, it is just a demo of the issue):

ALTER PROCEDURE [dbo].[FetchTestData]
(@a4 varchar(4))
AS
SELECT t1, t2, t3 INTO #temp01 FROM Table_1
SELECT t1, t2, t3 FROM #temp01
RETURN

The next step is then to run profiler during a run of the generate wizard to see what is actually happening in the background. How does the metadata get generated by the wizard and why does it fail?

When running the generation, we can see the following bits in the trace

SET FMTONLY OFF; SET FMTONLY ON;
exec [dbo].[FetchTestData] @a4=NULL
SET FMTONLY OFF;

Now we are getting somewhere. The FMTONLY setting is used in order to not process any rows but just return response metadata to the client. However, our stored procedure uses a temporary table which FMTONLY=ON will cause to not be created since any modifications are allowed to be made. When trying to do a select on the temporary table, it will fail since it never was created causing the error messages mentioned above.

There is a way around this issue though. Since we know what is happening, we can revert the execution of SET FMTONLY ON that the adapter does before the execution of the procedure. We should however not just add a command of SET FMTONLY OFF to the beginning of our procedure. Actually executing the entire procedure which such a solution will result in might not be a good choice. If we only do a select on data it is fine, but if the procedure also includes insert, update and delete statements, these will be called as well.

Instead, we check for the FMTONLY flag early on and if it is set, we switch it off when needed and then switch it back on again. Our modified and metadata-generation-secure procedure now look like this:

ALTER PROCEDURE [dbo].[FetchTestData]
(@a4 varchar(4))
AS
DECLARE @FmtOnlyIsSet bit = 0
IF (1=0) BEGIN SET @FmtOnlyIsSet = 1 END
IF @FmtOnlyIsSet = 1
   SET FMTONLY OFF

SELECT t1, t2, t3 INTO #temp01 FROM Table_1

IF @FmtOnlyIsSet IS NULL
   SET FMTONLY ON

SELECT t1, t2, t3 FROM #temp01

RETURN

What magic is done here?

First, we declare a variable that can hold the current setting for the FMTONLY flag, we call it @FmtOnlyIsSet and set it to false by default.

The IF (1=0) bit may look a bit off, but is in fact quite clever. When FMTONLY is set to ON, all conditional statements will be ignored but the content will be executed. This since all possible return paths has to be checked. By checking for an impossible match (1=0) we can be sure that the statement inside the IF will be run only if FMTONLY is ON. Hence we set our FMTONLY flag to true here.

Then we simply check if our flag is set to true when needed and if so, switch off the FMTONLY setting. Afterwards we do the same check and switch it back on. This part is important due to what I mentioned above. If we don't switch the FMTONLY setting back on, all statements will be run just as in a normal execution of the procedure which might not be wanted.


It is noteworthy to know that the FMTONLY setting not only will be used during metadata generation in development, but also when actually calling the procedure from the application. I noticed this when using typed datasets in BizTalk server with the WCF-SQL adapter. I couldn't do alterations to the stored procedure which I handled by instead doing a mock procedure to generate schemas from. Then I assumed that I could safely call the original procedure from BizTalk but I still got the invalid object error message. A quick look in Profiler showed that the adapter will do two passes to the stored procedure. First once with FMTONLY set to ON and then one without to actually execute the code.

My guess is that the adapter is smart enough to do a check that the signature for the procedure matches the previously generated and deployed metadata before executing code that could change data in the database. If the returned dataset wouldn't match the schema, we would know before any code has been executed.

I have only seen this when using typed datasets though which make sense. By using the technique described above, it isn't an issue at all. I'd rather like the idea that the contract is checked first before executing the procedure. It is also not posing a performance hit. The result from the metadata extraction will be cached so only the first call will need to fetch metadata. I am still not sure for how long the metadata is cached before it is refreshed in a call but it seems to hold it for quite a while.

BizTalk SQL adapter deadlocking issues

A few months ago I had an assignment at a company that was having issues with their BizTalk installation. When examining their integration platform I noticed that the event log was filled with deadlock exceptions from the SQL adapter. It was the standard error message stating that a message sent to adapter "SQL" is suspended. The transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The vast amount of deadlocks that occured directly pointed me to the procedures that was being called by the receive locations.

In this particular flow, a number of receive locations where scheduled to poll a database for data every minute. Nothing unusual about this so far. When looking at the procedures, I saw that each one logged the time of execution to a log table used for debugging purposes. This is a valid practise, if it weren't for the fact that the logging was set up wrong. Instead of letting each procedure write it's own row with execution data, the table had one single row with a number of columns, one for each procedure.

So, only one row in the table, which all the procedures tried to write to. The chance of all procedures running at the same time and therefore trying to do an update on this table row at the same time is actually pretty slim if the procedures run fast enough. However, the procedures in this case did take a few seconds to run.

Adding to this is the fact that BizTalk will wrap the call in a serializable transaction meaning that the row will be locked throughout the entire execution of the procedure. The risk of another procedure trying to gain access to the table while its locked increases then quite a bit, resulting in the high volume of deadlocks I saw in the event log.

Since this was in production and a valid test environment was missing (shudder), I made certain that the log table wasn't used for anything (you never know) and then commented out the UPDATE statements in the procedures. Since the entire flow were to be rewritten from scratch, I saw no need to more or less do a transposition of the table to get the procedure timestamps on their own rows since the data were of no more use.

The result: The number of deadlocks were reduced with 97% and according to the server operators, the BizTalk/SQL Server installations now runs a lot better while we build the replacement to this flow.

What to remember from this is that it is both vital to know how BizTalk handles transactions in the SQL adapter and to also know the different table and locking hints available in SQL Server.