x

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.
more ▼

asked Apr 08 '11 at 12:52 AM in Default

andrew clayton gravatar image

andrew clayton
21 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.
more ▼

answered Apr 08 '11 at 01:16 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

Thanks for that!

Out of interest, does any way to directly run an app with a gui exist with SQL?
Apr 08 '11 at 01:19 AM andrew clayton
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.
Apr 08 '11 at 01:22 AM Pavel Pawlowski
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.
Apr 08 '11 at 01:35 AM Pavel Pawlowski
@Pavel, think Andrew needs the app to generate the pdf in a particular way, not necessarily start an email client
Apr 08 '11 at 01:47 AM Kev Riley ♦♦
@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.
Apr 08 '11 at 01:54 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Apr 11 '11 at 01:46 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

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.
Apr 11 '11 at 10:12 PM Pavel Pawlowski
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.
Apr 11 '11 at 11:47 PM andrew clayton
(comments are locked)
10|1200 characters needed characters left

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 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 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.
more ▼

answered Apr 13 '11 at 06:21 PM

jimbobmcgee gravatar image

jimbobmcgee
359 4 5 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x900
x14
x14

asked: Apr 08 '11 at 12:52 AM

Seen: 1593 times

Last Updated: Apr 08 '11 at 12:52 AM