Printing from CLR throws an exception

Question

Post reply

Printing from CLR throws an exception

Gunjan

Hall of Fame

Points: 3226
More actions
February 3, 2009 at 12:10 pm

#196778

Hi,
I am trying to print the byte array which I am receiving from reporting service's render method.
I have used PrintDocument class in my CLR stored proc. I have also addded the system.drawing assembly. here is my code
PrintDocument printDoc = new System.Drawing.Printing.PrintDocument();
public String PrintImage(string printerDestination, byte[] image)
{
try
{
string status = "";
_documentToPrint = image;
printDoc.PrinterSettings.PrinterName = @"\\printserver\HR Canon iR5070"; //printerDestination;
//PrintingPermission b = new PrintingPermission(PrintingPermissionLevel.DefaultPrinting);
strErrMsg = "Setting Print Controller->";
PrintController standard = new StandardPrintController();
printDoc.PrintController = standard;
//wire event
//printDoc.PrintPage += new PrintPageEventHandler(this.printDoc_PrintPage);
printDoc.PrintPage+=new PrintPageEventHandler(printDoc_PrintPage);
strErrMsg = strErrMsg + "Calling Print Method | " + printDoc.PrinterSettings.PrinterName + "|";
printDoc.Print();
//status = "File Printed";
return strErrMsg;
}
catch (Exception ex)
{
_documentToPrintCurrentPage = 0;
// strErrMsg = strErrMsg + ex.Message;
strErrMsg = strErrMsg + ex.Message + "->" + ex.InnerException.Message + "->" + _documentToPrint.Length.ToString() + "-> Print Image Method";
//status = strErrMsg;
// strErrMsg = "";
}
}
private void printDoc_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
{
strErrMsg = strErrMsg + "inside printDoc_PrintPage | ";
MemoryStream ms = new MemoryStream(_documentToPrint);
strErrMsg += "--lenght:" + ms.Length.ToString() +".";
Bitmap bm = (Bitmap)Bitmap.FromStream(ms); // Throwing an exception at this line
Graphics g = e.Graphics;
try
{
int pagesFound = bm.GetFrameCount(FrameDimension.Page);
if (pagesFound > 0)
{
bm.SelectActiveFrame(FrameDimension.Page, _documentToPrintCurrentPage);
_documentToPrintCurrentPage++;
if (_documentToPrintCurrentPage >= pagesFound)
{
e.HasMorePages = false;
_documentToPrintCurrentPage = 0;
}
else
e.HasMorePages = true;
g.DrawImageUnscaled(bm, 0, 0);
}
}
catch (Exception ex)
{
_documentToPrintCurrentPage = 0;
throw ex;
}
}
Any ideas?
Thanks.
Thanks.
Gunjan.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Um..

The CLR proc runs on the server, within the database server. Where do you think it's going to print to?

What exception is it throwing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gunjan Hall of Fame Points: 3226 More actions · Answer 2

It should print it to network printer.

and the exception is

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:at StoredProcedures.BRCOffer_Process(String DataBaseName)

.

Thanks.

Gunjan.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

You are the 3rd person to try this, and it is not possible to do. This is akin to trying to use a MessageBox.Show() in a clr routine in SQL. This is application code and SQLCLR exists only to extend existing functionality. This is why the approved classes for SAFE assemblies is limited. Just because you can load the assembly doesn't guarantee it will execute.

Move this outside of SQL server where it belongs.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

I believe that you have to explicitly catalogue System.Drawing.dll as UNSAFE in order to get it into SQL.

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jonathan: which is the part that cannot be done? Just the Bitmap stuff or do you mean anything from System.Drawing.dll?

[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]

Gunjan Hall of Fame Points: 3226 More actions · Answer 6

Gunjan

Hall of Fame

Points: 3226

February 3, 2009 at 12:46 pm

#937460

I have already done that.

Same error

Thanks.

Gunjan.

Gunjan Hall of Fame Points: 3226 More actions · Answer 7

I am only trying to print it out.

and this line throwing an exception

Bitmap bm = (Bitmap)Bitmap.FromStream(ms);

Thanks.

Gunjan.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

I am not in a position to check immediately but I would venture a guess that the HPA's on the Assembly may preclude it from use in SQL completely. At best ( or worst depending on which side of the SQLCLR fence you sit on) you will only be able to use part of the classes in the Assembly.

SQL Server is a database server, not an application, image processing, or print server. If it does have to do with validating/manipulating/reading/updating data it has no business in SQL server. You just can't convince .NET developers of this for some reason. Is there a DBA anywhere involved with this project/development?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jonathan Kehayias (2/3/2009)
If it does not have to do with validating/manipulating/reading/updating data it has no business in SQL server.

I think you left a not out of that statement

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Thanks Gail. Working from my Palm Treo at the moment and missed that word. Tiny keyboard and fat thumbs never works out that good.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

RBarryYoung (2/3/2009)
Jonathan: which is the part that cannot be done? Just the Bitmap stuff or do you mean anything from System.Drawing.dll?

According to the following blog:

http://blogs.msdn.com/tims/archive/2004/05/27/142798.aspx

System.Drawing is not allowed because of its HPA (Host Protection Attributes). I am inclined to trust this based on the information in the disallowed types lists in the BOL:

http://msdn.microsoft.com/en-us/library/ms403285.aspx

http://msdn.microsoft.com/en-us/library/ms403277.aspx

http://msdn.microsoft.com/en-us/library/ms403287.aspx

http://msdn.microsoft.com/en-us/library/cc645949.aspx

If you look at the lists of restrictions, these things really don't have anything to do with relational data processing. I would also suspect that these same HPA rules are why people can't use WCF in SQLCLR. You can use basic web services, but not the more advanced stuff because it uses External Threading which is incompatible with the Cooperative scheduling handled by the SQLOS. As for the Bitmap problem, it is likely using System.IO.Stream.Synchronized() which is banned for synchronization. If I need to I can look into the code in Reflector and clarify for sure why it is not allowed, but suffice it to be that you can't do what you are trying in SQLCLR. The code needs to move out to the application tier.

You might consider using a SqlDependency to trigger an external service to process your tasks by inserting/changing a record in a table that the Dependency watches.

Gunjan Hall of Fame Points: 3226 More actions · Answer 12

Thanks Jonathan.

I am going to use the WCF for printing the image. I will use it in my SQL CLR stored proc.

Thank you all for the help.

Thanks.

Gunjan.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 13

Gunjan (2/3/2009)
Thanks Jonathan.
I am going to use the WCF for printing the image. I will use it in my SQL CLR stored proc.
Thank you all for the help.

I don't think you read my post properly. You won't be able to call WCF from SQLCLR either in most cases. There are a few corner cases where people have gotten this to work, but good luck figuring out how they did it. I have yet to figure that out, just so I can answer people who ask how to do it and why it doesn't work, and I have tried a number of ways to make it work.

You need to create a standard Windows Service that does the printing and is activated through a SqlDependency object. That is the safe/best practice way of doing this kind of thing. Your stored procedure could still trigger the printing, by writing a row of data into a table. The SqlDependency will trigger an event in the Service when the data on the table changes, that can then allow the Service to spawn a thread from the threadpool and handle processsing/printing the report. Then if you choose, delete/update the row and reset the SqlDependency so it watches for the next change and let the service go back to "sleep".

Gunjan Hall of Fame Points: 3226 More actions · Answer 14

Hi,

I have already implemented that. To use the WCF service we need to add it as a Web reference in our SQL CLR project. I am done with the printing the images but now the problem is printing the PDF document. I am able to print TIFF byte arrays but not the PDF byte arrays returned from the Render() method of reporting services. Compare to PDF; TIFF is not having good quality; So as per my user's requirement I need to print the PDF byte array.

Thanks.

Gunjan.