asked
How do I start a windows app (with a gui) from a stored procedure on SQL 2008
Hi 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.
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. **EDIT:** 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.
The option could be the XP_cmdshell, but you should never do this. Remember, that the processing of query/command will be blocked until the xp_cmdshell returns. What more.. Mostly you work remotely from the SQL Server instance and all you execute using the xp_cmdshell, is executed on the server side and not the client side.
And a question.. why you would like to launch an application with gui? You would like to send an email using an email client? This si wrong.. and you should use the sendmail task in the SSIS.. not launching the email client.
Thanks for that! Out of interest, does any way to directly run an app with a gui exist with SQL?
@Pavel, think Andrew needs the app to generate the pdf in a particular way, not necessarily start an email client
@Kev Riley in such situation and having an Enteprise version of SQL Server I would go by Reporting services and DataDriven Subscription. :-). I update the answer and add this option to.
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.
Yeh, this maybe could work, but hard to say. As probably even in this case you will have to run the utility in interactive mode - I mean to have a logged console or remote desktop as the GUI report creator caused problems when executed eg. from xp_cmdshell etc. The GUI of the report creator seems to cause problems when executed in non GUI interactive mode.
Yes, have to agree - the GUI flashing up its "Accessing Data" dialog box seems to be what upsets things - in fact same thing happens whether run from xp_cmdshell or SS Agent. I am thinking the only way past is to use something that doesn't throw up any kind of user interface - i.e., a console app or similar is the only reliable way to do this or as Pavel suggested using report server.
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? - If you need to open a GUI in order to generate a PDF, you are using the wrong PDF-generating tool. - If you need to open a GUI in order to send an email, you are using the wrong email-sending tool. - If you need to open a GUI in order to do x, you are using the wrong x-doing tool. For mail sending, try [`sp_send_dbmail`](
http://msdn.microsoft.com/en-us/library/ms190307.aspx) 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](
http://sourceforge.net/projects/itextsharp/). You could start this with [`sp_start_job`](
http://msdn.microsoft.com/en-us/library/ms186757.aspx), but my preference would be to use a `BIT` column in your table to determine whether a mail had been sent, then use a scheduled job to run the SSIS package which loops through all orders where `mail_sent = 0`, generates the PDFs, send the mail and sets `mail_sent = 1`. 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.
