To update a clr dll should I drop the assembly?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SammyBar - 01 Jul 2008 16:35 GMT

Hi all,

I'm updating by hand a dll containing clr code for Sql 2005 stored
procedures. My question is which are the steps needed to update the dll to
the new compilation?
The file is not locked so I can overwrite the old version with the new one,
but it looks like the sql server does not understand the changes 'cause it
caches the old version somewhere.
Supposing the stored procedure interfaces does not change from version to
version, should I drop the assembly and recreate it and the sp declarations?

Thanks in advance
Sammy

Reply to this Message

Fred - 01 Jul 2008 17:22 GMT

Dans : news:%23w$%23ZA52IHA.2524@TK2MSFTNGP04.phx.gbl,
SammyBar écrivait :

> Hi all,

Hello,

> I'm updating by hand a dll containing clr code for Sql 2005 stored
> procedures. My question is which are the steps needed to update the
[quoted text clipped - 5 lines]
> version to version, should I drop the assembly and recreate it and
> the sp declarations?

That's what I do.
SQL Server loads the assembly. It doesn't keep a link to the dll file.
I have a script that drops all the function and procs and the assembly,
loads the new version and recreate all functions and procs.
Something like this :

DECLARE @Path nvarchar(1000)
SET @Path = 'C:\Assemblies\'
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'MyFunction' AND
type=N'FS')
DROP FUNCTION dbo.MyFunction

...

IF EXISTS(SELECT * FROM sys.assemblies WHERE name='MyAssembly')
DROP ASSEMBLY [MyAssembly]
CREATE ASSEMBLY [MyAssembly]
FROM @Path + 'MyAssembly.dll'
WITH PERMISSION_SET = SAFE ;

...

CREATE FUNCTION MyFunction(@myParam1 nvarchar(MAX), @myParam2 xml)
RETURNS xml
EXTERNAL NAME [MyAssembly].[MyClassName].[MyFunction]

The problem (that I don't have) is when you can't drop some objects
(used in constraints for example).

Perhaps is there another way to do but this works fine in my case.

Signature

Fred
foleide@free.fr

Reply to this Message

Fred - 02 Jul 2008 06:22 GMT

Dans : news:%23tW1Za52IHA.4488@TK2MSFTNGP02.phx.gbl,
Fred écrivait :

> Dans : news:%23w$%23ZA52IHA.2524@TK2MSFTNGP04.phx.gbl,
> SammyBar écrivait :
[quoted text clipped - 13 lines]
>
> That's what I do.

But it exists an ALTER ASSEMBLY instruction that can meet your
requirements. I didn't try it.

Signature

Fred
foleide@free.fr

Reply to this Message