He just cut me off! SSMS and Display Limits in the Query Grid

I’m using NLog in my current project to record errors, tracing, and other information while my application is running.  Some of the log messages, particularly the errors, can get quite long – thousands of kilobytes – because part of what I’m recording is the raw XML being passed into the app.

I have NLog configured to both email me when an error occurs and record the details to a table in SQL Server 2008.  I was running a load test recently, and noticed a few errors being recorded.  When I looked at the "Message" field in the table, I found that the message was being truncated – in fact, it was cutting the raw XML off.  Since I use this for reproducing the error, figuring out why it was being truncated was fairly important.

As I investigated more deeply, I found several things that were odd about this:

1. First, I checked the "Message" field in the table to make sure it was large enough to store everything.  I confirmed that it was defined as NVARCHAR(max) field, so there should have been ample room.
2. Next, I looked at the version of the error that got emailed to me.  That version had the complete message.
3. I next added a "File" target to my NLog.config file.  I wanted to see what would get recorded if I wrote the error message out to a text file.  That version also had the complete message.
4. Finally, the messages were always being truncated at 43,679 bytes.

So, it seemed that I was only having problems writing NLog error messages to SQL Server.  Perhaps there was something wonky with the "database" NLog target source.  I dug that up on the internets, and didn’t see anything that should be truncating the messages, let alone at 43,679 bytes.

I next decided to try inserting a large message directly into SQL Server, via some quickly-crafted C# logic.  I wanted to see if I could eliminate NLog as being the culprit here.  I inserted 30KB and 40KB messages, and those worked fine – I could insert them without errors, and I could get them out of SQL Server Management Studio (SSMS) completely intact.  When I tried a 50KB file, though, that failed in the same way – no errors on the insert, but when I queried the table in SSMS and copied the message to a text file, it was truncated.

What. The. Heck?!?

I did some more digging on the internets, this time for "sql server truncate" and other variants, and came across this post on SQLServerCentral.com.  From Jim McLeod’s answer:

"Yep – you got it. There’s a setting in Management Studio – Tools, Options, Query Results, SQL Server, Results To Grid, Maximum Characters Retrieved – Non XML Data, which defaults to 64KB."

So SSMS is limiting the text it’s going to display in the grid.  Ok, easy enough, I just have to increase this default limit.  I found the option:

And tried to change it to 100KB.  The dialog looked like it saved it, but when I went back in later it had been reverted to 65KB.  I tried the spinner controls, and it wouldn’t let me go above 65KB.

SSMS – why do you taunt me so?

Since Management Studio won’t let me view this via a query, perhaps I just need to extract it.  I first tried extracting this to a flat file, but that choked because the Message field was an NVARCHAR, and there was a problem with the character set.  I next tried exporting to Excel, but that choked because individual cells in Excel can’t hold more than 32KB.  In the end, I just wrote a little app in C# that extracts the message and saves it to a text file.

<rant>
I’m going to officially write this up as a case of "tool-fail", if only because it was failing silently.  Microsoft: At least tell me that the query I ran returned text results that were greater than 65KB, and would be truncated.  If you really want brownie points, tell me where this limit is defined in the Tools dialog, and even better LET ME INCREASE IT.
</rant>

Ok, I’m done.

Advertisement

CEO: Chief Exec() Oddity

I have a side project that I’m working on building a desktop application based running on SQL Server Express, and ran into an interesting issue with T-SQL.

The Setup
Since I’m distributing this to users who are geographically distant from me, and because those users are firmly in the “poweruser” category – not programmers – I needed to provide an easy way for them to get their local copies of SQL Server Express updated when I modified the table structure.  I decided to build a bootstrapper class that runs through a series of SQL Scripts – each one with its own version number attached – until the local database was up to date.  The local database contains a table that stores the current version of the database (which is designed to match the application version), and it will only apply the scripts that need to be applied, and in the order that they need to be applied.

The Issue
The most recent database modification I needed to make was to add a new column to a database and then populate that column with the ID of that row.  It would have been handy in this case if SQL Server allowed me to define the Default value for a column as the ID column, but it specifically disallows that:

Msg 128, Level 15, State 1, Line 1
The name “id” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Ok, so Plan B.  Use an ALTER statement to add the new column, and then an UPDATE statement to populate it:

ALTER TABLE MyTable ADD MyNewCol [int] NOT NULL DEFAULT 0;
UPDATE MyTable SET MyNewCol=ID;

