Prev: OpenQuery problem
Next: Proxy Credentials Frustration
From: SammyBar on 1 Jul 2008 11:35
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


From: Fred on 1 Jul 2008 12:22
Dans : news:%23w$%23ZA52IHA.2524(a)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
> 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?

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.

--
Fred
foleide(a)free.fr

From: Fred on 2 Jul 2008 01:22
Dans : news:%23tW1Za52IHA.4488(a)TK2MSFTNGP02.phx.gbl,
Fred �crivait :
> Dans : news:%23w$%23ZA52IHA.2524(a)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
>> 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?
>
> That's what I do.

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

--
Fred
foleide(a)free.fr

 | 
Pages: 1
Prev: OpenQuery problem
Next: Proxy Credentials Frustration