We received various errors when trying to install an assembly of ours that uses CSLA. CSLA uses WPF items rather than System.Windows.Forms. Unfortunately we do not want to make the widespread changes that would need ot occur to CSLA. In the end we actually decided to move anything csla related into another assembly that would not be loaded, however meanwhile I did find a fix which I will describe below.
When adding them we received several errors, one of them being:
"The assembly returned from the host store has a different strong name signature than the corresponding one in GAC"
The following reference paths are in different locations and BOTH refer to each other... so you are stuck with a chicken/egg problem here in running:
CREATE ASSEMBLY [PresentationFramework] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationFramework.dll' WITH permission_set = unsafe
It will then try to find PresentationUI which is in a different folder, specifically C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationUI.dll
If you try to add that assembly you will get an error it cannot find PresentationFramework (since PresentationFramework is in a different folder)
CREATE ASSEMBLY [PresentationUI] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationUI.dll' WITH permission_set = unsafe
What I did however incorrect it may be (although it works) is to temporarily copy the PresentationUI.dll to the "Reference Assemblies" location (IE the same folder PresentationFramework.dll is located in).
Then when you run
CREATE ASSEMBLY [PresentationFramework] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationFramework.dll' WITH permission_set = unsafe
it will look in the current folder for presentationui.dll now in
C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationUI.dll
and add the assembly. Once sql server imports it, you can remove the copied file.
This is not the only dll required in our case, in fact there is another blog entry out there that describes a bunch of the libraries required at:
http://weblogs.asp.net/albertpascual/archive/2009/07/27/sql-2008-clr-triggers-use-a-net-class-library-in-sql-using-wpf.aspx
Note: Once you add an assembly that references another assembly, sql server will automatically add that referenced assembly for you.
We ended up using this list:
CREATE ASSEMBLY [SMDiagnostics] FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.Runtime.Serialization.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Data.Linq] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Data.Linq.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Core] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\system.core.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [WindowsBase] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\windowsbase.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationCFFRasterizer] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationCFFRasterizer.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Drawing] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationCore] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationCore.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationHost] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationHostDLL.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Windows.Forms] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.DirectoryServices] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationUI] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationUI.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationFramework] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationFramework.dll' WITH permission_set = unsafe
--CREATE ASSEMBLY [PresentationUI] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationUI.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.IdentityModel] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.IdentityModel.Selectors] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [Microsoft.Transactions.Bridge] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Web.Extensions] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Web.Extensions.dll' WITH permission_set = unsafe
CREATE ASSEMBLY[System.Web] from'C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll' with permission_set = UNSAFE
CREATE ASSEMBLY [System.Design] AUTHORIZATION dbo FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Design.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [Microsoft.Build.Utilities] AUTHORIZATION dbo FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Microsoft.Build.Utilities.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [Microsoft.Build.Tasks] AUTHORIZATION dbo FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Microsoft.Build.Tasks.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Workflow.Runtime] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.Workflow.Runtime.dll' WITH permission_set = unsafe
This is great - thanks for the hints here.
ReplyDeleteHowever, once I get all these assemblies installed on my SQL Server and I try to execute a CLR Trigger containing WPF printing code, I get the error:
Msg 6522, Level 16, State 1, Procedure PrintLabelOneTrig, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "PrintLabelOneTrig":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at PipePrintLib.PipePrinter.PrintLabelOne()
Do you have any suggestions as to how to proceed here?
This post helped me massively. I finally managed to add the PresentationFramework assembly!
ReplyDeleteI have also managed to deploy my CLR function to the SQL database. However, when I try to call my function, I get the following error:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "FN_RTFConverter":
System.IO.FileLoadException: Could not load file or assembly 'PresentationFramework, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.
System.IO.FileLoadException:
at UserDefinedFunctions.FN_RTFConverter(String RTFText)
.
I have looked at the KB article, but it doesn't really help me.
Any ideas?
Thanks
Dan
This is an old post, I know, but I just fought my way through this today and found a workaround.
DeleteThe best I could do was to copy PresentationUI.dll to C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationFramework.dll prior to creating the assemblies (as per Adam's post).
But then, after all assemblies were created, I altered the assembly with ALTER ASSEMBLY PresentationFramework FROM 'C:\Windows\assembly\GAC_MSIL\PresentationFramework\3.0.0.0__31bf3856ad364e35\PresentationFramework.dll'
Then things worked.
An alternate pattern that may be preferable is to copy the assembly DLL and all dependency DLL's to a folder, and then CREATE ASSEMBLY for the desired assembly using the fully qualified path to the desired assembly in that folder. In this way SQL will create all dependency assemblies from this folder.
Consider creating a batch file to copy all assembly files from their native locations to this folder. In that batch file, consider copying from C:\Windows\assembly\GAC_MSIL\... wherever possible.
I tested this second approach with success as well.
The basic rules seem to be that 1) unless a dependency assembly is already registered in SQL, SQL will look to register that dependency assembly using a DLL file in the same folder as the assembly DLL you are registering; 2) For some assemblies that you register you may encounter the error described above ("...different signature than the assembly in the GAC") if the DLL you registered (or that SQL implicitly registered) has a different signature than the DLL that was registered in the GAC; 3) For this reason, using DLL's copied from the GAC (C:\Windows\assembly\GAC_MSIL\...) seems to be preferable wherever possible; 4) All of this is bending the official guidance as to the kind of assemblies that one should use in SQL CLR
This is on SQL2008R2 running on Windows Server 2012 R2 Standard with current updates as of 12/30/2015.
I get these errors when trying register WINDOWSBASE and PresentationCore
ReplyDeleteMsg 10301, Level 16, State 1, Line 1
Assembly 'WindowsBase' references assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', 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(failed to retrieve text for this error. Reason: 15105)). Please load the referenced assembly into the current database and retry your request.
Msg 10301, Level 16, State 1, Line 2
Assembly 'PresentationCore' references assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', 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(failed to retrieve text for this error. Reason: 15105)). Please load the referenced assembly into the current database and retry your request.
Thanks for a great job. Can't imagine how much effort you put in this blog. Glad for me, I'm not that experienced to be concerned with such problems, only issues I might have is missing dll files:) Wich is extremely easy to solve using google or source such as http://fix4dll.com/d3dx9_38_dll. Maybe some day I will become a master of programming and will be treated like a guru by the people.
ReplyDeleteThis blog is nice and very informative. I like this blog.
ReplyDeleteblog Please keep it up.
Incredibly you describe it Brunette ladies
ReplyDelete