A Bit Scatterbrained…

Lately I find myself mixing my PascalCase with my camelCase, calling alloc init instead of new, using [ ] instead of (), and writing awfully verbose method names all over the place. I justify my errors with a glimpse at my average day here lately:

Enterprise Library Sometimes Chops XML into 2033 Characters

This morning I began what I thought would be an uneventful task in .NET: connect to one database, download as XML some rows from a table that have changed since a given time, then pass that XML to a stored procedure in another database so that the changes can be merged. Just two tables: one relatively small, the other just a 2-column relationship table for the first table. Here’s a contrived snippet to serve the same purpose:

The SQL:

CREATE PROCEDURE dbo.GetXmlValue

AS

SELECT TOP 100
 *
 FROM sys.all_columns
 FOR XML AUTO, ROOT('Root')

The Code:

Database db = GetDatabase();

DataSet set;

using (DbCommand cmd = db.GetStoredProcCommand("dbo.GetXmlValue"))
 {
     set = db.ExecuteDataSet(cmd);
 }

In my application, it was this second table that tripped me a bit. The first only had a few changed rows, so the XML was only 500-1000 characters. The second table generated a much longer XML string – 3864 characters long. My initial plan was to return two tables, each 1 column and 1 row. When using Database.ExecuteDataSet to fetch the results, however, the second table yielded two rows. I backtracked, second guessed myself, and even grabbed a couple of guys in the hall to double check my code. No one could explain it. It seemed odd that this hasn’t come up before for someone here. I did a bit of Googling and only found a couple of results. This Microsoft Knowledgebase Article just says it’s “by design” and to use SQLCommand.ExecuteXmlReader.

The next question that came up was “Well, what does it do if I return a long XML value among other values in the same row?” I tested this out and everything worked as expected. All of the XML was in a single column value as it should be.

I still can’t exactly explain it. ExecuteScalar has a character limit of 2033 characters. I suspect that because each table in my result set is just a single column value, something somewhere is relying on ExecuteScalar.

My solution? In my stored procedure I just stored each of my table results as an XML typed variable, then returned the two together in the same row.

GTIN 12 UPC Check Digit Calculation in SQL Server 2008

I recently needed to generate the check digit for a GTIN-12 UPC code in SQL. The calculation method for the check digit varies depending on what kind of barcode you’re implementing, so I popped on over to Google to get the necessary algorithm. Naturally, and regardless of simplicity, I checked for any available code snippets. The only I found weren’t GTIN-12, and were in C or C++. So for anyone looking to do it (or something similar) in T-SQL, here’s a scalar function to do just that.

CREATE FUNCTION dbo.CalculateBarcodeGTIN12CheckDigit(@input CHAR(11))
RETURNS INT
AS
BEGIN
	DECLARE @evenDigitSum INT = 0
		,@oddDigitSum INT = 0
		,@i TINYINT = 0
		,@result INT;

	-- check if given Barcode is Numeric , if not return error status -1
	IF(ISNUMERIC(@input) = 0
		OR LEN(RTRIM(LTRIM(@input))) != 11)
		RETURN -1

	-- start the compute BarCode checksum algorithm
	SET @i = 1
	WHILE (@i <= 11)
		BEGIN
		 --Add odd and even digits separately;
		 IF((@i % 2) = 0)
			 SET @evenDigitSum += CONVERT(TINYINT, SUBSTRING(@input,@i,1))
		 ELSE
			 SET @oddDigitSum += CONVERT(TINYINT, SUBSTRING(@input,@i,1))
		 SET @i += 1
		END

	--As per: http://en.wikipedia.org/wiki/Universal_Product_Code
	--Multiply odd sum by 3, add to even sum, and mod 10.
	SET @result = ((@oddDigitSum * 3) + @evenDigitSum) % 10;
	IF(@result = 0)
		RETURN 0
	ELSE
		RETURN 10 - @result;

	RETURN -1
END
GO

Scalar functions are killer, but I’ll only ever be using it for a single record.

SSMS “Connect to Database Engine” Prompt Every Time A Saved Query Is Opened

About two weeks ago, I noticed Sql Server Management Studio’s behavior changed on me. I often use saved queries for deployment, so I’m saving/opening them all day long. For as long as I’ve been using SSMS, it has always just used my existing connection in Object Explorer as the connection context for the saved file. Something changed, and I now have to react to this dialog every time I open a file. It doesn’t matter if I have a connection established in object explorer or not.

My irritation level finally rose to the point this morning that I decided to address it. I’d given Googling it a solid 25-35 minutes worth of effort before I decided to bother anyone else about it. All I saw were people complaining of the same problem with no real solutions. Most of the responses were justifications for the new behavior, but they just didn’t seem to get the OP’s problem: The behavior used to be different! Something changed!

Dave’s installation was acting exactly like mine used to – the way I want it to. Chris Benard simply “rarely uses saved queries.” Has anyone ever experienced this and know the solution? It’s driving me nuts!

SSMS Tools Pack Makes My Life Easier

I don’t currently do a lot of whiz-bang magic in my current job role. The majority of my day consists of writing reports, which can be boiled down to any of the follow:

• Writing a stored procedure to generate the report data
• Creating the report layout in a WYSIWYG-style editor.
• Writing SQL script to deploy those changes to our customers.

The most exciting part is generally when I need to roll up my sleeves and figure out something fancy for the stored procedure. Much more often though, I’m hammering out INSERT scripts or UPDATE scripts with some XML stored as VARCHAR(MAX).

Because of the robotic nature of some of this, I decided to start using some code snippets. I couldn’t find any snippet feature in SQL Sever Management Studio, so I discovered SSMS Tools Pack.

This add-on has already saved me an enormous amount of time. I’ve only used a few of the features so far. SQL code snippets have obviously saved me a lot of time, but I found some other uses that I didn’t expect. I often need to test a report across multiple databases. With the tool pack, I can right click on a script, click “Run on multiple targets” and be prompted with a list of text boxes for each database I want to run it against. There’s nothing particularly magical about this process – it just creates a new tab and separates copies of the script with ‘USE [DatabaseName]’ lines, but it saves me a reasonable amount of time.

If you spend much time in SSMS, I recommend giving it a whirl. The full list of features:

• Run one script on multiple databases
• Copy execution plan bitmaps to clipboard or file
• Search results in Grid Mode or Execution Plans
• Generate Insert statements from resultsets, tables, or database
• Regions and Debug sections
• Running custom scripts from Object Explorer
• CRUD stored procedure generation
• New query template

SSMS Tools Pack