Simple, right?  Unfortunately, this pair throws an error:

Msg 207, Level 16, State 1, Line 0
Invalid column name ‘MyNewCol’.

Even when I tried to run this in SQL Server Management Studio (SSMS), it fails with this same error.  I think what was happening is that the ALTER statement didn’t completely commit to the database in time for the UPDATE statement to run.  If I ran these two separately in SSMS (highlight first statement, CTRL-E, highlight second statement, CTRL-E) it worked fine, but I wouldn’t have that option in script.  The statements would have to run back to back, and I would need to find a way to make them play well with each other.

The Solution
I did some digging on the interweb and came across this post: http://www.sqlservercentral.com/Forums/Topic255804-8-1.aspx#bm256576.  The solution was to wrap both statements in an EXEC() function call – basically treating them as dynamic SQL:

EXEC(‘ALTER TABLE MyTable ADD MyNewCol [int] NOT NULL DEFAULT 0’);
EXEC(‘UPDATE MyTable SET MyNewCol=ID’);

This worked.

Asking for Trouble
Then I got gutsy.  There isn’t anything after the UPDATE statement that depends on the value of MyNewCol – why would I need the EXEC call around that statement too?  This pair of statements should work just as well, right?

EXEC(‘ALTER TABLE MyTable ADD MyNewCol [int] NOT NULL DEFAULT 0’);
UPDATE MyTable SET MyNewCol=ID;

Yeah – gutsy really wasn’t working for me today.  As it turns out, this also fails with the “Invalid column name” error.  As a result, I went with the dual-EXEC() calls.

It’s puzzling why the UPDATE needed to be invoked via EXEC() too, and unfortunately I don’t have a good explanation for that.  At least I have a method that reliably adds and populates my column, so that will have to be good enough for now.

SSMS Scripter Updated – Major Performance Improvement

I just released a new version of the SSMS Scripter – version 2.6.0 is now available at http://CodePlex.com/SSMSScripter.

The major update for this version was a huge (two-orders-of-magnitude-huge) improvement in the time needed for the Scripter to load up once invoked from SQL Server Management Studio.  With version 2.5.3, even databases with a handful of stored procedures, views, or user-defined functions would take 100-200 seconds for the Scripter to spin up.  It ended up being three lines of code to drop that load time to two seconds or less, even for large databases.

After doing some digging, I found this article by Bill Graziano dated November 29, 2005 (just about a year before I wrote the very first version of the Scripter): http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated.  This article was doing the same basic thing that I was – enumerating the non-system objects in a database via SMO.  Bill’s sample code was referencing the same property I am – IsSystemObject – and found it to be very slow.  Boy, this sounds familiar.

The reason for the lousy performance was explained by a member of the SQL Server Product Team, Euan Garden, who was responding to Bill’s post (a link to Euan’s comments is included in Bill’s article).  By default, the IsSystemObject property is not returned when you get a collection of objects back (like stored procedures).  As Euan explains, this was done to optimize how much SMO needs to retrieve with each request.  As it turns out, you can force specific properties to be returned, though:

Me._CurrentServer.SetDefaultInitFields(GetType(StoredProcedure), “IsSystemObject”)
Me._CurrentServer.SetDefaultInitFields(GetType(View), “IsSystemObject”)
Me._CurrentServer.SetDefaultInitFields(GetType(UserDefinedFunction), “IsSystemObject”)

I added these lines to the newly created (as the result of a refactoring) “CurrentServer” property in SSMSScriptor.vb.  This configures the SMO.Database object to bring IsSystemObject back as one of the properties of the StoredProcedure, View, and UserDefinedFunction objects.  By not doing this, the code was having to make a round trip to the database for EVERY object in the collection before it could say whether or not it was a system object.  This meant every stored procedure in the database, plus all of the ones in Master – thousands in all.  And it ONLY took 200 seconds.  Ahem.

In addition to the performance boost, I also updated the class headers throughout to reflect the license that the Scripter is being released under now, refactored the main interface class a bit, modified the interface so that it no longer closes once you finish scripting to a file or to the clipboard, and adjusted how the progress bar/status messages are displayed.

Enjoy!

SSMS Scripter Republished to CodePlex.com

I’ve moved the source code and latest release (2.5.2) to CodePlex: http://CodePlex.com/SSMSScripter.  This particular version was the last one released, and was done so here on my blog in February 2007.

When I first released it in late 2006 and early 2007, I wrote a series describing how the plug-in worked: http://Mark-Gilbert.com/category/sql-server/.  In the “Part 2” post in that series, I mentioned that I couldn’t find any decent documentation on how to write an SSMS plug-in.  At the time I had hoped that my series would help form that documentation.  Since then I’ve found another post at JCooney.net that goes into some additional depth from my series: http://jcooney.net/archive/2007/11/26/55358.aspx.  As the link implies, Joseph Cooney wrote this in late 2007, almost a year after I wrote mine and he had a similar lament with a section of the post titled “So Why Does This Qualify as a Black Art?”.  I agree with Joseph – writing one of these is definitely a black art, and I had to play a lot of code-roulette to get my plug-in to work.

For the next release, I have a performance-related update in mind, but I’m still sorting out how I might go about it.  The actual process of generating the scripts is relatively quick (measured in seconds), regardless of the number of objects being scripted.  The Scripter startup time can be substantial, especially for databases with large numbers of views/stored procedures/functions, and especially if you invoke the utility at the Database level in the SSMS hierarchy.

I also have not tried installing it with SQL Server 2008, and I’m eager to see how well (or not so well) it works there.

Let me know what else you’d like to see in the next release!

Tools and Toys Update

I’ve moved the source code for the WPF Media Player to CodePlex: http://codeplex.com/WPFMediaPlayer.  Currently the Player sits at version 0.1, but I’ve been working on a version 0.2 for a while now.  Ok, I was working on it earlier this year, and it’s been sitting dormant for a while now.  Ahem.  That should be changing in another week or so.  I’m wrapping up a personal project of mine, and then I focus on a handful of programming projects that I’ve been putting off for a while – this being one of them.

The WPF Media Player is actually the second project I’ve moved to CodePlex – the first being NAntRunner, now hosted at http://codeplex.com/nantrunner.  I have an update in mind for NAntRunner as well, which would bring it up to version 0.3.  Hopefully that will be posted in the next month or so.

Now that I’ve moved these to CodePlex, I’ve been thinking about the other items still hosted on my Tools and Toys page:

• The SSMS Scripter seems like a natural to post there, so that one will probably be next to move.  I have an update in mind for that one as well, but it’s not clear to me how to go about making it.  I’ll have to give it some more thought first.
• In the four months since I wrote and started using String Cleaning, I’ve found it to be eminently useful, but the core of the application is the one line of source described in this post.  I can’t really see it changing all that much, so I don’t think that’s worth setting up a new project for.  I have considered porting it to a PowerShell script (which would hopefully decrease the cold-start time), but that’s pretty low on the priority list at the moment.
• One-Three Back Solitaire hasn’t changed in years, and it was really my learning application for Test-Driven Development.  At this point I don’t think that will move over either.  If I ever decided to add on to it (write a web-based leader board using WCF, or something like that) I’d probably move it over first, just to simplify things.

There you have it.  I’ll keep you posted on progress on these projects, and I’d love to hear your feedback on what you find useful, and what you’d like to see in future releases.

SSMS Scripter Update – 2.5.2

This minor release corrected an issue where the Scripter would register itself for the Database node in the SSMS hierarchy, or an invalid node, but not both.  This is because the context string for a Database node consists only of the server and database names.  These two properties appear in every node below the database.  The old logic would look for a view, a stored procedure, a user-defined function, or their respective folders, and if it didn’t fall into one of these six categories it would assume the user had selected the database node.  This version corrects the issue by looking for specifically valid and invalid nodes, and only when the selected node does not fall into one of these two buckets will the Scripter treat the selected node as the Database node.

The updated version of the scripter can be found at https://markegilbert.wordpress.com/ssms-scripter/.

SSMS Scripter Update 2.5.1

A colleague of mine recently found that my Scripter broke with the release of SQL Server 2005 SP2, CTP.  Specifically, the Scripter would fail to add an item to the context menus for stored procedures, databases, etc..  This issue has been fixed in a new, minor release – 2.5.1.

I’ve also created a new page that contains all source code for past released versions of the Scripter, and the latest installation: https://markegilbert.wordpress.com/ssms-scripter/.

SSMS Scripter and SQL Server 2005 SP2 CTP

