Build SQL CLR Objects With .NET (Continued)

Once you catalog an assembly, SQL Server 2005 stores information about CLR assemblies in these tables (see Table 2). Perform this query in SqlQuery if you want to see your assembly:

SELECT Content FROM sys.assembly_files
WHERE name="<Path & Filename of Assembly 
Cataloged>"

This gives you your assembly's binary CLR code cataloged in SQL Server. You can also deploy your assembly with the VS.NET 2005 IDE. When you're deploying through the VS.NET 2005 IDE, the assembly, debug symbols (if DEBUG is ON), the actual source code, and the project file are imported. You can change the PERMISSION_SET value inside VS.NET 2005 by editing the Properties page of your SQL Server project. VS.NET 2005 will use this setting when it's deploying from within the IDE. For this article, you'll catalog and create your CLR objects manually to become more familiar with the inner workings of CLR objects within SQL Server 2005.

You can add your debug information manually after cataloging the assembly by using the ALTER ASSEMBLY statement:

ALTER ASSEMBLY MySqlSampleProcedures
ADD FILE FROM
'C:\Development 
Area\VSM122004\SampleProcedures\bin\SampleProcedures.pdb'

This adds another row in the sys.assembly_files table, which is tied to the same assembly_id.

Use Statements to Change the Assembly
Once you catalog an assembly, its CLR code resides in SQL Server 2005, and the external file you used to create the assembly isn't referenced again. If you want to change the assembly, you can use the ALTER ASSEMBLY statement, or you can use the DROP ASSEMBLY statement and re-create the assembly after the desired changes are compiled. The CreateObjects.sql file in the Deployment project, included in the downloadable VS.NET 2005 solution for this article, contains all the DDL to catalog all the assemblies and CLR objects discussed in this article.

There is one strange bug to be aware of that happens only for VB.NET projects in VS.NET 2005. As you've probably noticed already, VS.NET 2005 now creates a lot of auto-generated code under the MyProjects folder (turn on View All Files to see MyProjects as a folder). If you attempt to rename either the assembly or root namespace under the MyProject properties, you'll get an error message about having a static variable/member when trying to compile and deploy your CLR object. So, for now, you'll probably want to keep the default properties created for you already when you create your SQL Server project inside VS.NET 2005.

In order to catalog an assembly, you must have the necessary permissions within SQL Server 2005. You must be logged into SQL Server with an integrated security account (SQL security accounts can't create assemblies). You catalog an assembly with the security granted to the integrated Windows account that created the assembly, so be careful which user ID you use. In addition, you need to catalog dependent assemblies under the same user ID or role. After you catalog an assembly, the owner can extend access permissions for that assembly to other IDs and roles.

Now that you've cataloged the assembly, you need to create your stored procedure definition and map it to the SimpleStoredProc() method in the assembly. You do this by using the standard CREATE PROCEDURE statement in SQL Server:

CREATE PROCEDURE usp_clr_GetSQLVersion
AS EXTERNAL NAME 
MySQLSampleProcedures.[SampleProcedures.StoredProcedures].SimpleStoredProc

This creates a user stored procedure called usp_clr_GetSQLVersion and binds it to the SimpleStoredProc method of the StoredProcedure class within the SampleProcedures namespace of the MySQLSampleProcedures assembly. Notice that the assembly name used is what you cataloged it as in the CREATE ASSEMBLY phase and not the name of the assembly of the CLR code itself. A word of caution, especially when working with VB.NET: Case is significant for EXTERNAL NAME, even though the language might not be case-sensitive (such as VB.NET). The names of the class and method must match the names in the source code exactly.