Monday, March 19, 2012

How To Create a PDF from a stored procedure

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name which has all these data is called dbo.TestTable.
I would like my stored procedure to output a PDF file with data from TestTable.
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help

Quote:

Originally Posted by goal2007

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name which has all these data is called dbo.TestTable.
I would like my stored procedure to output a PDF file with data from TestTable.
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help


There's no way a stored procedure can directly output its results to a PDF file that I know of. I suggest that you make a .NET component that creates a database connection, executes your stored procedure retrieving a dataset, creates a PDF file and writes the output to it. You can leverage a free iTextSharp library http://itextsharp.sourceforge.net/ for making PDF files. It provides for basic formatting and may be just what you need. Then, you have a few options of setting up your component to run either as a SQL job or Windows service or even invoke it from inside the stored procedure itself (requires CLR integration). Hope this is helpful. Let me know if you need more details.|||

Quote:

Originally Posted by davef

There's no way a stored procedure can directly output its results to a PDF file that I know of. I suggest that you make a .NET component that creates a database connection, executes your stored procedure retrieving a dataset, creates a PDF file and writes the output to it. You can leverage a free iTextSharp library http://itextsharp.sourceforge.net/ for making PDF files. It provides for basic formatting and may be just what you need. Then, you have a few options of setting up your component to run either as a SQL job or Windows service or even invoke it from inside the stored procedure itself (requires CLR integration). Hope this is helpful. Let me know if you need more details.


Thank you for your replay. Here is what i am trying to do. I am trying to setup sql schedule which is going to execute a stored procedure that gets some data then put these data into a pdf file. After that it will send an e-mail to the manger with new created pdf file. How can i accomplish that?
Thank you|||

Quote:

Originally Posted by goal2007

Thank you for your replay. Here is what i am trying to do. I am trying to setup sql schedule which is going to execute a stored procedure that gets some data then put these data into a pdf file. After that it will send an e-mail to the manger with new created pdf file. How can i accomplish that?
Thank you


You can create a Windows service that establishes a connection to the database, polls the database table(s), creates a new PDF file with the data retrieved and sends an email to the manager with this attachment. In the service, you embed a timer component for scheduling tasks. The actual scheduling info can be placed in .config file for starters.

No comments:

Post a Comment