A random spillage of programming (and other) thoughts

Archive for the ‘SQL’ Category

Debugging into Compiled Assemblies, tracking down an apparent bug in Microsoft SQL SMO

Posted by Michael Bray on February 6, 2015

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:

Screenshot - 2_6_2015 , 1_06_46 PM

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.

“Wait, why I can’t get the value of that variable?!”

“Why did it step there? It should have stepped here?!”

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:

  • Create a .cmd file that sets an environment variable and then launches Visual Studio. Name it whatever you want (e.g. DisableOptimizationsInVisualStudio.cmd). It’s contents should be:
set COMPLUS_ZapDisable=1
cd /d "%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\ide\"
start devenv.exe
  • Launch Visual Studio with this .cmd file.
  • Once in Visual Studio, disable the Visual Studio hosting process:

Right click on your project and choose “Properties”.

Choose the “Debug” tab and uncheck “Enable the Visual Studio Hosting Process”.

  • Launch your application in the debugger.


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.

Posted in .NET, .NET Framework, Bugs, Debugging, SMO, SQL, Visual Studio | Tagged: | Leave a Comment »

Linq2Sql Caching with SqlDependency

Posted by Shaun McDonnell on November 25, 2009

It took me a while to figure this one out.  I almost thought it was impossible to implement it in a ‘good way’.  However, extension methods saved the day.  So, if you want to use Sql Service Broker dependencies in your Linq2Sql code, you’ll need an extension method like this:

   1: public static List<T> LinqCache<T>(this IQueryable<T> q, DataContext dc, string key)
   2: {
   3:     SqlDependency.Start(dc.Connection.ConnectionString);
   4:     var cachedItem = (List<T>)HttpContext.Current.Cache.Get(key);
   6:     if (cachedItem == null)
   7:     {
   8:         TracingHelper.Write("No cache key [{0}] found for query: {1}", key, dc.GetCommand(q).CommandText);
   9:         string connectionString = dc.Connection.ConnectionString;
  10:         string command = dc.GetCommand(q).CommandText;
  11:         using (var sql = new SqlConnection(connectionString))
  12:         {
  13:             sql.Open();
  14:             using (var cmd = new SqlCommand(command, sql))
  15:             {
  16:                 foreach (DbParameter dbp in dc.GetCommand(q).Parameters)
  17:                 {
  18:                     cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value));
  19:                 }
  20:                 SqlCacheDependencyAdmin.EnableNotifications(connectionString);
  21:                 string notificationTable = q.ElementType.Name;
  22:                 if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString).Contains(notificationTable))
  23:                     SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, notificationTable);
  24:                 var sqldep = new SqlCacheDependency(cmd);
  26:                 cmd.ExecuteNonQuery();
  27:                 cachedItem = q.ToList();
  28:                 HttpContext.Current.Cache.Insert(key, cachedItem, sqldep);
  29:             }
  30:         }
  31:     }
  32:     else
  33:     {
  34:         TracingHelper.Write("Cache key [{0}] FOUND for query:  {1}", key, dc.GetCommand(q).CommandText);
  35:     }
  37:     SqlDependency.Stop(dc.Connection.ConnectionString);
  38:     return cachedItem;
  39: }

Now, in order to use this extension method in your queries, you’ll need to do something like this:

   1: public List<Company> Get()
   2: {
   3:     using (var dataContext = new RoutePointRepositoryDataContext(Db.ConnectionString))
   4:     {
   5:         var items = dataContext.Companies.LinqCache(dataContext, "Companies.All");
   6:         return items;
   7:     }
   8: }

The downside is that I haven’t figured out a good way to implement this when retrieving one record from the database.  So, you’ll have to do this:

   1: public Company Get(int id)
   2: {
   3:     using (var dataContext = new RoutePointRepositoryDataContext(Db.ConnectionString))
   4:     {
   5:         var company = dataContext.Companies.Where(c => c.Id == id).LinqCache(dataContext, "Company.Get." + id.ToString()).Single();
   6:         return company;
   7:     }
   8: }


MSDN did have some code similar to this but it did not work and there were no examples.  This works and and the examples should be enough to get you started.


Posted in .NET, LINQ2SQL, SQL | Leave a Comment »

Danger when using UNION in SQL

Posted by Michael Bray on December 19, 2007

Interesting thing I learned about UNION today – it removes duplicated result set rows.  At first glance, it may not be a big deal – it is clearly documented in the Transact SQL SELECT statement docs, and after all, this matches the concept of UNION in set theory.  However, what I didn’t expect was that not only does it remove the rows that are duplicates BETWEEN the two queries, but it will also remove duplicated rows WITHIN each of the individual queries.

I found this when trying to calculate the sum of a single column, where the data was actually stored in two different tables.  So the query I was trying to execute was:

SELECT SUM(Duration) FROM (SELECT Duration FROM tblA [WHERE…] UNION SELECT Duration FROM tblB [WHERE…]) tmp

The individual result sets were:

tblA.Duration tblB.Duration
8 0
8 2
6 2
6 2
8 0

And the result of the SELECT SUM(Duration) was 16.  Huh??  It’s 16 because the Duration values are selected, then UNIONed, then the duplicates removed, resulting in a list of ‘8, 6, 2, 0’ to be summed.  I would have expected that the UNION find the duplicated rows between the two sets, and then doing the UNION, resulting in something more like ‘8, 0; 8, 2; 6, 2’ to be the rows that are kept after the removal of the duplcates, followed by the sum resulting in 26. 

Of course, this isn’t the number I want either, and the answer is to include the ‘ALL’ keyword along with UNION (“UNION ALL”) which disables the removal of duplicates.  Then the sum works perfectly, giving an answer of 42.  (Of course!!!  :))

Posted in SQL | Leave a Comment »