I am writing a small customisation to an SQL based accounting system. When a new sales order is created by a custom stored procedure, I want send a copy of the order via email to the dispatch dept.
Plan A - xp_cmdshell The accounting package has a report writer that can receive the required parameters to produce a pdf file, but it does have a gui, though there is no requirement for the user to respond to anything, but since it has a windows gui implemented I cannot use xp_cmdshell to run the report writer exe.
Plan B - SSIS package I figured I would try using SSIS instead, this seems to work fine, passing the parameter (the SEQNO of the order I want to print among other things), however I still can't find a way to run this from within the stored procedure which creates the order. Again I come up against xp_cmdshell and the gui limitation, since the dtexec utility still gets called using xp_cmdshell and so the gui based exe fails to run properly.
Plan C - SSIS package run using SQL Server Agent My next idea was to create a job in SQL Server Agent to run the SSIS package - but I can't figure out how to pass the parameters back to the package - remember I need to tell the package which order to generate the report and email for.
I am open to ideas and any pointers here!
Thanks a heap, Andrew PS thanks to all of you for the useful and interesting articles and blogs.
asked Apr 08, 2011 at 12:52 AM in Default
Ideal solution could be use a service broker,
But the easiest way would be use the SSIS package which you run using the SQL Server Agent (Plkan C).
For the stored which saves a new order, you can write the ID of the order to a separate table, which will be next processed by the SSIS package which will send the reports. In the table you can along the order ID store all necessary information for the SSIS package. Once you process the records by SSIS package you, can delete the processed records from the table.
Then you schedule the job to check periodically the table and send all necessary information in some convenient time periods.
PS. The option A is the worst one and you should never use such things. Option B without Agent is the same as A.
As @Kev Riley mentioned, in case you need to send PDF with the details and you have Enterprise version of SQL Server I would go by creating a report using based on the table I mentioned on the beginning. Then Use a Data Driven Subscription in SSRS to deliver the report using email with attached PDF version of the report. Again the subscription could be executed in some convenient intervals to check whether there are any changes or not.
Another option is to create a utility in another language, perhaps PowerShell, that will call your stored procedures and then call your GUI report creator. As long as the GUI does not wait for user input, this should cause no problems. You can then put this script on a timer using something like Windows task scheduler.
answered Apr 11, 2011 at 01:46 PM
I'm going to side with the 'bad idea' answers here. Why do you feel you need to open a GUI from what should always be considered a 'server-side' process?
For mail sending, try `sp_send_dbmail` from within your stored procedure.
For PDF generation, if SSRS is a no go, try an SSIS with a .NET Script Task that links in an external library, like iTextSharp. You could start this with `sp_start_job`, but my preference would be to use a
If you have a GUI component that you need end-users to see, it must not be instantiated on the server (where they won't see it) but on the client (where they will). For this, you are looking at writing a client app that interacts with your database, rather than allowing users to interface with the database directly. To do it any other way would be a massive risk to your server's reliability and performance, not to mention your own sanity.
answered Apr 13, 2011 at 06:21 PM