CREATE ASSEMBLY Issue with Visual Studio SQL Server Database Projects

God breathing through me

CREATE ASSEMBLY Issue with Visual Studio SQL Server Database Projects

Published on April 9, 2008

Visual Studio offers a project type for building SQL Server databases out of the box. This type of projects is available both for SQL Server 2000 and 2005 (and an standalone setup for SQL Server 2008 exists). This project type is very handy because lets you write your T-SQL codes and write your database code in Visual Studio IDE and get the help of good debugging features available there.

This also helps you have a consistent solution in one place where all your projects are placed.

Working with this project type needs a little background and experience to be able to create separate files for different objects and be able to debug and use them without getting some syntax errors but it's easy, though.

One of the main parts of SQL Server 2005 (and 2008) is its CLR integration and the ability to write .NET assemblies and deploy them to your databases to enable extra functionality inside database.

Creating an assembly in database is a straightforward task with a simple T-SQL statement and you probably know it but for those who don't know here is a sample of this syntax:

CREATE ASSEMBLY [SampleCLRIntegration]
FROM 'c:\SampleCLRIntegration.dll'
WITH PERMISSION_SET = SAFE;

Here CREATE ASSEMBLY is a statement that gets the name of the assembly that you like to have in database and there is an assembly path that you need to pass to the statement and an optional permission set.

This is very simple. You create an assembly that contains some SQL-CLR integration code and deploy it to the database.

Here is a sample of a user-defined function that I'm going to register on my database.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "SayHello")]
    public static SqlString SayHello()
    {
        return new SqlString("Say hello to your dad!");
    }
};
 

It's very simple, though. But assume that I want to create this assembly by creating a new assembly file in a SQL Server database project. Here I want to use the abovementioned syntax to create my assembly and type its code. As soon as I enter this code, Visual Studio displays an error for me as you see below.

The error specifies that "CREATE ASSEMBLY statement can only have binary elements in its FROM clause." (just to help search engines!). At first glance this is very weird and you may think you've done something wrong. But unfortunately this is a very stupid issue with Visual Studio since version 2005 that is not solved yet. Error shows up only in Visual Studio SQL Server database projects and a KB article demonstrates this problem and its solution.

Actually I never expected such a problem and solution from Microsoft and Visual Studio but it's truly an issue that is even documented and has been there for 3-4 years.

The solution is to find the binary code of your assembly somehow and put it instead of the path of the assembly in CREATE ASSEMBLY statement.

One possible and logical solution is to deploy your SQL-CLR integration project to a temporary database then generate its build scripts which includes the binary code and then copy/paste it into Visual Studio!

For example, I create a TempDataBase database for this purpose and deploy my SQL-CLR project to this database (this is a built-in feature for Visual Studio).

Now I can generate build scripts for my TempDatabase in different ways. One easy way is to open SQL Server Management Studio and right-click on the database then choose Tasks->Generate Scripts and follow the wizard to generate a text version of the build script.

Generate Scripts

Don't forget to check Assemblies, User-defined functions and any other SQL-CLR integration object types that you want to generate. Also you need to choose the name of your assemblies and user-defined functions in the next step. Avoid generating any unnecessary object to have a cleaner output and be able to find your objects easier.

Script Wizard

After generating the script, you can open it and see that the binary versions of your assemblies are generated for you. Here there is a CREATE ASSEMBLY statement as well as multiple ALTER ASSEMBLY statements that add the assembly and its different files. In order to use your assemblies you need to include the required parts and the simple way is to include them all. So I copy/paste this part of the generated code to my SQL Server database project in Visual Studio.

Here is the result that works as expected (I removed long binary code).

CREATE ASSEMBLY [SampleCLRIntegration]
FROM 0x4D5A90000300000004000000FFFF0000B800000
GO
ALTER ASSEMBLY [SampleCLRIntegration]
ADD FILE FROM 0xEFBBBF7573696E672053797374656D
AS N'Properties\AssemblyInfo.cs'
GO
ALTER ASSEMBLY [SampleCLRIntegration]
ADD FILE FROM 0x4D6963726F736F667420432F432B2B
AS N'SampleCLRIntegration.pdb'
GO
ALTER ASSEMBLY [SampleCLRIntegration]
ADD FILE FROM 0xEFBBBF7573696E672053797374656D
AS N'SayHello.cs'

This solves the issue after a manual process but has another drawback: you need to repeat this process again every time that you change your code in the assembly!

I hope that Microsoft applies a solution for this issue in the next versions. Of course, I haven't tested this with SQL Server 2008 projects in Visual Studio so if this is solved, please let me know.

I uploaded the sample project for this post here so you can check the whole code if you need.

3 Comments

Anders
Oct 31, 2008 3:07 PM
#

Thanks!

I have been puzzled by the inability to deploy the assembly into another database.

I'll surely try your solution

//Anders

ishan
Feb 12, 2009 11:04 AM
#

i am still screwed up with this.. can u help me out .. m tryin to build it bt still it is generating errors

cn u tell me what i am missing here

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefined

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString.GetOSVersion

{

return System.Environment.OSVersion.Tostring()

}

cn u plz mail me the solution . my id is getishan@gmail.com

Greg
Mar 04, 2009 9:26 PM
#

In VS2008, you can run

CREATE ASSEMBLY [SampleCLRIntegration]

FROM 'c:\SampleCLRIntegration.dll'

WITH PERMISSION_SET = SAFE;

with no errors.

Greg

Keyvan Nayyeri

Recent Posts

Archive

CREATE ASSEMBLY Issue with Visual Studio SQL Server Database Projects

Leave a Comment