Home / server / Questions

Questions[sqlclr](server)

Martin Hope
Roger Guess
Asked: 2012-01-27 09:00:35 +0800 CST
  • 2

We inherited a legacy SQL 2005 database. Two assemblies were setup to be used by triggers in one of the databases (ouch). The folder/files were delted by mistake. They were installed by a previous employee from his desktop folder, and did not get checked into source control (double ouch). Now that the dll's are gone, we are concerned about a reboot. Does SQL 2005 copy/rename/store those dll's in such a way that would mean a reboot of the server would not create a problem?

sql-server-2005 sqlclr
Martin Hope
Ian Boyd
Asked: 2011-10-20 10:50:19 +0800 CST
  • 24

How can i update a CLR function (or procedure) assembly dll without having to drop and re-create the assembly in SQL Server (2008 R2)?

As it stands now if i update an assembly (e.g. to add a new function), SQL Server will not honor the updated dll until i drop the assembly:

DROP ASSEMBLY CLRFunctions

Msg 6590, Level 16, State 1, Line 1
DROP ASSEMBLY failed because 'CLRFunctions' is referenced by object 'NormalizeString'.

But before i can drop the assembly, i must first drop all functions that reference it:

DROP FUNCTION NormalizeString
DROP FUNCTION RemoveDiacritics
DROP FUNCTION RemoveCombiningDiacritics
DROP FUNCTION CombineLigatures
....
DROP FUNCTION PseudolocalizeArabic

And then i can drop the assembly:

DROP ASSEMBLY CLRFunctions

Now i have to "create" the assembly:

CREATE ASSEMBLY CLRFunctions FROM 'c:\foos\CLRFunctions.dll';

And now i have to hunt the declaration of all the UDF's that were registered before i deleted them.

i would rather update an assembly, and have SQL Server begin using it.

Update: i randomly tried DBCC FREEPROCCACHE to force a "recompile", but SQL Server still uses the old code.

Update: i deleted the assembly dll CLRFunctions.dll, and SQL Server is still able to run the code (without code that should be impossible).

sql-server sql-server-2008-r2 sqlclr
Martin Hope
Ed Leighton-Dick
Asked: 2010-05-08 11:40:26 +0800 CST
  • 4

One of our in-house applications has a SQLCLR assembly which is currently experiencing problems. The developer is having problems recreating the issue using the version that is stored in our source control system, so he suspects that some code may have been released that was not uploaded to source control. Is there a way to extract a SQLCLR assembly into a .dll file so that he can reverse engineer it for analysis?

sql-server-2005 sqlclr
Martin Hope
dnolan
Asked: 2010-03-27 01:00:18 +0800 CST
  • 2

We have a SQL server running SQL 2005 Workgroup 64 bit (9.0.4273), on Windows 2003 server 64 bit.

We have run sp_configure and reconfigured the server which indicates that the clr is now enabled.

exec sp_configure 'clr enabled', '1'
go
reconfigure
go

However, when trying to call CREATE ASSEMBLY the server completely dies on us and we have to do a full reboot of the machine.

A little more diagnostic information, even though clr enabled is set to 1 and we have rebooted the full server, running the following statement

select * from sys.dm_clr_properties

returns 

directory   
version 
state   locked CLR version with mscoree

which is what it says when the CLR is not enabled on another machine.

On a correctly enabled machine (after reboot) this function reads

directory   C:\Windows\Microsoft.NET\Framework64\v2.0.50727\
version v2.0.50727
state   CLR is initialized
sql-server sql-server-2005 64-bit sqlclr
Martin Hope
dance2die
Asked: 2010-01-22 07:56:15 +0800 CST
  • 4

I am trying to deploy a CLR function (which computes a hash of a text or a file, not available from SQL Server) for the first time on a production server.

I was able to test the function on a development machine but I am still wondering,

Are there any list of gotchas to consider before turning on the CLR option?

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
sql-server sql-server-2005 sqlclr