Getting the schema for an XML column in SQL Server 2005

There is no question that the XML column data type in SQL Server 2005 opens up a large number of interesting possibilities. Some that I’ve even been lucky enough to work on myself. In this particular instance, I needed to find out the schema against which a typed XML document will be validated. The idea is to provide some client-side validation for the XML before submitting it to the database server. This doesn’t prevent SQL Server from redoing the validation (there isn’t any way to prevent that from happening that I’ve found), but it does reduce network traffic in the case of bad data.

The problem I faced was that the schema information is not readily apparent. After some digging, the I found that the following SQL will get the string representation of the schema.

SELECT Object_Name(Object_id) [TableName], col.name, s.name, XML_SCHEMA_NAMESPACE(sch.name,s.name)
   FROM (sys.columns col INNER JOIN sys.xml_schema_collections s ON col.xml_collection_id = s.xml_collection_id)
      JOIN sys.schemas sch on s.schema_id = sch.schema_id
WHERE Object_Name(Object_id) = @TableName 
   AND col.name = @ColumnName

The first two columns are the table and column name. The third column is the name of the schema used for the column. This is the name as found in XML_SCHEMA_COLLECTIONS. The fourth and final column contains the string representation of the schema.

Unexpected Locking in SQL Server 2005

I was working a little bit with SQL Server 2005 today and ran across an unexpected (at least to me) situation. From within the Management Studio, I opened up a table to view the contents.  Nothing complicated, although I did modify the default SQL slightly to reorder the results.  Then, in another query tab, I executed a script that did a DROP TRIGGER against the table that I had just opened. Problem is, the DROP statement just hung there. Waiting on a lock.  Specifically, waiting on exclusive access to the table. A lock that it couldn’t get because I had opened the table through Management Studio. Like I said, unexpected.

The solution was simple once I saw who was holding the lock. Close down the tab and everything was hunky-dory.  But the time I spent researching the problem is gone, never to be recovered. I also did a bit of digging into why this was so unexpected. It doesn’t appear to happen in Enterprise Manager. Simply opening a table and retrieve all of the rows does not establish a lock on the records. Doesn’t even appear to if you start to edit a particular record. Chalk it up to one more thing that is new and improved.

 

Invalid XPath Expression in a BizTalk Mapping

While working on a BizTalk mapping for a client, I ran across an unusual problem.  When the mapping was tested, an XSL transform error was thrown. Specifically, the error message was:

XSL transform error: (0,0) : 'userCSharp:(string(*[local-name()='Times' and namespace-uri()='']/*[local-name()='WindowCloseTime' and namespace-uri()='']/text()))' is an invalid XPath expression. 'userCSharp:(string(*[local-name()='Times' and namespace-uri()='']/*[local-name()='WindowCloseTime' and namespace-uri()='']/text()))' has an invalid qualified name.

Since this came from the XSL that is generated by the mapping, the first step I took in identifying the problem (ok…the first step after scratching my head for 30 minutes or more) was to examine the XSL for the mapping. This can be created by running the Validate Map option from the Solution Explorer. My search for this particular XPath expression found it in an XSL segment that looked like the following.

<xsl:variable select="userCSharp:(string(*[local-name()='Times' and namespace-uri()='']/*[local-name()='WindowCloseTime' and namespace-uri()='']/text()))" name="var:v18"></xsl:variable> 
<xsl:if test="string($var:v15)='true'">
  <xsl:variable select="string($var:v18)" name="var:v19"></xsl:variable>
  <xsl:attribute name="FIRST_PU_MINUTE">
     <xsl:value-of select="$var:v19"></xsl:value-of>
  </xsl:attribute>
</xsl:if>

Now that I have a better look, it appears that the userCSharp namespace has attached itself to an XPath expression. Normally, it is attached to a function that is defined further down in the XSL file. No wonder it’s invalid.

The good thing is that I was able to see from the attribute name that the problem is in the mapping for the FIRST_PU_MINUTE attribute. When I looked, it turned out that the scripting functoid I was using hadn’t been defined. Instead of defining the external assembly function, I had left it as the default, commented-out code. Once I corrected that problem, my transform error went away.

Update: The XSL fragment didn't appear when I first posted it. My bad. Now it makes much more sense.

Updating Config Files in ASP.NET 2.0

One of the new features of ASP.NET 2.0 is the ability to not only more easily read from config files, but also to update them. If you’re looking for a pretty good description of what’s possible, check out the ASP.NET QuickStart Tutorial. Going over the basic process isn’t the point of this post. Instead, I want to talk about an unexpected, but yet expected, side effect.

The situation is quite common. Your web site utilizes Session variables. Over the course of your application, you update one of the config files that you had defined. All is fine and wonderful right up to the point where a Session variable was accessed. For some unknown reason, it was gone.

If this particular situation sounds familiar, there could very well be a good reason. It is similar to what happens if web.config gets updated on an active virtual directory. At least the symptoms are. What is actually happening is that the web application gets restarted. So it should be a surprise, should it.

Well, no. With one minor exception. In the particular case we were looking at, the config file was actually defined as an external file. So in web.config there was an entry similar to:

<customAppSettings configSource=”customAppSettings.config” />

So the actual updates were taking place outside of the web.config file. Since web.config isn’t updated, the application shouldn’t restart. Should it?

Well it turns out that the default behavior of the Config API is that changes to the external file cause an application restart just like web.config. And with the application restart comes the loss of Session information

There is a way to modify this default behavior. In the <section> tag where the config section is defined, the restartOnExternalChanges attribute gets set to false.

<section name="customAppSettings" type="ObjectSharp.Demo.CustomAppSettingsSection, ObjectSharp.Demo, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a140608022e61b4b" restartOnExternalChanges="false" />

All of a sudden updating the config file doesn’t cause an application restart and all of the little session variables remain fat and happy.

 

Missing vcvarsall.bat in VS.NET 2005 Command Prompt

I took a few moments to dig into a nagging problem this weekend.  Seems that whenever I launched the Visual Studio 2005 Command Prompt, I received an error message indicating that a file called vcvarsall.bat couldn’t be found.  A thorough search of the Visual Studio 8 directory found nothing of that name, although there was the old standby of vsvars32.bat. I took a further search through ladybug and I see that it had already been reported and marked as fixed for the release version. If you find this annoying, there is a workaround available.  The details can be found at http://lab.msdn.microsoft.com/productfeedback/ViewWorkaround.aspx?FeedbackID=FDBK24480#2, but it works out to be either install C++ or change the shortcut for the command prompt to run vsvars32.bat.

The Dual Life of Code Behind

When you create an ASP.NET page using Visual Studio .NET, the default processing model is to use code-behind (the basics of which I described here). One of the more interesting aspects of code-behind is that you can specify the code for the code-behind assembly using two different techniques.  The first, and most commonly used is to build an assembly and deploy it to the bin directory on the web server.  The second mechanism is to specify the file containing the source code in the Page directive for the ASPX file.

Compiled Assembly

<%@ Page Language=”C#” Inherits=”ObjectSharp.WebPageClass” %>

Source Code

<%@ Page Language=”C#” Src=”WebPageClass.cs” %>

There are pros and cons to both of these approaches.  Functionally, they are equivalent.  When the first request is made to the page, the source code file is compiled and the resulting assembly loaded into memory. For every subsequent request, no compilation is required. Not only does this mean functional equivalence, but it also means that there is no performance penalty for deploying source code instead of a compiled assembly.

The biggest downside of the source code technique is exactly that, however.  That the code file needs to be deployed onto the web server.  This, naturally, has the potential to be a security problem.  A deployed assembly doesn’t have quite the same exposure, if only because it can be deployed into the Global Assembly Cache instead of directly into the application’s virtual server.

While it might seem that the security risk might tilt the tables entirely towards compiled assemblies, that isn’t true.  The problem with compiled assemblies has to do with updates to the web site.  When a new version of a compiled assembly is deployed to the web server, IIS is smart enough to detect the change.  The current web application is stopped and restarted so that the modified assembly can be loaded.  Unfortunately, the stopping and starting of the web application means that every Application and, more importantly, Session variable is discarded.  Depending on how the web application has been designed, this can be a significant problem.

Source code deployment doesn’t suffer from the same problem.  As with compiled assemblies, IIS monitors the source code files, so that when an update occurs, a recompilation takes place.  So the updates do get activated immediately.  The difference is that the web application does not have to be stopped and started in order to get the changes in place.

Choices, choices.  The trick to ASP.NET, as it is with almost any discipline, is to understand not only the choices but when each can and should be used.  This not only helps you design better web applications, but also solve those nagging times when the web application seems to restart for no apparent reason.

App.Config for BizTalk 2004

As part of a current project, I'm utilizing some standard .NET assemblies in the BizTalk mapping.  Because these same assemblies can be used in other non-BizTalk applications, they utilize the app.config file to store potentially changing information.  Naturally, because these assemblies and indeed pretty much all of BizTalk run out of the Global Assembly Cache, the location for the app.config file becomes a little challenging to determine.  While there are a number of potential solutions for where the information can be stored, I found that the simplest is to place the config information in to the BizTalk server config file.  This file is called BTSNTSvc.exe.config and can be found in the installation directory for BizTalk.  Also, if you're going to make a change to this file, you need to stop and restart the BizTalk services to get it picked up.

Installing Beta 2

I just finished installing Beta 2 of Whidbey. For the most part, it went pretty smoothly. 

I initially started with a VPC image that had the February CTP installed.  I tried to uninstall the bits using the technique suggested on the download page.  Didn't work for me.  To be fair, I didn't uninstall in the same order that they listed.  I just started at the top of the Add/Remove Programs list and went down. I was unable to uninstall any of the SQL Server Express pieces.  The uninstall threw up a fatal error during installation message box.

Since it was just a VPC image and I was in a hurry ;), I just created a VMC image from scratch.  Now the installation worked to perfection. And I have a Beta 2 version to play with.  There goes the evening.

Philosophy, Debugging and Star Wars

I find it amazing how certain quotes stick with you over a much longer period of time then they probably should.  Allow me to give two examples.  The first is an oft-repeated phrase spoken by Yoda in the second (or fifth, depending on your numbering system) Star Wars movie:  "Try not.  Do or do not.  There is no try'

Yeah, I know.  I simultaneously dated myself and added a geek label to my business card.

The second quote is a little more professional and, unfortunately, I don't know the origin. "Debugging is the process of discovering how many invalid assumptions you made during  the design and implementation of your program".

While cruising my blog roll today, I came across this post from Ted Neward that links these two apparently unrelated quotes.  Nice work and something to keep in mind the next time you're faced with a challenging problem in your code.

Creating Your Own EDI Schema in BizTalk - Part II

See.  I told you.

Actually, this was an error of my own making.  The error that appeared on building the schema project was

Error in inserting document definition: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK__codset__2D27B809'. Cannot insert duplicate key in object 'codset'

A similar error was displayed for the segcon and elmnts tables as well.

The problem was that I had badly defined the EDI schema.  The schema I built had two N1Loops (that is a loop that contained N1, N3, N4 and G61 segments). The second loop also contained one more segment, meaning that I couldn't reuse the loop. But, in a moment of boneheadedness, I created duplicate definitions for the N1, N3, N4 and G62 segments.  The result was an error such as the one shown above.  The solution is to rename the duplicated segments (say N1_2) while (and this is important) keeping the tag_name attributed of the recordInfo tag at N1.

One thing I will say is that the message that appears is completely useless for identifying the duplicated elements.  To see what went wrong, I modified the table definition for segcon, sgmnts, codset and elmnts to get rid of the primary key violation message.  Then a build would work to success.  Once the build was complete, I used the following statement to see the duplicated records.

SELECT fc, envr, code, COUNT(*)  from codset GROUP BY fc, envr, code ORDER BY COUNT(*) DESC

Any row with count greater than 1 would cause the violation exception.  The envr value contains the segment that is duplicated.

004010204_004010_DEFAULT_X_<G6101>

means that the G61 segment is being duplicated.  Realize that if G61 is duplicated, then you will also seen records for G6102, G6103, etc.

Once I had solved all of my duplication issues, I needed to rebuild the database schema.  For me, I only had a couple of schemas, so it was no problem for me to rebuild.  I deleted all of the tables and rebuilt the table structure using the Edi.sql script found in c:\Program Files\Microsoft BizTalk Server 2004\EDI\Adapter\bin\config. If you have already compiled a number of schemas and don't want to go through it again, make a backup of the BizTalkEdiDb database before removing the primary keys and perform a restore when all of the problems have been addressed.