SQL Server 2005 - ALTER ASSEMBLY DROP FILE does not drop the file.

udf.learner

unread,

Mar 13, 2009, 5:28:09 PM3/13/09

to

I have an assembly in which I want to replace the code with new code.
The manual states this can be done with
ALTER ASSEMBLY DROP FILE 'somefile.dll' ADD FILE FROM 'somefile.dll'

This did not work.
Nor did
ALTER ASSEMBLY DROP FILE ALL ADD FILE FROM 'somefile.dll'
Nor did just
ALTER ASSEMBLY DROP FILE 'somefile.dll'
or
ALTER ASSEMBLY DROP FILE ALL

The message I get for:

alter assembly someCLR
drop file 'C:\Program Files\hello\MsSqlProvider\MSSQL05\somefile.dll'
is
Assembly "someCLR" does not have an associated file "C:\Program
Files\hello\MsSqlProvider\MSSQL05\somefile.dll".

However, somefile.dll still shows up in

SELECT * FROM sys.assembly_files

I have even pasted the results from this select into the DROP FILE in case
there is a spelling error. Is this a known issue? Are there situations
where the file cannot be dropped because functions refer to implemetations
within the files (but that just the error message is misleading)

udf.learner

unread,

Mar 13, 2009, 5:51:01 PM3/13/09

to

Two clarifications for my own post.

1. I am able to drop other files in the same assembly using the ALTER
ASSEMBLY. The problem is specific to a particular file.

2. In the original post, the syntax (accidentally) omitted the assembly
name. The actual SQL used did not have this or any other syntax error.
i.e. instead of
ALTER ASSEMBLY someCLR DROP FILE 'somefile.dll' ADD FILE FROM 'somefile.dll'
I wrote

ALTER ASSEMBLY DROP FILE 'somefile.dll' ADD FILE FROM 'somefile.dll'

Erland Sommarskog

unread,

Mar 13, 2009, 7:13:52 PM3/13/09

to

udf.learner (udfle...@discussions.microsoft.com) writes:
> I have an assembly in which I want to replace the code with new code.
> The manual states this can be done with
> ALTER ASSEMBLY DROP FILE 'somefile.dll' ADD FILE FROM 'somefile.dll'

I always do it with

ALTER ASSEMBLY FROM '<filepath>'

That's all. I see that Books Online says this is only possible when there
are no associated files with the assembly.

In the same vein ADD/DROP FILE appears to be for associated files only.

Is your assembly a single-file assembly?

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

udf.learner

unread,

Mar 15, 2009, 1:30:01 AM3/15/09

to

Thanks for your post. Yes, it is a single file assembly.
I first tried with DROP and ADD in the same command to replace the code.
This gave the error message about the file not being present, but it also
added the new file anyway (with file id 2) so I have two files with the same
fully qualified name.

Then, when I do a DROP ALL (so that I could follow this with an ADD), it
only dropped the new one I just added (with file id 2), and kept the old one
with the same name (which has a file id of 1).

udf.learner

unread,

Mar 15, 2009, 2:58:01 AM3/15/09

to

When I try with:

ALTER ASSEMBLY FROM '<filepath>'

I get this:

Msg 6285, Level 16, State 1, Line 1
ALTER ASSEMBLY failed because the source assembly is, according to MVID,
identical to an assembly that is already registered under the name "someCLR".

Bob Beauchemin

unread,

Mar 15, 2009, 3:46:04 AM3/15/09

to

Try: ALTER ASSEMBLY someCLR FROM '<filepath>'

Cheers,
Bob Beauchemin

"udf.learner" <udfle...@discussions.microsoft.com> wrote in message
news:1B27F912-236C-4D2A...@microsoft.com...

Erland Sommarskog

unread,

Mar 15, 2009, 6:05:15 AM3/15/09

to

udf.learner (udfle...@discussions.microsoft.com) writes:
> When I try with:
>
> ALTER ASSEMBLY FROM '<filepath>'
>
> I get this:
>
> Msg 6285, Level 16, State 1, Line 1
> ALTER ASSEMBLY failed because the source assembly is, according to MVID,
> identical to an assembly that is already registered under the name
> "someCLR".

You get this error when you try to alter the assembly to load the exact
same compile as you already have in the database. If you recompile the
assembly, you will not get this error, even if you leave the source code
to the assembly unchanged.

As for SQL Server feels compelled to give you an error for this, I don't
know. I might have received some explanation for it in the past, but in
that case I've forgotten it.

udf.learner

unread,

Mar 16, 2009, 3:12:08 PM3/16/09

to

Thanks for your reply. I should have pasted the exact syntax I had used (
it did specify someCLR).