In mid-December 2006 Microsoft released SQL Server 2005 SP2 CTP (here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D2DA6579-D49C-4B25-8F8A-79D14145500D&displaylang=en#QuickInfoContainer).  While there are some nice things fixed, one thing that the CTP changes is how Management Studio plug-ins are “registered”.

All three released versions of my SSMS Scripter (available at http://www.devmi.com/meetings/2006/Pages/2006_12_07.aspx) throw an error when it tries to add an item to the content menus for Databases, Stored Procedures, etc., it throws an error to the effect of “can’t cast object of type ToolStripItem to MenuItem’.

I haven’t had a chance yet to dig into this yet, but once I do I’ll either issue an updated version, or links to articles describing this as a new bug that will be fixed when the full release of SP2 is out.  For the time being, I wanted to give everyone a heads up – the Scripter and SP2 don’t play well together.

SSMS Scripter 2.5 Released

The Scripter is now covered by the GNU General Public License.  The 2.5 release boasts improved integration with SSMS, and a few other miscellaneous enhancements:

• Carriage return issue fixed. The Scripter replaces standalone line feed characters with Carriage Return-Line Feed combinations.
• Scripter now uses the authentication used to connect to the database in SSMS, and doesn’t assume Windows Authentication.
• I added a message to the splash screen saying that it may take a while to initialize for a database with a large number of objects.
• Branches are only loaded into the TreeView when they are needed (when a branch is expanded by the user, or an object is defaulted when you first start the Scripter up), which makes for a quicker load time for the utility.
• Starting the scripter from the Database level now selects all objects in the tree.

Complete source code and the updated installer can be found at https://markegilbert.wordpress.com/ssms-scripter/.

SSMS Scripter Internals Part 3 of 3: “Making a Splash”

In the previous part of this series I looked at the project settings and code components that defined the Scripter as an SSMS Plug-in.  In this third and final piece, I’ll look at the splash screen used by the Scripter, and how threading was used to make it look pretty.

Because of the way that the Scripter digs into the source database when it first boots up, databases with a large number of objects (stored procedures, views, or UDFs) can take more than a few seconds to load.  Instead of just appearing to doing nothing except churn, I added a simple splash screen that appears before the main form is instantiated.

My first splash screen had a single line of text.  As a result, it was easy to miss it on screen, since it blended in well with the half-dozen other windows that I regularly have open.  I wanted to make it stand out more, and I thought a simple animated GIF would be the best way.  I found a GIF on the web that looked similar to the moving circle icons that Microsoft seems to have incorporated into all of their most recent applications (SQL Reporting Services, IE 7, etc.).  It was large enough, and animated enough, to draw the user’s eye to the splash screen, and specifically my one line of text encouraging them to be patient (and not give in to the Dark Side of the Force).

The splash screen is opened from the Invoke method of the ScripterMenuItem class.  Originally I simply instantiated the form as a dialog, and closed it when the SSMSScriptor form finished initializing.  This caused the splash screen to open just fine, but the animation wouldn’t show because the splash screen never got a message to repaint itself.

I had first considered setting a timer to go off every quarter second or so, and force the dialog to repaint itself, but that seemed like a bit of a hack.  Instead, I opted to instantiate the form in its own thread and then kill it when the main form finished loading.

To do this, I needed to add the System.Threading namespace to my list of imports in the ScripterMenuItem class.  Then, I created three local private variables: m_tiSplashScreen, m_frmScripter, and m_frmSplashScreen.  The second one was declared WithEvents so I could declare a public ScripterMenuItem method called ScripterLoadedEventHandler that handles, well, the ScripterLoaded event of the SSMSScriptor object (a custom event).

The following is the startup sequence of events:

• The ScripterMenuItem.Invoke method creates a new thread (stored in m_tiSplashScreen), and passes it the address of a private method of ScripterMenuItem called OpenSplashScreen. This private method instantiates the local private variable m_frmSplashScreen, and opens it as a dialog. I found that starting it as a separate form caused it to open, and then immediately close back down.
• The Invoke method then starts the new thread, which causes the splash screen to actually be displayed to the user.
• Finally the Invoke method creates the new SSMSScriptor form, and opens it.
• The Load event handler of SSMSScriptor does its normal startup, and at the very end raises the ScripterLoaded event.
• The ScripterMenuItem captures the ScripterLoaded event, and kills the splash screen thread (if it is still active).

Forcing the splash screen to run using a thread separate from the main program allowed it to refresh itself and show the animation properly.