4533.aspx

Assembly load error in SQL Server 2005 after attaching DB

I recently migrated a Microsoft SQL Server database from one disk drive to another using the sp_detach_db and sp_attach_db stored procedures.

It was on the same machine so I was surprised when I got this half English, half Italian error message:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65733. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:   System.IO.FileLoadException: Impossibile caricare il file o l'assembly '..., Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' o una delle relative dipendenze. Errore relativo alla protezione. (Eccezione da HRESULT: 0x8013150A)  System.IO.FileLoadException:      at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)     at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)     at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)     at System.Reflection.Assembly.Load(String assemblyString)    The statement has been terminated.

Turns out that the DB had a couple of CLR stored procedures and that they refused to work after the move. SQL Server could find the assemblies but it was still unable to load them. The trust configuration was OK as well.

The problem was that the commands were executed from the SQL Server Development Studio using an account connected via windows integrated security. As a result the DB was attached using the current user, not "sa", as the owner of the DB.

Using sp_changedbowner to set sa as the owner fixed the problem.

4532.aspx

Microsoft and Oracle goes shopping

Microsoft buys FAST and Oracle buys BEA.

Bigger and bigger, in the end, there can be only one...

4531.aspx

Bill says goodbye with style

What can I say. Bill's goodbye note is better than mine.

4526.aspx

How to kill open connections on SQL Server

The stored procedure below, from Michele, kills all open connections to the DB you specify. It is a great time saver when you have to take a busy db offline for maintenance.

CREATE PROCEDURE usp_DBKillConn
@DBname sysname = NULL
AS
create table #SpidDB
(
   spid  smallint
)
declare @curSpid smallint
declare @cmd varchar(64)
INSERT INTO #SpidDB(spid) SELECT spid FROM master.dbo.sysprocesses WHERE dbid = DB_ID(@DBname)

SELECT @curSpid = min(spid) from #SpidDB
while @curSpid IS NOT NULL
begin
   PRINT @curSpid
   SET @cmd = 'KILL ' + convert(varchar(6),@CurSpid)   /*KILL @curSpid*/
   EXEC sp_sqlexec @cmd
   SELECT @curSpid = min(spid) from #SpidDB where spid > @curSpid
end
Print @DBname
GO

4527.aspx

Emtpy toolbox in Visual Studio 2005 after Windows Update

I lost all the tool box controls in Visual Studio 2005 after the last Windows Update. I was able to get them back by following these steps:

• Go to the Tools menu


• Import and Export Settings


• Choose "Reset all settings"


• Select the setting you want to use

4525.aspx

My sabbatical

My one month sabbatical has gone fast, too fast. I was expecting to spend a lot of time in front of the computer, but I have hardly written a mail. That caught me by surprise. I have a lot of cool hobby projects going and the sabbatical would have been the perfect time to work on them.

Instead I have spent as much time as possible with my family doing the things I usually do not have time for. This week has been especially good for recharging the batteries as the kids are still home from school and it is snowing :-)

I start my new job next week but it will take a week or two before I am back to my normal blogging and development schedule. I guess this blog will be less Microsoft centric but I will continue to develop for the Pocket PC on my spare time.