Skip to content

Latest commit

 

History

History
114 lines (70 loc) · 8.57 KB

deploying-clr-database-objects.md

File metadata and controls

114 lines (70 loc) · 8.57 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords
Deploying CLR Database Objects
Using Microsoft Visual Studio, you can develop CLR database objects for SQL Server, deploy them to a test server, and distribute them to production servers.
rwestMSFT
randolphwest
12/27/2024
sql
clr
reference
intro-deployment
deployment script [CLR integration]
common language runtime [SQL Server], deploying
deploying assemblies [CLR integration]
deploying [CLR integration]

Deploy CLR database objects

[!INCLUDE SQL Server]

Deployment is the process by which you distribute a finished application or module to be installed and run on another computer. Using 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 .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 [!INCLUDE tsql] statements.

Once the CLR methods are 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 [!INCLUDE ssManStudioFull] (covered later in this article).

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

Deploy 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 isn't required to develop managed database objects for [!INCLUDE ssNoVersion].

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

Note

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

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 is then registered in the [!INCLUDE ssNoVersion] instance and database specified when the [!INCLUDE ssNoVersion] project was first created in Visual Studio.

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 [!INCLUDE c-sharp-md] source files:

    csc /target:library C:\helloworld.cs

  3. For Visual Basic source files:

    vbc /target:library C:\helloworld.vb

These commands launch the [!INCLUDE c-sharp-md] 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 [!INCLUDE ssManStudioFull] on the test server. Create a new query, connected to a suitable test database (such as [!INCLUDE sssampledbobject-md]).

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

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

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

    CREATE PROCEDURE hello AS
EXTERNAL NAME HelloWorld.Procedures.HelloWorld;

For more information about creating the different types of managed database objects in [!INCLUDE ssNoVersion], see:

Deploy the assembly to production servers

Once the CLR database objects are tested and verified on the test server, they can be distributed to production servers. For more information about debugging managed database objects, see How to debug CLR database objects.

The deployment of managed database objects is similar to that of regular database objects (tables, [!INCLUDE tsql] 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 [!INCLUDE ssManStudio]. The deployment script can also be built manually, or built using Generate Scripts and manually altered. Once the deployment script is built, it can be run on other instances of [!INCLUDE ssNoVersion] to deploy the managed database objects.

Generate a deployment script using generate scripts

  1. Open [!INCLUDE ssManStudio] and connect to the [!INCLUDE ssNoVersion] 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 select Next.

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

  5. In the Choose Object Types pane, choose the type of database object to be deployed. Select 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 select Next.

  7. The Output Options pane comes up when all of the desired database object types are selected. Select Script to file and specify a file path for the script. Select Next. Review your selections and select 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 select deploy, Visual Studio will run this script after the deployment of your project.

Related content