Skip to content

deploying-clr-database-objects.md

Latest commit

 

History

History
105 lines (64 loc) · 9.4 KB

deploying-clr-database-objects.md

File metadata and controls

105 lines (64 loc) · 9.4 KB
title Deploying CLR Database Objects | Microsoft Docs
ms.custom
ms.date 03/16/2017
ms.prod sql
ms.reviewer
ms.technology clr
ms.topic reference
helpviewer_keywords
deployment script [CLR integration]
common language runtime [SQL Server], deploying
deploying assemblies [CLR integration]
deploying [CLR integration]
ms.assetid 00752573-3367-41a7-af98-7b7a29e8e2f2
author rothja
ms.author jroth

Deploying CLR Database Objects

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md] Deployment is the process by which you distribute a finished application or module to be installed and run on another computer. Using [!INCLUDEmsCoName] Visual Studio, you can develop common language runtime (CLR) database objects and deploy them to a test server. Alternatively, the managed database objects can also be compiled with the [!INCLUDEmsCoName] .NET Framework redistribution files, instead of Visual Studio. Once compiled, the assemblies containing the CLR database objects can then be deployed to a test server using Visual Studio or [!INCLUDEtsql] statements. Note that Visual Studio .NET 2003 cannot be used for CLR integration programming or deployment. [!INCLUDEssNoVersion] includes the .NET Framework pre-installed, and Visual Studio .NET 2003 cannot use the .NET Framework 2.0 assemblies.

Once the CLR methods have been tested and verified on the test server, they can be distributed to production servers using a deployment script. The deployment script can be generated manually, or by using [!INCLUDEssManStudioFull] (see the procedure later in this topic).

The CLR integration feature is turned off by default in [!INCLUDEssNoVersion] and must be enabled in order to use CLR assemblies. For more information, see Enabling CLR Integration.

Deploying the Assembly to the Test Server

Using Visual Studio, you can develop CLR functions, procedures, triggers, user-defined types (UDTs), or user-defined aggregates (UDAs), and deploy them to a test server. These managed database objects can also be compiled with the command line compilers, such as csc.exe and vbc.exe, included with the .NET Framework redistribution files. The Visual Studio Integrated Development Environment is not required to develop managed database objects for [!INCLUDEssNoVersion].

Make sure that all compiler errors and warnings are resolved. The assemblies containing the CLR routines can then be registered in a [!INCLUDEssNoVersion] database using Visual Studio or [!INCLUDEtsql] statements.

Note

The TCP/IP network protocol must be enabled on the [!INCLUDEssNoVersion] instance in order to use [!INCLUDEmsCoName] Visual Studio for remote development, debugging, and development. For more information about enabling TCP/IP protocol on the server, see Configure Client Protocols.

To deploy the assembly using Visual Studio

  1. Build the project by selecting Build <project name> from the Build menu.

  2. Resolve all build errors and warnings before deploying the assembly to the test server.

  3. Select Deploy from the Build menu. The assembly will then be registered in the [!INCLUDEssNoVersion] instance and database specified when the [!INCLUDEssNoVersion] project was first created in Visual Studio.

To deploy the assembly using Transact-SQL

  1. Compile the assembly from the source file using the command line compilers included with the .NET Framework.

  2. For [!INCLUDEmsCoName] Visual C# source files:

  3. csc /target:library C:\helloworld.cs

  4. For [!INCLUDEmsCoName] Visual Basic source files:

vbc /target:library C:\helloworld.vb

These commands launch the Visual C# or Visual Basic compiler using the /target option to specify building a library DLL.

  1. Resolve all build errors and warnings before deploying the assembly to the test server.

  2. Open [!INCLUDEssManStudioFull] on the test server. Create a new query, connected to a suitable test database (such as AdventureWorks).

  3. Create the assembly in the server by adding the following [!INCLUDEtsql] to the query.

CREATE ASSEMBLY HelloWorld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE;

  1. The procedure, function, aggregate, user-defined type, or trigger must then be created in the instance of [!INCLUDEssNoVersion]. If the HelloWorld assembly contains a method named HelloWorld in the Procedures class, the following [!INCLUDEtsql] can be added to the query to create a procedure called hello in [!INCLUDEssNoVersion].

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorld.Procedures.HelloWorld

For more information about creating the different types of managed database objects in [!INCLUDEssNoVersion], see CLR User-Defined Functions, CLR User-Defined Aggregates, CLR User-Defined Types, CLR Stored Procedures, and CLR Triggers.

Deploying the Assembly to Production Servers

Once the CLR database objects have been tested and verified on the test server, they can be distributed to production servers. For more information about debugging managed database objects, see Debugging CLR Database Objects.

The deployment of managed database objects is similar to that of regular database objects (tables, [!INCLUDEtsql] routines, and so on). The assemblies containing the CLR database objects can be deployed to other servers using a deployment script. The deployment script can be built by using the "Generate Scripts" functionality of [!INCLUDEssManStudio]. The deployment script can also be built manually, or built using "Generate Scripts" and manually altered. Once the deployment script has been built, it can be run on other instances of [!INCLUDEssNoVersion] to deploy the managed database objects.

To generate a deployment script using generate scripts

  1. Open [!INCLUDEssManStudio] and connect to the [!INCLUDEssNoVersion] instance where the managed assembly or database object to be deployed is registered.

  2. In the Object Explorer, expand the <server name> and Databases trees. Right-click the database where the managed database object is registered, select Tasks, and then select Generate Scripts. The Script Wizard opens.

  3. Select the database from the list box and click Next.

  4. In the Choose Script Options pane, click Next, or change the options and then click Next.

  5. In the Choose Object Types pane, choose the type of database object to be deployed. Click Next.

  6. For every object type selected in the Choose Object Types pane, a Choose <type> pane is presented. In this pane, you can choose from all the instances of that database object type registered in the specified database. Select one or more objects and click Next.

  7. The Output Options pane comes up when all of the desired database object types have been selected. Select Script to file and specify a file path for the script. Select Next. Review your selections and click Finish. The deployment script is saved to the specified file path.

Post Deployment Scripts

You can run a post deployment script.

To add a post deployment script, add a file called postdeployscript.sql in your Visual Studio project directory. For example, right click your project in Solution Explorer and select Add Existing Item. Add the file in the root of the project, rather than in the Test Scripts folder.

When you click deploy, Visual Studio will run this script after the deployment of your project.

See Also

Common Language Runtime (CLR) Integration Programming Concepts