CLR Assembly redeploy problem.

I have created a C# library containing CLR stored procedures and user-
define functions using Visual Studio 2005, and I have used Visual
Studio to deploy the assembly to a SQL Server 2005 instance
successfully, of course these sps and udfs are used by other T-SQL sps
and udfs. Now the assembly has a new version, and I want to use it to
replace the original one, but when I use Visual Studio 2005 to deploy
the new assembly, the following error occurs:

Error 1 Cannot drop the function 'TranslationStringLike', because it
does not exist or you do not have permission.
Cannot drop the function 'TranslateEngString', because it does not
exist or you do not have permission.
Cannot drop the function 'TranslateEngStringReverse', because it does
not exist or you do not have permission.
DROP ASSEMBLY failed because 'FirmBankCLR' is referenced by object
'TranslationStringLike'. FirmBankCLR

That is the assembly is dependent by other objects, it can't be
dropped until it is not dependent by other objects. How can I resolve
this problem using Visual Studio 2005 or something else?

Re: CLR Assembly redeploy problem.

Amber (guxiaobo1982@gmail.com) writes:
> I have created a C# library containing CLR stored procedures and user-
> define functions using Visual Studio 2005, and I have used Visual
> Studio to deploy the assembly to a SQL Server 2005 instance
> successfully, of course these sps and udfs are used by other T-SQL sps
> and udfs. Now the assembly has a new version, and I want to use it to
> replace the original one, but when I use Visual Studio 2005 to deploy
> the new assembly, the following error occurs:
>
> Error 1 Cannot drop the function 'TranslationStringLike', because
> it does not exist or you do not have permission. Cannot drop the
> function 'TranslateEngString', because it does not exist or you do not
> have permission. Cannot drop the function 'TranslateEngStringReverse',
> because it does not exist or you do not have permission. DROP ASSEMBLY
> failed because 'FirmBankCLR' is referenced by object
> 'TranslationStringLike'. FirmBankCLR
>
> That is the assembly is dependent by other objects, it can't be
> dropped until it is not dependent by other objects. How can I resolve
> this problem using Visual Studio 2005 or something else?

As long as the interface of the assembly does not change, you can just
do ALTER ASSEMBLY. Either specify directly, which is simple if the DLL
is accessible from SQL Server. Or specify the DLL as a hexstring directly
in the ALTER ASSEMBLY statement.

If the interface has changed, you will need to drop all functions
created from it, as well as dependent assemblies.

I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
of Visual Studio. I don't use Visual Studio to work with assemblies
but stick to the command line. And all impression I get is that
using Visual Studio just makes things harder.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: CLR Assembly redeploy problem.

> I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
> of Visual Studio. I don't use Visual Studio to work with assemblies
> but stick to the command line. And all impression I get is that
> using Visual Studio just makes things harder.

It seems Microsoft hasn't done the job well, but if we have a lot of
objects in the assembly, the manual process of dropping and creating
them is horrible.

Re: CLR Assembly redeploy problem.

Amber (guxiaobo1982@gmail.com) writes:
>> I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
>> of Visual Studio. I don't use Visual Studio to work with assemblies
>> but stick to the command line. And all impression I get is that
>> using Visual Studio just makes things harder.
>
> It seems Microsoft hasn't done the job well, but if we have a lot of
> objects in the assembly, the manual process of dropping and creating
> them is horrible.

Yes, it's horrible if you have to do it each time you change the
implementation of some single method, and it's amazing that VS cannot
handle this situation.

If you change the interface, it's still a lot of work of course of dropping
and recreating objects. But it is or more less inevitable.

I don't know what's in your assemblies, but I woudl suggest that if you
have a suite of functions and procedures that are independent of each other,
that it's best to have a separate assembly for each function/procedure. Of
course, if they use a lot of common code, you still need to put that common
code in a single assembly in which case you are back to the same situation.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx