February 12, 2010 at 5:40 am
I am having problem creating an assembly in SQL SERVER 2005.
The command I inserted is the following:
create assembly StampaEtichetteLib from 'C:\AlphaDec\Bin\StampaEtichetteLib.dll' with permission_set = safe
and the error that I received is
Msg 10301, Level 16, State 1, Line 8.
Assembly 'StampaEtichetteLib' references assembly 'system.drawing, 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(error not found)). Please load the referenced assembly into the current database and retry your request.
The dll print a label on a printer and uses some dll from framework 2.0 as System.Drawing, System.xml and others.
Can someone help me.
Thanks
Maurizio
February 14, 2010 at 5:48 am
I had a similar problem because I was using a RichTextBox within my code. In order to resolve my problem I did the following;
USE [DBNAME]
GO
--set the database trustworthy
ALTER DATABASE DBNAME SET TRUSTWORTHY ON
GO
--if the clr stored procedure exists drop it
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Templates.RTFToTEXT') AND type in (N'FS'))
DROP FUNCTION Templates.RTFToTEXT
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Templates.GetFreeText') AND type in (N'FS'))
DROP FUNCTION Templates.GetFreeText
GO
--if the RTFConvertor assembly exists then drop it
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'RTFConvertor')
DROP ASSEMBLY RTFConvertor
GO
--if the System.Windows.Forms assembly exists then drop it
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.Windows.Forms')
DROP ASSEMBLY [System.Windows.Forms]
GO
--Create assembly [System.Windows.Forms]
CREATE ASSEMBLY [System.Windows.Forms] AUTHORIZATION dbo
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'
WITH PERMISSION_SET = UNSAFE
GO
--Create Assembly RTFConvertor
CREATE ASSEMBLY RTFConvertor from 'D:\Assembly\MigrationTool.dll' WITH PERMISSION_SET = UNSAFE
GO
I had to use unsafe because of the System.Windows.Forms.
If all you are using is System.Drawing then replace where I have System.Windows.Forms to be System.Drawing
If you have a reference in your code to System.Windows.Forms. Then just add it because System.Drawing will be picked up.
Hope this helps you some
February 15, 2010 at 1:13 am
Great !
It works !
Thank you very much, you get me out of an awkward situation.
Maurizio
February 21, 2010 at 10:15 am
OMG - no!
Never use this as a work-around, it is dangerous and completely avoidable.
Never load an assembly into SQL Server with the UNSAFE permission set if you can avoid it - and certainly never use it to force code with a UI component into SQL Server! If your assembly contains a RichTextBox, it does not belong in the database!
SQL Server protects you from a good many things with the hosting controls. If you are dumb and determined, you can work around many of the controls - that does not make it a good idea, at all.
Paul
February 21, 2010 at 10:20 am
We are migrating data from an Informix database and in it there is RTF text. We are using SSIS to move the data to SQL Server staging database. Then we are using SQL Stored Procedures to manipulate the data and move it to the final SQL Server database. We need to take this rtf and store it as normal text. So we need to convert it from the rtf that was stored in the Informix database to text in the destination database.
So unless you know a way to have SQL Server convert this data from RTF to text this was our solution.
Joe
February 21, 2010 at 10:24 am
joepacelli (2/21/2010)
We are migrating data from an Informix database and in it there is RTF text. We are using SSIS to move the data to SQL Server staging database. Then we are using SQL Stored Procedures to manipulate the data and move it to the final SQL Server database. We need to take this rtf and store it as normal text. So we need to convert it from the rtf that was stored in the Informix database to text in the destination database.So unless you know a way to have SQL Server convert this data from RTF to text this was our solution.
Joe
Absolutely any other method would be preferable.
Use the scripting component in SSIS, perhaps?
Stage the data out somewhere, batch convert the RTF in place (using a dedicated executable converter), and re-load it?
Just don't do it in the SQL Server process space!
Did you not stop to wonder why it was so hard? It is a desperately terrible solution. Reconsider.
Paul
February 21, 2010 at 3:36 pm
joepacelli (2/21/2010)
We are migrating data from an Informix database and in it there is RTF text. We are using SSIS to move the data to SQL Server staging database. Then we are using SQL Stored Procedures to manipulate the data and move it to the final SQL Server database. We need to take this rtf and store it as normal text. So we need to convert it from the rtf that was stored in the Informix database to text in the destination database.So unless you know a way to have SQL Server convert this data from RTF to text this was our solution.
I posted a solution for this over in the other thread: http://www.sqlservercentral.com/Forums/Topic864014-386-1.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 9:37 pm
Performing the data conversion before it hits the database at all is still cleaner and more efficient in my view.
As far as existing data is concerned, bulk copy out, convert, bulk copy in. This would be an off-line operation, naturally.
Paul
February 22, 2010 at 12:33 am
You are right Paul !
But my dll doesn't use any UI object, the system dll's where I had problems are used by Printer component that I use use in my dll. I don't call any UI from a sql script: it makes no sense.
Maurizio
February 22, 2010 at 1:55 am
For some reason, every callable RTF conversion facility that I have seen, at least four different ones, was linked/bound to one or more system UI libraries (probably to give you the option of displaying it at the same time). Thats why doing RTF conversion is forcing you to bind(*) to the UI dlls.
*(link to? whats the proper term these days?)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2010 at 2:07 am
I still say 'bind to' - no doubt some young upstart developer like Flo will set us both straight...:w00t:
RTF conversion is something I have happily managed to avoid so far, but if I had to do it, I think I'd be quite happy to use a system UI library.
Just not from within the SQL Server process space.
Paul
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply