Playing With SQL Server CLR Integration – Part IV (Deploying To SQL Server 2005)
With all developed and tested on my laptop using SQL Server 2008, it’s time to deploy to the company’s test machine running SQL Server 2005.
The first thing I ran into when executing:
CREATE ASSEMBLY [MyAssembly] AUTHORIZATION [dbo] FROM '...\MyAssembly.dll' WITH PERMISSION_SET = SAFE GO
was:
Msg 10327, Level 14, State 1, Line 1
Assembly 'MyAssembly' references assembly 'system.xml.linq, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.',
which is not present in the current database.
SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from,
but that operation has failed (reason: 2(The system cannot find the file specified.)).
Please load the referenced assembly into the current database and retry your request.
Looks like SQL Server 2005 doesn’t know about .NET Framework 3.5. I’d just load the assemblies being used: System.Core and System.Linq.Xml:
CREATE ASSEMBLY [System.Core] AUTHORIZATION [dbo] FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll' WITH PERMISSION_SET = SAFE GO
Not so easy:
Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. Msg 6218, Level 16, State 2, Line 1 CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message [ : System.Diagnostics.Eventing.EventProvider::EtwRegister][mdToken=0x600003b][offset 0x0000003D][found Native Int][expected unmanaged pointer] Unexpected type on the stack. [ : System.Diagnostics.Eventing.EventProvider::EncodeObject][mdToken=0x6000046][offset 0x00000000] Unmanaged pointers are not a verifiable type. [ : System.Diagnostics.Eventing.EventProvider::WriteMessageEvent][mdToken=0x6000047][offset 0x0000003C][found ref 'System.String'] Expected numeric type on the stack. [ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x0000012E] Instruction cannot be verified. [ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x00000030] Instruction cannot be verified. [ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004a][offset 0x0000005F][found ref 'System.String'] Expected numeric type on the stack. [ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004b][offset 0x00000010][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack. [ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x0000007D] Instruction cannot be verified. [ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x00000309][found Native Int][expected unmanaged pointer] Unexpected type on the stack. [ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004d][offset 0x0000001B][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack. [ : System.Security.Cryptography.CapiNative::ImportSymmetricKey][mdToken=0x60007c2][offset 0x00000071][found address of Byte] Expected numeric type on the stac...
Ok. I’d just load it with PERMISSION_SET = UNSAFE:
CREATE ASSEMBLY [MyAssembly] AUTHORIZATION [dbo] FROM '...\MyAssembly.dll' WITH PERMISSION_SET = UNSAFE GO
Not yet:
Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.
Solved:
ALTER DATABASE MyDatabase SET TRUSTWORTHY ON GO
Only than I was able to load the .NET 3.5 assemblies:
CREATE ASSEMBLY [System.Core] AUTHORIZATION [dbo] FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll' WITH PERMISSION_SET = UNSAFE GO CREATE ASSEMBLY [System.Xml.Linq] AUTHORIZATION [dbo] FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Xml.Linq.dll' WITH PERMISSION_SET = UNSAFE GO
With all dependencies in place, I tried to load the assembly with PERMISSION_SET = SAFE with no luck:
Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'ShortPropsToXml' on type 'ShortProps' in safe assembly 'Esi.SA.Encyclopedia' is storing to a static field.
Storing to a static field is not allowed in safe assemblies.
It had to be with PERMISSION_SET = UNSAFE.
After successfully loading the assemblies, I was finally able to create the Transact-SQL definitions for the functions (see Part I and Part II).
Now the DBAs won’t definitely let me use this, but it was fun to build it.