May 22, 2008 at 9:46 am
Hello,
I have a question about the sequence of events in SQL Server. Here is my problem.
We are using handheld devices to send data to a SQL Server. The application on the
handheld will call a procedure with input parameters. I use these parameters to insert
a row in my table. The table has a trigger that executes a DTS package, which in turn
run a PowerBuilder application.
The data is inserted in the application and the DTS package is executed, but in my application,
I am not able to retrieve values inserted in the table. We are using SQL Server 200.
I would appreciate your help.
Thanks
May 22, 2008 at 10:23 am
Calling a DTS package from a trigger is creative, but a bad idea. The trigger is part of the original transaction, so your original record is not committed while your package is running. I am surprised you are not getting blocked by your own process and simply timing out. Regardless, if your DTS package fails, your original transaction will be rolled back. Also, your original transaction inserting the record cannot complete until your DTS package completes.
You have a couple of options for this. The first is service broker which is more suited to doing this type of event-driven action and ultimately probably the best solution - but you will need to learn how to use service broker.
The second is to have a process that basically loops and waits for new records to appear. You can then process the new records and somehow mark them as processed. This is what notification services did, but it has been removed in SQL 2008, so you should really build your own solution.
The third option would be to use replication. Replication allows you to execute a DTS package when a log entry is made for an article (rather than just copying the record to another server). This will move the watching for a transaction to replication services and de-couple it from your original transaction.
May 22, 2008 at 10:30 am
I am not familiar with the Service Broker functionality. Also, is it available in SQL Server 2000.
May 22, 2008 at 10:39 am
No, it's not, so scratch that option.
You are in a SQL 2005 forum, so I figured you were using 2005.
May 22, 2008 at 10:58 am
Michael Earl (5/22/2008)
No, it's not, so scratch that option.You are in a SQL 2005 forum, so I figured you were using 2005.
However - a scheduled task that routinely looks for "new requests" to process DTS is still viable. Or - any other mechanism that runs on a scheduler to go instantiate this powerbuilder app. DTS sounds like a bit of a waste if you're just using it as a wrapper to launch this app; if that's the case - perhaps a windows service or an externally-scheduled task to run the PowerBuilder app might actually work better (fewer dependencies, and fewer issues by not using SQL Server as an automation server).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply