Windows 64bit SQL 2005 Express CLR Install Mystery

rcl58
rcl58 used Ask the Experts™
on
My 32bit .NET application runs SQL 2005 SP3 and uses CLR. The install works fine on XP & Vista. The install fails on XP 64bit & Widows 7 64bit.

The error is in one of my SQL scripts that creates an Assembly:

Error:
“Execution of .NET Framework code is disabled. Set ‘CLR enabled’ configuration option and restart server. (5847)”

Script Code:
CREATE ASSEMBLY MyCode
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\MyCode.dll'
WITH PERMISSION_SET = UNSAFE

HOWEVER when I check the CLR it is enabled. In fact my very first script turns the CLR On and Trustworthy On.

So again, this all works on 32bit machines but not on 64bit.

Suggestions?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
WOW! This is a surprise.

WOW64 servers must be restarted before the changes to this setting will take effect. Restart is not required for other server types.

Link Here...

So now the question is how can I set the CLR On before starting the sever?
I've never tried creating an assembly from an SQL script, so I may not be much of a help, but I do have a few theories that may point you in the right direction.

One of the nice things about CLR applications is you can set them to be CPU architecture independent. Since they are compiled at runtime, if you run it on a 32-bit system, it will run as 32-bit native code once the program has launched. If you run it on a 64-bit system, it will run as 64-bit native code once it has launched. Somewhere there may be a conflict of architectures going on.

Is your code truly 32-bit? In other words, will it still show up as a 32-bit app in Task Manager when you run it on a 64-bit system? You can always check the process in Task Manager if you aren't sure... it will have a *32 next to it if it's running in 32-bit mode.

Also, is your SQL Server 32-bit or 64-bit? You might have to play with the architecture settings a bit to get this to work.
Top Expert 2010

Commented:

The ability to execute common language runtime (CLR) code is set to OFF by default in SQL Server. The CLR code can be enabled by using the sp_configure system stored procedure.
To overcome this, you have to reconfigure the SQL server to enable CLR objects. To do that, you can use the following commands…

exec sp_configure 'clr_enable','1'

RECONFIGURE

Author

Commented:
Thanks senad,

I am doing that but take a look at my second post. WOW64 requires a database restart. It’s now down to figuring out how to get my install (Installsheild) to restart the DB and continue with create assembly scripts.
 
Thanks again.
Commented:
The final answer is an install sequence issue and I am using InstallShield. The full solution is here.

If you found this post helpful support Code{the final solution} group. Click the “Like” button and help spread the word…

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial