How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration

SQL Server projects in Visual Studio help create stored procedures, triggers, aggregates, user-defined functions, and user-defined types using .NET Framework languages such as Visual Basic and Visual C#. SQL Server projects require a reference to SQL Server 2005 or later versions of SQL Server. These objects are often referred to as SQL Server Common Language Run-time objects, or SQL CLR objects.

Important

If you want to develop SQL CLR assemblies for SQL Server 2005 or SQL Server 2008, you must have the .NET Framework version 3.5 installed on your development computer. SQL Server 2005 and SQL Server 2008 require that SQL CLR assemblies target version 2.0, 3.0, or 3.5 of the .NET Framework. You can install the Microsoft .NET Framework 3.5 from the Microsoft Web site.

After you create a SQL CLR database project, you create the database objects that you want to deploy to the SQL Server. The following table provides links to Help topics that describe how to create the available database objects:

Database Object	Information
Stored Procedure	How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration
Trigger	How to: Create and Run a SQL Server Trigger by using Common Language Run-time Integration
Aggregate	How to: Create and Run a SQL Server Aggregate by using Common Language Run-time Integration
User-defined Function	How to: Create and Run a SQL Server User-Defined Function by using Common Language Run-time Integration
User-defined Type	How to: Create and Run a SQL Server User-Defined Type by using Common Language Run-time Integration

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Creating a New Project

To create a new SQL Server project

1. On the File menu, point to New and click Project.

2. Under Installed Templates, expand the Database node, and then click SQL Server.

3. In the list of templates, click the icon that corresponds to the type of project that you want to create. For example, to create a SQL CLR project that uses Visual C#, click Visual C# SQL CLR Database Project.

4. In the target framework list, which is located above the list of templates, click the version of the .NET Framework that corresponds to the version of SQL Server to which you plan to deploy the SQL CLR database project.

   Important

   SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the 2.0, 3.0, or 3.5 version of the .NET Framework. If you try to deploy a SQL Server project to SQL Server 2005 or SQL Server 2008, an error appears: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (where AssemblyName is the name of the assembly that you are deploying).

5. In Name, specify the name that you want to use for the project.

6. Click OK.

Configuring the SQL Server Project

After you create your SQL Server project, you might want to modify several project properties before you can successfully deploy your project.

To configure properties for the SQL Server Project

1. In Solution Explorer, select the project for which you want to configure properties.

2. On the Project menu, click ProjectName Properties, where ProjectName is the name that you gave the SQL Server project.

3. On the Application tab, in Assembly name, specify the name that you want to give the assembly that is built from your SQL Server project.

4. If you have already specified a version of the .NET Framework that is supported by the version of SQL Server to which you plan to deploy the SQL CLR database project, you can skip to step 10.

5. If you are using Visual Basic, skip to step 7.

6. On the Application tab, in Target Framework, click the version of the .NET Framework that corresponds to the version of SQL Server to which you plan to deploy the SQL CLR database project.

   Important

   SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the 2.0, 3.0, or 3.5 version of the .NET Framework. If you try to deploy a SQL Server project to SQL Server 2005 or SQL Server 2008, an error appears: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (where AssemblyName is the name of the assembly that you are deploying).

7. Skip to step 10 (Steps 7, 8, and 9 apply only if you are using Visual Basic

8. On the Compile tab, click Advanced Compile Options.

9. In the Advanced Compiler Settings dialog box, in Target framework, click the version of the .NET Framework that corresponds to the version of SQL Server to which you plan to deploy the SQL CLR database project.

   Important

   SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the 2.0, 3.0, or 3.5 version of the .NET Framework. If you try to deploy a SQL Server project to SQL Server 2005 or SQL Server 2008, an error appears: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (where AssemblyName is the name of the assembly that you are deploying).

10. Click OK.

11. On the Database tab, in Assembly Owner, type the name of a user or a role as the owner of the assembly.

    Important

    This value must be either the name of a role of which the current user is a member or the current user must have IMPERSONATE permission. If you do not specify an assembly owner, ownership is given to the current user. This setting corresponds to the AUTHORIZATION owner_name argument for the SQL Server CREATE ASSEMBLY statement. For more information, see CREATE ASSEMBLY (Transact-SQL) on the Microsoft Web site.

12. On the File menu, click Save All.

    If you want to create a SQL CLR object that utilizes XML Serialization, perform the following additional steps that are described in "Creating SQL CLR Objects that Use XML Serialization."

Creating SQL CLR Objects that Use XML Serialization

If you are creating a Web service, you can set the Generate Serialization Assembly option to 'On' in the build properties. However, when you deploy the assembly that contains your SQL CLR objects, the XML Serialization assembly is not created on the target server. You must add a statement to the PostDeployScript.sql script to create that assembly.

Note

If you are creating a Web service, you must set your permission set to External_Access or Unsafe, and the target database must have the Trustworthy setting set to On.

To create the XMLSerializer assembly for a SQL CLR assembly

1. In Solution Explorer, expand the SQL CLR project that you want to deploy, and double-click the PostDeployScript.sql script.

2. In the code editor, add the following Transact-SQL statement to the script:

   CREATE ASSEMBLY SqlClassLibraryXML from 'path\assemblyname.XmlSerializers.dll' 
   

   Where SqlClassLibraryXML is that name that you want to give the assembly, path is the path to the serializer assembly, and assemblyname.XmlSerializers.dll is the name of the serializer assembly.

3. On the File menu, click Save PostDeployScript.dll.

Connecting to a SQL Server Database

SQL CLR database projects require a connection to a database running on SQL Server 2005 or later. If connections to SQL Server 2005 databases are available in Server Explorer, then they will be listed in the Add Database Reference dialog box. If you plan to deploy the assembly that you build from the SQL Server projects by using a database project (.dbproj), then you do not have to specify a database connection and you can click Cancel in the connection dialog box. For more information, see Starting Team Development of Databases that Reference SQLCLR Objects.

To connect to a SQL Server database

• Connect to a SQL Server 2005 or SQL Server 2008 database by completing the Add/Modify Connection Dialog Box. For more information, see Add/Modify Connection (Microsoft SQL Server).

  -or-

  Select an existing database connection in the Add Database Reference Dialog Box to use for the SQL Server project. For more information, see Add Database Reference Dialog Box.

See Also

Tasks

Walkthrough: Creating a Stored Procedure in Managed Code

How to: Debug a SQL Server CLR Integration Stored Procedure

Reference

Attributes for SQL Server CLR Integration Database Projects and Database Objects

Concepts

Introduction to SQL Server CLR Integration (ADO.NET)

Advantages of Using Managed Code to Create Database Objects

Creating SQL Server Objects in Managed Code