SQLCoffee - Using CLR Integration in SQL Server 2005

[Company Logo Image]

Using CLR Integration in SQL Server 2005

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:

	SAFE: Only code that runs inside the SQL process can be executed.
	EXTERNAL_ACCESS: The managed code needs access to files, networks, environmental variables, and the registry.
	UNSAFE: 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

Using CLR Integration in SQL Server 2005.

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