sql-serversql-server-2008sqlclrclrstoredprocedure

How refresh to refresh C# CLR code (assembly) in SQL Server?

I implemented the "HelloWorld" sample of a CLR stored procedure, and it worked.

I then changed the code, recompiled, and re-ran it, and got the old results, not the new results.

Example Code Change (all other code is in above link):

Before:

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

After: 

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

Is there anything short of recycling SQL to get the new assembly loaded?

Solution

  • Say you have created a CLR Function HelloSQLCLR() in C#
    You will need the following steps before you can call it in Sql Server
    Enable SQLCLR

    sp_configure 'clr_enabled', 1
GO
RECONFIGURE
GO

    Install and register HelloSQLCLR

    Install (change the directory to the actual location)

    CREATE ASSEMBLY MyUDFsLib
    FROM 'C:\Path_To_Your_DLL\HelloSQLCLR.dll'
GO

    Register

    CREATE FUNCTION dbo.HelloSQLCLR()
    RETURNS NVARCHAR(50)
    EXTERNAL NAME MyUDFsLib.MyUDFs.HelloSQLCLR;

    Test it

    SELECT dbo.HelloSQLCLR()

    Edit
    If you have changed the code try something like this 

    ALTER ASSEMBLY MyUDFsLib 
FROM 'C:\Path_your_changed_Recomplied_DLL\.dll'