SQL - Generate a PDF

Question

SQL - Generate a PDF

Nuno Morgado 41

Hi everybody.
Is it possible to generate a pdf from SQL?

I already have an rpt connected to a SQL database and, if I use Crystal Reports, works fine but I wanted to do it from SQL.

Example, create a trigger that, when an insert occurs in a table, creates a pdf using an rpt.

Thanks in advanced.

1 answer

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

It's probably possible by writing a CLR stored procedure for the task, but having this procedure called by a trigger does not sound like the best of ideas. Keep in mind that even if you don't have a user-defined transaction, the trigger will run in a transaction defined by the statement that fired the the trigger. So the INSERT/UPDATE will not commit until that PDF has generated. This could cause some contention issues in a high-transaction environment.

And what if the PDF generation fails, do you want the INSERT to be rolled back in this case? (Because that is what is going to happen.)

No, most likely you are better off having some polling mechanism that looks for new rows and generates the PDFs. You could still generate the PDF from a CLR stored procedure, but there is quite a bit of red tape with adding CLR code, and you are probably better off by doing this from an external program. You should probably look into what scheduling opportunities that Crystal may offer

Nuno Morgado 41 Reputation points

2022-07-20T08:47:11.173+00:00

Hi ErlandSommarskog,
Thanks for the reply and by warnings.
I hadn't really thought of those details.
leaving the control in a trigger, now, doesn't seem like such a good idea...

I'm not comfortable with CLR SP, do you have an example that does what I want?
Thank you.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-07-20T21:22:15.473+00:00

I'm not comfortable with CLR SP, do you have an example that does what I want?

Let me ask this first: do you feel confident that you would be able to write a C# program that generates a PDF? Doing the same in a CLR procedure only means adding some extra steps. Of which some requires blessing from the sysadmin, so there is a bit of red tape. But if you don't feel writing a program that generates the PDF, there is little point in exploring the CLR SP option.

And, no, I don't know how to write a PDF from C#, but I assume that you can find libraries out there for the purpose. Hey, when I think of it, we generate a lot PDFs in the system I work with, but we do that outside SQL Server. We used a product that costs money for this, so this could also be a barrier.

And if you got the hint that using a trigger was not the best of ideas, what's then the reason to generate the PDF from inside SQL Server?

Share via

SQL - Generate a PDF

1 answer

Your answer