Sergio and the sigil

Rule "Previous releases of Microsoft Visual Studio 2008" failed

Posted by Sergio on 2010-03-07

Today I was trying to install SQL 2008 on my box and the setup stopped after checking a bunch of rules. The error message was the title of this post.

A quick search on the internet revealed that somehow the installer didn't believe I had VS 2008 SP1 installed, which I did. The recommendations in the KB article were kind of insulting. There's no way I'd spend hours of my day uninstalling and reinstalling VS and SQL — sorry, no chance. I also could not accept not installing the Management Tools, for example. I also did not have any Express version of VS or SQL installed in this box.

A little snooping around with ProcMon led me to the following registry key:


In that key I noticed the suspicious values:


Without quitting the SQL server installer validaton screen, I changed these values to what you see below, crossed my fingers and rerun the installer validation, which passed!


Now, I didn't really guess those values. I looked in a sibling registry key (...Servicing\9.0\PRO\1033) and saw that it contained those new values, then I copied them.

I think I didn't break anything. So far all seems to be working. But, as usual with anything related to manual registry hacking, you have to be really insane to change your settings because you read on a random blog on the 'net. I'm just saying... Don't come crying if your house burns down because of this.

UdtTypeName and .NET data types in SQL

Posted by Sergio on 2008-06-11

We are working on SQL 2008 for a new project with the intent to leverage the new data types, in this case especially the HIERARCHYID data type. One of the stored procedures we created had a parameter of type HIERARCHYID.

The code that calls the stored procedure was something like this.

SqlHierarchyId nodeValue = GetNodeValueFromSomewhere();
var cmd =new SqlCommand("usp_EnableNode");
cmd.CommandType = CommandType.StoredProcedure;
var  nodeParam = myCommand.Parameters.Add("@Node", SqlDbType.SqlHierarchyId);
nodeParam.Value = nodeValue; 
var enableParam = myCommand.Parameters.Add("@Enable",  SqlDbType.SqlBit);
enableParam.Value = true;

When running this code we were getting the error "UdtTypeName property must be set for UDT parameters." Since the HIERARCHYID data type is a .NET type in SQL 2008 and we had not played with .NET types in SQL 2005 yet, we scratched our heads for a few minutes.

We tried both:

nodeParam.UdtTypeName = typeof(SqlHierarchyId).Name; 


nodeParam.UdtTypeName = typeof(SqlHierarchyId).FullName; 

But the solution was much simpler:

nodeParam.UdtTypeName = "HierarchyId";

This should be needed for the new GEOMETRY and GEOGRAPHY data types as well. Maybe this helps someone else.

The new data types in SQL Server 2008

Posted by Sergio on 2008-04-06

I can't wait to use SQL 2008. I wish I could convince my DBA to jump on it as soon as it goes RTM, but the data peeps don't suffer of the same short attention span as us developers.

Reading a recent Technet Magazine article and attending the launch event in Chicago made me drool for the new features. And I don't usually care that much for database technologies.

The new features that are more within reach for developers are the new data types.

Spatial Data Types

A good part of my work involves GIS databases and overlaying business intelligence on top of it (or deriving BI from it). As such, it's with great interest that I see the addition of spatial data types in SQL 2008.

With SQL 2008 I'll be able to treat all those latitude/longitude pairs as first class citizens in my tables and stored procedures. Add to that all the new supporting functions that come with these new data types. That means I do not have to write my own functions to determine the bounding rectangles around a collection of points or shapes. Nor will I have to create my own function to calculate the distance between two coordinate points considering the Earth's shape. Believe me, this can be huge.

Suppose I have some oddly-shaped polygon that represents a geometric region like a mining field, a flood zone, or a pizza delivery service area (or anything small enough that can be considered flat, discarding the Earth's curvature).

DECLARE @shape geometry
SET @shape = geometry::STPolyFromText('POLYGON ((
                 47.653 -122.358, 
                 47.653 -122.354, 
                 47.657 -122.354, 
                 47.653 -122.350, 
                 47.645 -122.350, 
                 ... (snip)
                 47.651 -122.355, 
                 47.653 -122.358))',  0)

