For my day job, I had written an application which would capture all changes made to our development SQL database, script those changes using Microsoft SQL SMO, and automatically check in/out the files as needed so that changes to our database could be tracked efficiently and cleanly in TFS.
But I had a problem – Microsoft SQL SMO was failing to script a trigger. Specifically, it was generating the following error:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Trigger ‘as_invoice_insertToDocuments’. —> Microsoft.SqlServer.Management.Smo.FailedOperationException: Syntax error in TextHeader of Trigger ‘as_invoice_insertToDocuments’.
When I first googled this error, I found a couple of references and suggestions… Most of them said that this could be caused by nested comments, but my trigger had no comments at all. I tried changing the contents and header of the trigger, but nothing worked. When I tried debugging my program, I was stifled because I was unable to debug into the SMO DLL – all I could tell is that it was generating the exception.
My first attempt at diagnosing the error was to try to use dotPeek to disassemble the Microsoft.SqlServer.Management.Smo.DLL library to figure out why the code was throwing an error. This is what I found:
This narrowed it down a bit, but still, there were two possible lines that could have been generating the exception, with no way to distinguish between them (grrr!).
And that’s when I decided to try to debug into the SMO library. I reasoned that since I knew it was possible to debug into the .NET Framework, I might be able to do the same with this library, and I was right. Doing it was a bit tricky, though… First, I decided to use dotPeek’s symbol server. It’s possible that I could have used Microsoft’s symbol server too, since it was a Microsoft DLL, but I haven’t tried. So I started up dotPeek, turned on the symbol server, and selected the option to generate PDBs for all assemblies (just to be safe). Then I went back to Visual Studio (I have 2010), configured the symbol server with the http address provided by dotPeek, turned off the option “Enable Just My Code”, and tried debugging my program by F11 stepping into the SMO function call that was failing. On my first try, it didn’t work.
After a bit of research, I went back into dotPeek and opened the “Project/PDB Generation Log” window and saw that it was apparently still generating some PDBs and/or source files. So I waited for that to finish, then went back to Visual Studio. Before attempting to debug again, I tried a few more changes to Visual Studio options – specifically I checked the box “Enable .NET Framework source stepping” which seemed to do something – Visual Studio seemed to load and cache the PDB/source files. I’m not sure if that was a necessary step or coincidental to the fact that dotPeek was now finished generating it’s PDB files, but it worked – now when I F11 stepped into the SMO function call, I was viewing the SMO library source code. WIN #1!
As I started stepping thru the code to try to get to the line that was causing the Exception to be thrown, however, I realized it was going to be difficult to get what I needed. When I got to the CheckTextCorrectness method indicated by the stack trace, I found that it was in fact the FIRST throw that was executing, in response to the function call to CheckDdlHeader apparently returning false! I tried debugging into that function to figure out what going on, but I kept having difficulty because when I tried to analyze variables, the Visual Studio Watch window would just say “Cannot obtain value…as it is not available at this instruction pointer, possibly because it has been optimized away”, even though I was running in DEBUG mode and had “Optimize Code” unchecked in my project file settings. Actually that made sense since those settings would only affect code that was compiled on my machine, which the SMO dll was not. It was actually the .NET JIT compiler that was optimizing the MSIL. But I really needed to see those variables to figure out what was going on.
Then I found a web page with the information to avoid the optimizations. WIN #2! Just in case that page ever goes away, here’s the answer, lifted almost verbatim from that page (sorry!):
Ok, you think you’re cool when you get the capability to debug the .NET Framework source code all set up. You’re like, “I am all powerful!” Then you start noticing the oddities.
The problem is that ‘you’re debugging against retail-optimized code’. Fortunately, someone at Microsoft handed out the trick to disable these optimizations. Check out this link [EDIT: this link appears to be broken, the correct URL might be this one!] for more info, but basically there are only a few steps:
set COMPLUS_ZapDisable=1 cd /d "%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\ide\" start devenv.exe exit
NOTE: This page refers to “Microsoft Visual Studio 9.0” but your exe path might be different – for Visual Studio 2010 it’s “Microsoft Visual Studio 10.0”.
Once I did the steps above, it was like manna from heaven – I could now see the contents of all the variable that I couldn’t before, and I was finally able to diagnose (and work around) the problem in SMO. Since doing this, I’ve found several other references to things I could have checked, such as the Advanced Build settings, but I’m not sure if they would have worked for an externally-linked DLL being debugged thru the dotPeek symbol server. I’ve also found a method using an INI file [Microsoft reference] that controls the JIT optimization on a per-dll basis, although I’m not sure if that works independently every time the executable runs. For me, setting the environment variable was easier.
In case you are wondering, the workaround for my actual problem was simple. All I had to do was turn off the option “DdlBodyOnly” in the ScriptingOptions parameter passed to the Script() method. When this option is set to true, CheckTextCorrectness attempts to ensure that the DDL text starts with CREATE or ALTER, but I’m not sure why either (a) they would do this when DdlBodyOnly is set to true since it should only check the body and not the header, or (b) why it doesn’t fail for the other object types, or (c) why it doesn’t just generate the body without the header, which would have prevented me from ever setting it to true in the first place. I would consider this a bug one way or another in SMO. The result of setting this parameter to false is that the trigger script passes validation, but it also includes “SET ANSI_NULLS” and “SET QUOTEDIDENTIFIERS” lines at the top of the script, which is what I was generally trying to avoid by setting DdlBodyOnly to true.