[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
Using CLR Integration in SQL Server 2005

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

Using CLR Integration in SQL Server 2005.

 

1.    Write a simple “Hello World” program

 

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

 

public class HelloWorldProc

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void HelloWorld()

    {

        SqlContext.Pipe.Send("Hello world!\n");

    }

}

 

 2.    Compile the "Hello World" Stored Procedure

 

 

  

3.    Open SQL Server Management Studio and create a new query, connecting to the AdventureWorks sample database, then enable CLR Integration.

 

 

Next, let’s create the assembly in SQL Server.

 

 

 

The permissions you can set when are as follows:

 

bulletSAFE: Only code that runs inside the SQL process can be executed.
bulletEXTERNAL_ACCESS: The managed code needs access to files, networks, environmental variables, and the registry.
bulletUNSAFE: The managed code needs access to the Win32 subsystem, or unrestricted access to resources, both within and outside an instance of SQL Server.

 

To verify the existence of the assembly, query the sys.assemblies table.

 

 

 

Now you can access the HelloWorld method by using the create procedure statement.

 

 

Run the procedure like a normal one.

 

 

 

If you would like to remove the procedure and the assembly do the following:

 

 

  

References

Getting Started with CLR Integration

http://msdn2.microsoft.com/en-us/library/ms131052.aspx

 

 

 

 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.