Registering a User-defined type in SQL Server

This is not new, it’s been around since SQL 2005, but I had never had to do it before.  If you installed the SQL server samples as part of your SQL client installation, you can find a CLR folder with all sorts of goodies, it will usually be installed at C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR.  Go to the UserDefinedDataType folder and open up the solution.  This will allow you to build it.  You might need to generate the strong name key, which you can easily do by opening up your VS2005 command prompt and typing: sn -k "C:\Program Files\Microsoft SQL Server\90\Samples\SampleKey.snk"
Ok, so now that that's our of the way, you need to load the CLR assembly onto SQL server:

FROM ‘C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\UserDefinedDataType\CS\ComplexNumber\bin\debug\ComplexNumber.dll’

That adds an entry on the Assemblies list, you can get a list of all your registered assemblies like this:

SELECT * FROM sys.assemblies

And finally, to register your user-defined type:

CREATE TYPE ComplexNumber EXTERNAL NAME ComplexNumberDll.[Microsoft.Samples.SqlServer.ComplexNumber]

The square brackets are very important!!  [Assembly Name].[Fully-qualified type name]

About esteban

Esteban is the Founder and Chief Technologist at Nebbia Technology, an ALM consulting and Azure-powered technology company. He is a software developer with a passion for ALM, TFS, Azure, and software development best practices. Esteban is a Microsoft Visual Studio ALM MVP and ALM Ranger, Pluralsight author, and the president of ONETUG (Orlando .NET User Group).

One thought on “Error when installing SQL 2008 on Windows Server 2008 R2 (64 bit)

  1. Mark Allen

    Another approach which works well is to have the XML tag reference a custom TFS group. In my case there is a master contributor group shared between projects so rather than list all the contributor users in the template AssignedTo list boxes I created a TFS group called ‘TeamAssignments’ and within security I referenced the team. Then in the templates I added the tag ListItem value=[project]\TeamAssignments. Now when anyone is added to the team they are displayed in the AssignedTo List box. This also means that another team can be created without changing the templates as the changes can be done in security.

    TFS Team Name: Blue (example set up in security)
    TFS Group Name: TeamAssignments
    Members: Team Blue

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you human? *