Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Debugging SQL Procedure calls from BizTalk revisited

Dan Field

I wrote previously (here) about using SQL Server Profile to capture and debug SQL calls from BizTalk.  This method works well when there are no errors actually calling the procedure (but you want to tune the procedure using ‘real’ data from BizTalk).  However, it’s not as much help when BizTalk can’t call the procedure at all – because of an invalid conversion, or a malformed table type, or some other errors in the procedure that prevent the procedure from actually being run.  Either no event will get logged in the Profiler, or you will see only the stored procedure name but no parameters (because BizTalk realizes the procedure won’t be able to correctly execute).

While BizTalk will provide you with some feedback, it ranges in its helpfulness.  It may be something to the effect of “column X is invalid”, but not necessarily tell you which procedure it’s invalid in, or whether it’s invalid in a table type or in a table.  It also may simply say “Invalid cast”.  Experience says this is usually due to a bit (BizTalk is trying to insert “true” into a bit field) or a datetime (the XML data type and the SQL datatype are formatted differently) field, but which one?  What if there are many of both?

While dealing with such an issue at a client, I wrote the following XSLT.  This will take a BizTalk stored procedure message and turn it into a SQL statement – however, unlike the BizTalk adapter, it does not do much for data types (it’s going to assume everything is a VARCHAR or easily castable from a VARCHAR). It also assumes that an empty node should be inserted as a null, whether it has an “xsi:nil=’true'” or not.  However, it was enough to help me debug the procedure and figure out exactly where there was an invalid column or cast.  It’s fairly generic and should work for non-composite calls that use table types.  With some modification, it could work for composite calls as well.  First, the XSLT; then a sample XML document and output:

 

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="text" encoding="utf-8" indent="no"/>

	<xsl:template match="/">
                <!-- build any parameters -->
		<xsl:apply-templates />
                <!-- build the execute statement; BizTalk puts the SQL schema name in the XML namespace as the last part, hence all the substring-afters -->
EXEC <xsl:value-of select="substring-after(substring-after(substring-after(substring-after(substring-after(substring-after(substring-after(namespace-uri(*), '/'), '/'), '/'), '/'), '/'), '/'), '/')" />.<xsl:value-of select="local-name(*)" />
		<xsl:for-each select="/*/*">
			@<xsl:value-of select="local-name()" /><xsl:if test="position() != last()">,</xsl:if>
		</xsl:for-each>
	</xsl:template>

	<xsl:template match="/*/*[not(*/*)]">
                <!-- template should match any scalar parameters -->
DECLARE @<xsl:value-of select="local-name()" /> VARCHAR(255) = '<xsl:value-of select="." />';
	</xsl:template>

	<xsl:template match="/*/*[*/*]">
                <!-- template to match table type parameters -->
DECLARE @<xsl:value-of select="local-name()" />
		<xsl:text> </xsl:text>
		<xsl:value-of select="substring-after(substring-after(substring-after(substring-after(substring-after(substring-after(substring-after(namespace-uri(), '/'), '/'), '/'), '/'), '/'), '/'), '/')" />.<xsl:value-of select="local-name(./*[1])" />;

		<xsl:apply-templates />

	</xsl:template>

	<xsl:template match="/*/*/*[*]">
                <!-- template to match the table type data.  The node name will be the name of the UDT in SQL -->
		<xsl:variable name="ln" select="local-name(..)" />
INSERT @<xsl:value-of select="$ln" /> (<xsl:apply-templates>
			<xsl:with-param name="which">cols</xsl:with-param>
		</xsl:apply-templates>)
VALUES (<xsl:apply-templates>
			<xsl:with-param name="which">vals</xsl:with-param>
		</xsl:apply-templates>);
	</xsl:template>	

	<xsl:template match="/*/*/*/*">
                <!-- template to get the column names and the values; this is helpful when there might be cast issues, assuming they're not so bad that the SQL XML call can be produced at all -->
		<xsl:param name="which" />

		<xsl:if test="$which = 'cols'">
			<xsl:value-of select="local-name()" />
			<xsl:if test="following-sibling::*">,</xsl:if>
		</xsl:if>

		<xsl:if test="$which = 'vals'">
			<xsl:if test=". = ''">NULL</xsl:if>
			<xsl:if test=". != ''">'<xsl:value-of select="." />'</xsl:if>
			<xsl:if test="following-sibling::*">,</xsl:if>
		</xsl:if>

	</xsl:template> 

	<xsl:template match="text()" />
</xsl:stylesheet>

This would turn the following document:

<ns0:uspProcedure xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/Types/TableTypes/dbo">
	<ns0:scalarParam>FOO</ns0:scalarParam>
	<ns0:udtTableParam1>
		<ns3:udtTableType1>
			<ns3:Column1>HOO</ns3:Column1>
			<ns3:Column2>ASDF</ns3:Column2>
			<ns3:Column3>Value3</ns3:Column3>
		</ns3:udtTableType1>
		<ns3:udtTableType1>
			<ns3:Column1>BAW</ns3:Column1>
			<ns3:Column2>Fruit</ns3:Column2>
			<ns3:Column3>berry</ns3:Column3>
		</ns3:udtTableType1>
		<ns3:udtTableType1>
			<ns3:Column1>BAR</ns3:Column1>
			<ns3:Column2>Milk</ns3:Column2>
			<ns3:Column3>Dairy</ns3:Column3>
		</ns3:udtTableType1>
		<ns3:udtTableType1>
			<ns3:Column1>BAT</ns3:Column1>
			<ns3:Column2>CRELO</ns3:Column2>
			<ns3:Column3>CAS</ns3:Column3>
		</ns3:udtTableType1>
		<ns3:udtTableType1>
			<ns3:Column1>BAZ</ns3:Column1>
			<ns3:Column2>MARKER</ns3:Column2>
			<ns3:Column3>CHALK</ns3:Column3>
		</ns3:udtTableType1>
	</ns0:udtTableParam1>
	<ns0:udtTableParam2>
		<ns3:udtTableType2>
			<ns3:Column1/>
			<ns3:Column2/>
			<ns3:Column3/>
			<ns3:Column4/>
			<ns3:Column5/>
			<ns3:Column6>Basic</ns3:Column6>
			<ns3:Column7/>
			<ns3:Column8/>
			<ns3:Column9/>
			<ns3:Column10>Bar</ns3:Column10>
			<ns3:Column11/>
			<ns3:Column12/>
		</ns3:udtTableType2>
	</ns0:udtTableParam2>
</ns0:uspProcedure>

Into the following SQL call:

DECLARE @scalarParam VARCHAR(255) = 'FOO';
DECLARE @udtTableParam1 dbo.udtTableType1;
INSERT @udtTableParam1 (Column1,Column2,Column3)
VALUES ('HOO','ASDF','Value3');
INSERT @udtTableParam1 (Column1,Column2,Column3)
VALUES ('BAW','Fruit','berry');
INSERT @udtTableParam1 (Column1,Column2,Column3)
VALUES ('BAR','Milk','Dairy');
INSERT @udtTableParam1 (Column1,Column2,Column3)
VALUES ('BAT','CRELO','CAS');
INSERT @udtTableParam1 (Column1,Column2,Column3)
VALUES ('BAZ','MARKER','CHALK');
DECLARE @udtTableParam2 dbo.udtTableType2;
INSERT @udtTableParam2 (Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12)
VALUES (NULL,NULL,NULL,NULL,NULL,'Basic',NULL,NULL,NULL,'Bar',NULL,NULL);
EXEC dbo.uspProcedure
    @scalarParam,
    @udtTableParam1,
    @udtTableParam2

Enjoy!

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\