March 19, 2010 at 11:05 am
Hi guys
A new email CLR dll, which is tested and fine on a similar system causes a clients SQL server query window to hang when trying to register it.
They are using 2005 32 bit, operating system is 2003 server 64 bit.
If you attempt to stop and start the service, it says it has stopped but will not restart but is infact still running in the background.
The only way we have found to fix this is to restart the server.
SPID at the point of hanging is saying unknown token.
Any help greatly appreciated.
March 19, 2010 at 12:58 pm
rich-521822 (3/19/2010)
Hi guysA new email CLR dll, which is tested and fine on a similar system causes a clients SQL server query window to hang when trying to register it.
They are using 2005 32 bit, operating system is 2003 server 64 bit.
If you attempt to stop and start the service, it says it has stopped but will not restart but is infact still running in the background.
The only way we have found to fix this is to restart the server.
SPID at the point of hanging is saying unknown token.
Any help greatly appreciated.
The actual question is why are you running System.NET SMTP code as CLR procedure and if you are running CDO code then that is the problem x64 cannot use CDO a COM dll without conversion. So use your System.NET code in your application. In .NET I can help with implementation in both Winform and Asp.net. The new version of .NET 4.0 comes with improvements.
Kind regards,
Gift Peddie
March 25, 2010 at 11:28 am
Gift Peddie (3/19/2010)
rich-521822 (3/19/2010)
Hi guysA new email CLR dll, which is tested and fine on a similar system causes a clients SQL server query window to hang when trying to register it.
They are using 2005 32 bit, operating system is 2003 server 64 bit.
If you attempt to stop and start the service, it says it has stopped but will not restart but is infact still running in the background.
The only way we have found to fix this is to restart the server.
SPID at the point of hanging is saying unknown token.
Any help greatly appreciated.
The actual question is why are you running System.NET SMTP code as CLR procedure and if you are running CDO code then that is the problem x64 cannot use CDO a COM dll without conversion. So use your System.NET code in your application. In .NET I can help with implementation in both Winform and Asp.net. The new version of .NET 4.0 comes with improvements.
We have written a basic CLR DLL using System.NET.Mail to send an email message to an SMTP server. We have had a nasty experience with CDO, hence why we have written this DLL.
The problem is not the DLL as it installs on all of our servers in the office and on our client sites, x64 or x86. The issue is this particular server is not allowing CLR to be enabled / initialised. We have eneabled CLR, set the database as trustworthy and even checked surface area config, but still no dice.
The sympton is when we run the CREATE ASSEMBLY command, the query window gets stuck in a loop, you cant quit the window, it just leaves the SPID with a status of "UNKNOWN TOKEN". When the server goes into this state, even stop / starting the server service fails. It says the service is not running, yet the client can still access the database.
I think our main issue is the installation of the CLR or at least the CLR with SQL Server. Believe there maybe an issue with the mscoree DLL??
Any help would be great.
March 25, 2010 at 11:47 am
I think our main issue is the installation of the CLR or at least the CLR with SQL Server. Believe there maybe an issue with the mscoree DLL??
Any help would be great.
If that is your problem then it is serious because it requires the .NET Framework and relevant service pack reinstall. So start with the reinstall and see if you can add a config file with the SMTP server information.
Kind regards,
Gift Peddie
March 26, 2010 at 2:52 am
First things first. Run the following to see the status of the CLR hosting:
SELECT *
FROM sys.dm_clr_properties;
You will see something like the following if all is well:
name value
directory c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727version v2.0.50727
state CLR is initialized
Was the DLL written with the SQLCLR hosted environment in mind? Or is it a general .NET assembly?
You should check the documentation to make sure it uses only libraries documented as safe to use in SQLCLR.
March 26, 2010 at 7:01 am
Paul White NZ (3/26/2010)
First things first. Run the following to see the status of the CLR hosting:
SELECT *
FROM sys.dm_clr_properties;
You will see something like the following if all is well:
name value
directory c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727version v2.0.50727
state CLR is initialized
Was the DLL written with the SQLCLR hosted environment in mind? Or is it a general .NET assembly?
You should check the documentation to make sure it uses only libraries documented as safe to use in SQLCLR.
Thanks Paul,
Yes it was written in Visual Studio for SQL server. I have run the sys.dm_clr_properties, enabled clr in sp_configure, set the database(s) to trustworthy, stopped and started the service etc etc. No luck. I think there is an issue with the .NET mscoree.dll
I'm going for an install of .NET 3.5 SP1 before opening my box of axes and attempting a fix by physical threat.
March 26, 2010 at 7:10 am
rich-521822 (3/26/2010)
set the database(s) to trustworthy
This bit concerns me slightly. Setting TRUSTWORTHY on is not the recommended practice - you should sign the assembly, generate an asymmetric key for it, create a login from that, and grant that login the EXTERNAL_ACCESS or UNSAFE permission sets as required.
Seeing as SQL Server seems to be getting stuck at the stage where it verifies the assembly for safety, I would be interested to see what references there are (namespaces, external DLLs, and so on), what permission set is required...all the stuff that SQL Server is checking. Type safety, CAS, HPA...
See CLR Hosted Environment and links from that page.
March 26, 2010 at 3:06 pm
Paul White NZ (3/26/2010)
rich-521822 (3/26/2010)
set the database(s) to trustworthyThis bit concerns me slightly. Setting TRUSTWORTHY on is not the recommended practice - you should sign the assembly, generate an asymmetric key for it, create a login from that, and grant that login the EXTERNAL_ACCESS or UNSAFE permission sets as required.
Seeing as SQL Server seems to be getting stuck at the stage where it verifies the assembly for safety, I would be interested to see what references there are (namespaces, external DLLs, and so on), what permission set is required...all the stuff that SQL Server is checking. Type safety, CAS, HPA...
See CLR Hosted Environment and links from that page.
The is correct and I might add that I am not aware of SMTP code running without config file, the framework uses that to resolve where to send the mail. SQL Server comes with many mail implementations so I would say either move it to the application or use what is in the system. I understand that could be a problem with large numbers sent from your system.
Kind regards,
Gift Peddie
March 29, 2010 at 3:38 am
This is a server issue. The assembly has installed correctly and worked on many other servers, both 32bit and 64bit.
Its just this particular server. What I am after is why CLR is not initialising on the server itself.
March 29, 2010 at 6:55 am
I think our main issue is the installation of the CLR or at least the CLR with SQL Server. Believe there maybe an issue with the mscoree DLL??
That could be related to the above and I know from the .NET Framework people it requires the .NET framework reinstall. And you know the x64 also installs the x86.
Kind regards,
Gift Peddie
March 29, 2010 at 7:33 am
rich-521822 (3/29/2010)
This is a server issue. The assembly has installed correctly and worked on many other servers, both 32bit and 64bit. Its just this particular server. What I am after is why CLR is not initialising on the server itself.
I thought you said CLR did initialise? Checking back, you never did post the output from sys.dm_clr_properties. Not psychic, y'know!
This has been going on for a while now. Would it not be easier to rebuild the server? 😉
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply