The CLR in SQL Server 2005

  • Apologies if this is not the right forum for code samples, but here is a quick example of how to set up a sproc called CLR_usp_CopyFile to copy files. For the sake of brevity I have omitted any code not needed for this illustration:

    .NET code:


    using System;

    using System.Collections;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using System.IO;

    using Microsoft.SqlServer.Server;

    namespace fred


    public sealed partial class FileIO


    public static void CopyFile( string sourceFileName, string destFileName )


    using (SqlConnection conn = new SqlConnection("context connection = true"))


    File.Copy( sourceFileName, destFileName );





    //end of code

    Compile into an assembly, copy dll to location accessible to SQL Server instance.

    SQL Script to deploy assembly and build SQLCLR sproc:


    USE [master]


    -- Enter correct path of dll:





    -- Create the assembly


    FROM 'C:\SQLCLR\FileIO.dll' -- Enter correct path of dll

    WITH permission_set = EXTERNAL_ACCESS;



    CREATE PROCEDURE dbo.CLR_usp_CopyFile


    @SourceFileName NVARCHAR(255)

    ,@DestFileName NVARCHAR(255)


    AS EXTERNAL NAME FileIO.[fred.FileIO].CopyFile;


    --end of code



    EXEC master.dbo.CLR_usp_CopyFile @SourceFileName = 'c:\fred', @DestFileName = 'd:\fred'

    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • I totally agree with Alan Robbins. .Net framework is powerful, solid and beautiful. Hosting CLR in SQL Server brings the core of .Net to us SQL developers and administrators. It is up to us to learn to harness that power to our advantage.

    In our experience with the integration, in addition to the trivial CLR string manipulation (mainly Regex) functions and UDTs, we have successfully brought thirty-party "sealed" objects (either in Win32 DLL, COM, or .Net assemblies) into our SQL applications. For example, we can do massive VIN decoding, address parsing and geo-decoding, yellow/white paging, SSN decoding, and IBM MQ messaging, etc, within our databases natively. That has greatly enhanced programming productivity and processs integrity. Of course, we have had to carefully architect the CLR integration so that all those heavy duty thirty-party objects are hosted on a separate .Net application server with the SQL server being only the .Net remoting client.

    System.Runtime.Remoting is not fully supported in SQL 2005. We are looking forward to SQL 2008 to have a more complete CLR support.

  • On the backend business logic front, ive been steadily increasing how much I put directly in the db rather than in the front end for some time now.

    My main db here has several front ends crossing seperate technologies. being able to write a single shared set of instructions on the server once, rather than having to re-write several versions in each front end, I find is a real saver.

    CLR wise, most of my interest has been (as others seem to like too) in mroe `support` functions - data validation, string manipulation, that kind of thing. And to that end, I have a dream!

    Part of the sql server environment in management studio, an extra `CLR` branch under your database somewhere around Programmability - where adding/editing/deleting/etc. simple (e.g. .net) functions is as easy as doing the same with stored procedures 🙂 No messing around!

    martin 🙂


  • rick (1/25/2008)

    Grasshopper: I have to take issue with your statement that TSQL is a horrible language. From my 38 years first as a developer and then a DBA, I can vouch for the fact that for the languages I have used over the years, Assembler, Autocoder, Cobol, Basic, RPG, Fortran, and for the past 15 yeas SQL, including TSQL, there are many more bad developers than bad languages. SQL, IF you know how to use it, is an extremely powerful and fast tool. But it's just like woodworking. Having the best tools doesn't buy you a thing if you don't know how to use them. ...

    I have to completely agree with Rick. SQL is a beautiful language for what it is intended. There are some things for which it is simply not meant that you can shoehorn it into, but within its domain it is unsurpassed and graceful.

    Timothy A Wiseman
    SQL Blog:

  • I agree with what Alan and Steve have said. It's great tool that has its uses, but shouldn't be used whenever possible simply because it can be. So far I've only had one case where I found it necessary to create a .NET (CLR) stored procedure, but it was an invaluable tool when I needed it. The procedure involved some pretty advanced string manipulation, with an eventual query string of over 8k, pulling data down from an Oracle db to our SQL Svr instance. For all my other report and ETL processes, I've never actually needed it, even though it'd be cool to use, so didn't use it. But that one time it was a lifesaver, and I wouldn't hesitate to do it again if the situation presented itself.

Viewing 5 posts - 61 through 64 (of 64 total)

You must be logged in to reply to this topic. Login to reply