File IO Exception thrown when trying to access a CLR Assembly in SQL Server

I have been encountering a problem using a CLR Assembly in SQL server. The Assembly is one provided by Microsoft for an example on using Exchange web services with SQL server.

http://www.microsoft.com/downloads/details.aspx?FamilyId=D6924897-7B62-46FD-874E-24FB0FBA2159&displaylang=en#Requirements

Essentially what this package is, is a set of c# classes that access the Exchange 2007 Web Services via a set of user defined functions and views in MS SQL Server 2005.

We have not modified the code except to configure for our host environment.

We are able to register the assembly using the setup.sql file included. But when we try to access any of the views or use the functions we get the following error:

Msg 10314, Level 16, State 11, Line 10

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65551. 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: Could not load file or assembly 'exchangeudfs, Version=0.0.0.0, Culture=neutral, PublicKeyToken=777b97dde00f3dbe' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

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)

We have 3 testing environments. 2 Windows 2003 servers called test and test2. Also a single Windows 2000 server called test3. Test and test2 have a full suite installed on them, web server, exchange 2007, sql server 2005, they are also domain controllers for their own domains.

We get the above error on test and test2 but it runs fine on test3. Test and test2 represent what our production environment is like. Test3 was just part of our troubleshooting process.

We have tried a lot to make this work. Here are some details on the things we have tried.

The database is set up to allow CLR Assemblies. This is part of the setup.sql.

The assembly's permission is set to external_access.

We have gone all the way to setting the file permissions on the dll to full control to the Everyone group.

We have tried different accounts to run the SQL Service. We've tried the system account, the local admin account and a seperate user account.

The database we are using is a fresh brand new database. Therefore it does not fit into the bug reported in this article:

http://support.microsoft.com/kb/918040

I am really at a loss to where to go from here. Any ideas on why this 'out of the box' solution is causing us so many headaches would be appriciated.

Thanks,

Tim

Which box did you compile your changes on? Do the full assembly identities match for the exchangeudfs assembly and all the dependents?

|||

I compiled the assembly on another seperate development machine. I'm not sure I know how to answer your second question.

|||

Fro whatever reason I recompiled it and it magically started working. Not sure why.