How to: Deploy SQL Server CLR Integration Database Project Items to a SQL Server
After you build a SQL Server Common Language Run-time (SQL CLR) database project, it is compiled into an assembly that can then be deployed to the database. If you expect to debug the SQL CLR objects, you should also deploy the associated symbol file (.pdb).
Note
As an alternative to building, deploying, and debugging in three separate steps, you can use the Start command (F5) to build the assembly, deploy it to the database, and debug the database object. Select the Start Without Debugging command (CTRL+F5) to build the assembly and deploy it to the database.
If you want to deploy a SQL CLR database project that requires an XML Serializer assembly, you must make sure that your project is configured to create that assembly when you deploy the SQL CLR database project. For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.
Important
You must have appropriate permissions to deploy a new or an updated SQL CLR assembly to a target database. For more information, see Required Permissions for Database Features of Visual Studio.
To configure your project to deploy symbols to enable debugging
In Solution Explorer, right-click the SQL CLR database project that you want to configure and then click Properties.
Click the Deploy tab.
Select the Deploy code check box.
Note
By default, Deploy code is enabled, and symbols are deployed when you deploy your assembly.
On the File menu, click Save Selected Items.
To deploy items in a SQL Server project to a SQL Server
Build the project by selecting Build <project name> from the Build menu.
Note
Resolve all build errors before you deploy.
Select Deploy <project name> from the Build menu.
If you receive a message informing you that the project cannot be deployed, then you might have to manually create the assembly on the server. This error is typically the result of referenced assemblies in your project. For more information about how to manually deploy your assembly, see Create Assembly (Transact-SQL) on the Microsoft Web site.
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). For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.
Next Steps
After deploying SQL CLR objects to the database, you will likely want to exercise the item to test it.
To test a deployed database item
Choose one of the following methods to debug a SQL CLR object after you have deployed it to a target database:
Debug SQL CLR objects by using the Test.sql script in your project. For more information, see How to: Edit the Test.sql Script to Run Objects that use SQL Server Common Language Run-time Integration.
Execute the item by calling it from a query on the SQL Server. For example, if you deployed a stored procedure, run it by calling EXEC <StoredProcedureName> and passing in any expected parameters. For more information, see How to: Run Stored Procedures and User-Defined Functions.
See Also
Tasks
How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration
How to: Create and Run a SQL Server Trigger by using Common Language Run-time Integration
How to: Create and Run a SQL Server Aggregate by using Common Language Run-time Integration
How to: Create and Run a SQL Server User-Defined Type by using Common Language Run-time Integration
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