First of all, it's nice to have a data type to represent this polygon, and not having to use my own parsing mechanism or using other tables to store that. Another good thing is that these data types follow the OGC standards.

To determine the bounding rectangle for the shape defined above, it's as simple as calling a method on that shape object.

SELECT @shape.STEnvelope().ToString()
-- outputs something like
	47.657 -122.358,
	47.657 -122.350,
	47.645 -122.350,
	47.645 -122.358,
	47.657 -122.358))

Did I say object? Doesn't the syntax above look like plain old .Net? Exactly. The spatial data types were implemented in .Net, leveraging the capability of adding .Net user defined types, which was introduced in SQL 2005.

Spatial data types is a large topic, maybe I'll come back to it with a longer post once I have a chance to build an actual application with them. For now, I'll just point you to this nice series from Jason Follas.

The DATE type

When you don't need the time portion of a DATETIME, you can use the new 3-byte DATE type to store dates from 1/1/0001 to 12/31/9999. This coincides with the range (minus time) of the .Net System.DateTime structure.

SELECT @d -- outputs '2008-04-06 00:00:00.000'

SET @d = '1234-03-22 11:25:09'
SELECT @d  -- outputs '1234-03-22  00:00:00.000'

The TIME type

There are also scenarios when we only need the time portion of a DATETIME, that's where the new TIME type comes in handy.

The TIME data type's size can be 3, 4, or 5 bytes, depending on the chosen precision. The default precision is 7 digits, but you can specify the precision you need when declaring the data.

	T1	TIME(1),
	T5	TIME(5),
	T7	TIME(7)


SELECT * FROM #times

T                T1               T5               T7
---------------- ---------------- ---------------- ----------------
01:02:03.1234567 01:02:03.1000000 01:02:03.1234600 01:02:03.1234567

The DATETIME2 type

If you liked the increased precision of the TIME type and the increased date range of the DATE type, you'll be happy to learn that the new DATETIME2 stored date and time, with greater precision and range, basically combining those other 2 new data types.

DECLARE @dateA DATETIME2 = '2008-04-05 01:02:03.12345'
PRINT @dateA -- outputs 2008-04-05 01:02:03.1234500
DECLARE @dateB DATETIME2(4) = '2008-04-05 01:02:03.12345'
PRINT @dateB -- outputs 2008-04-05 01:02:03.1235

With this new data type you can have your dates range from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999. Again, this works well with .Net applications. You can initialize the DATETIME2 with string literals as shown above. These literal can be either ODBC format or ISO-8601 (sortable date time format, same as DateTime.ToString("s") in .Net).


With this new data type, SQL Server learns about time-zones. This is of particular interest to me because of the globally distributed data and users that I have to deal with.

The DATETIMEOFFSET type ranges in size from 8 to 10 bytes. It's precision is also defined at declaration time, just like the other new types shown above.

-- time stamp on Central Daylight Time
DECLARE @today DATETIMEOFFSET = '2008-04-05T01:02:03.1234567-05:00'
PRINT @today -- outputs 2008-04-05 01:02:03.1234567 -05:00
DECLARE @today2 DATETIMEOFFSET(2) = '2008-04-05T01:02:03.1234567-05:00'
PRINT @today2 -- outputs 2008-04-05 01:02:03.12 -05:00

We can initialize the DATETIMEOFFSET values using literal strings in ISO-8601 YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm] or YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (for times exclusively in UTC.)


About time, thank you. That's all I'm going to say. After having to endure the pain of representing, traversing, and querying hierarchical information stored in flat tables, I plan to use this data type extensively and never having to look back ever again.

You probably know what I'm talking about. All those foreign keys that point to the same table, like ParentCategoryID in a Categories table or ReportsToID in an Employees table.

Now we can simply define a column of type HIERARCHYID that will keep track of the record's position within the hierarchy being managed.

-- our Categories table
CREATE TABLE #Categories (
	CategoryID INT IDENTITY(1,1),

We will need to populate the CategoyNode field with the correct hierarchy information. The first element (the root) is the odd man out. After the root node, the process is quite repetitive.

-- the root category
DECLARE @root HIERARCHYID = hierarchyid::GetRoot()
INSERT INTO #Categories (CategoryNode, CategName) 
	VALUES (@root, 'All #Categories')

-- insert the 'Electronics' category
SELECT @electronics = @root.GetDescendant(NULL, NULL)
INSERT INTO #Categories (CategoryNode, CategName) 
	VALUES (@electronics, 'Electronics')

-- insert the 'Music' category after 'Electronics'
SELECT @music = @root.GetDescendant(NULL, @electronics)
INSERT INTO #Categories (CategoryNode, CategName) 
	VALUES (@music, 'Music')

-- insert the 'Apparel' category between 'Electronics' and 'Music'
SELECT @music = @root.GetDescendant(@music, @electronics)
INSERT INTO #Categories (CategoryNode, CategName) 
	VALUES (@music, 'Apparel')

-- insert some children under 'Electronics'
--   We could do a simple @category.GetDescendant() but, let's
--      show something that is more likely to happen
SELECT @video = CategoryNode.GetDescendant(NULL, NULL)
  FROM #Categories WHERE CategName ='Electronics'
INSERT INTO #Categories (CategoryNode, CategName) 
	VALUES (@video, 'Video Equipment')

-- insert some children under 'Video Equipment'
SELECT @tvs = @video.GetDescendant(NULL, NULL)
INSERT INTO #Categories (CategoryNode, CategName) 
	VALUES (@tvs, 'Televisions')

SELECT @players = @video.GetDescendant(NULL, @tvs)
INSERT INTO #Categories (CategoryNode, CategName) 
	VALUES (@players, 'DVD - BluRay')

When we query the table, the output from the CategoryNode column reflects the position of the record in the hierarchy, similar to directory paths.

	CategoryID, CategName, 
	CategoryNode.ToString() AS Path
FROM #Categories
CategoryID  CategName         CategoryNode   Path
----------- ----------------- -------------- ---------
1           All #Categories   0x             /
2           Electronics       0x58           /1/
3           Music             0x48           /0/
4           Apparel           0x52C0         /0.1/
5           Video Equipment   0x5AC0         /1/1/
6           Televisions       0x5AD6         /1/1/1/
7           DVD - BluRay      0x5AD2         /1/1/0/

Note that the numbers that are separated by / in the Path column are not the CategoryID values. They are values that represent the sequence of the record within its siblings. The value is directly related to where we positioned the node when we called GetDescendant. The HIERARCHYID data type stores a binary value, as shown in the above output.

Now let's see how we would return all the items under a given category, recursively.

DECLARE @electronics_Categ HIERARCHYID
SELECT @electronics_Categ=CategoryNode 
	FROM #Categories WHERE CategoryID=2
SELECT CategoryID, CategName, CategoryNode.ToString() AS Path 
	FROM #Categories 
	WHERE @electronics_Categ.IsDescendant(CategoryNode)=1
CategoryID  CategName       Path
----------- --------------- --------
2           Electronics     /1/
5           Video Equipment /1/1/
6           Televisions     /1/1/1/
7           DVD - BluRay    /1/1/0/

Being able to perform these sorts of queries without having to resort to Common Table Expressions makes the queries so much simpler. I need this now.

You can get more details on the HIERARCHYID data type here.

Wrapping up

This post is by no means an extensive overview of these data types, but I hope it serves as a brief introduction to what is available in terms of data in the upcoming release of SQL 2008.