Printing from CLR throws an exception

  • 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.

  • 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
  • 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.

  • 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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

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

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

  • I have already done that.

    Same error

    Thanks.

    Gunjan.

  • I am only trying to print it out.

    and this line throwing an exception

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

    Thanks.

    Gunjan.

  • 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?

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • 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
  • 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 | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • 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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • 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.

  • 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".

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • 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.

    Thanks.

    Gunjan.

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

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