Threading from an xp
-
Hi,
Does anyone know if you can kick off a thread from an xp? I need to execute an xp on atrigger and from the xp thread off a process so I can return control to SQl as quickly as possible. If this can be done or anyone has done it could they let me know their experiance of it.
Cheers,
Alan.
-
I do not know it's possible or not. It's upto the code of the xp.
Waht about to schedule a job that runs immediately in your trigger?. Let the job do whatever you want.
-
I do not think that would work because you would still have to wait for the job to complete before the control of execution would return to the trigger. I need to return control as soon as possible, hence the requirement of a thread to take on the new flow of control and pass old back to trigger.
-
Yep the the lock will remain untill the preocess trigger is completed. You could use the trigger to insert relevant rows into a table and then use a scheduled job to pole that table every minute or so running your xp where appropriate. This way the running of the xp does not tie up the the table on which it depends.
-
This would be far to slow, i need real time event processing from the update - the maximum delay time I could have from the update to the external vb app being notified is 1.5 secs
-
Alan "I do not think that would work because you would still have to wait for the job to complete before the control of execution would return to the trigger. "
Even if the job is scheduled to run immediately, it will run in another SPID. Your trigger can get control immediatly after the job is created. Or you can even start the job in your trigger. The trigger does not wait the job to finish too.
You can wrap all the logic after calling the xp into the job so that scheduling the job is the last command in your trigger.
You can test the latency. 1.5sec sounds good enough for the job to be created and started.
-
Did not know that an execution of a job would run out of process in another SPID - if this is the case then this sounds like a viable solution. However, I need to pass the id of the row that has been updated in the trigger to the encapsulated logic and as far as I know you cannot parameterise a job.
Do you know if a sp_cmdshell call to the DTSRun.exe from a trigger would run in another SPID, this would then allow me to pass a parameter.
-
The Unix 'fork' command would be a useful addition to the world of Windows ... MS has taken more, | and other things from Unix, why not 'fork' too ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Alan, what you need to do is to create the job on the fly in the trigger. When you call
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep,
pass your sp/xp and parameter in the @command parameter.
e.g.
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep,
@job_id = @JobID,
@step_id = 1,
@command = N'
EXEC dbo.your_sp '+CAST(@p1 as nvarchar(100))+',
@database_name = .....
Then call msdb.dbo.sp_start_job.
The parameter (@p1) should be PK or unique key so in your SP it can be uiniquely identified
If multiple rows are updated in the trigger, you can build a list and pass it to the SP.
-
Use xp_CmdShell and START a copy of OSQL to run a script...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
I think that to start a job from a trigger is BAD!!!! SQL Server Jobs are NOT re-entrant. Yes, they execute asynchronuosly and in a different process but if the trigger fires again while the job is still executing can you guess what's going to happen?
The solution to this problem is more architectural than anything else. What are you trying to do on that Process?
If is going to use the inserted data then you can use the Front end to call that app instead of the trigger. If you need a reaction instead then you will need to poll on the server.
Without more details all we are doing is especulating ...
Just my $0.02
* Noel -
Polling is not a viable option as this would be too slow, the VB app in the architecture outlined below needs to know immediately about an insert. The current problem is that due to frequency of inserts I am getting a deadlock. So to fix this I need to return control to the trigger as quickly as possible.
ARCHITECTURE:
A C++ client application on a PC
-Puts a row in the table
An SQL Server that holds the updatable table
-This table has a trigger on it that, when a row is updated, fires an extended stored procedure
-The extended stored procedure, via COM architecture, calls into an exposed method of a VB application
A VB application that recieves the COM call from the extended stored procure
-This does some processing then returns control back to the extended stored procedure
-
OK let's change your perspective a little.
What would the xp do if is is called?
Does it need the updated DATA?
is the XP re-entrant?
if Fundamentally all you need is that the Vb App knows about the update not about the "updated data" there are other mechanisms but I need you anwsers FIRST
* Noel -
When the trigger fires for the update it takes the data of the updated row and constructs a control string which it passes to the xp. The xp then passes this into the VB app, the VB app then just frames this string and passes it to a Programmable Logic Controller (PLC) via TCP/IP sockets). As this action is asynchronus the VB app then passes control back to the xp.
My original question about threading was asked because I would like the xp to create a thread for the call to VB app so that I could return control to the trigger ASAP.
I am not sure what the term re-entrant means but I will hazard a guess that it indicates the xp can be fired in repetitively or at the same time then the answer is yes. There are four C++ client apps that can update rows in the triggered table.
This system originally had an Oracle DB as the data tier which utilised oracle alerts to implement this event driven architecture, the VB app (which was C++ originally) could then subscribe to the update alert of the table. A very succinct and useful architecture that SQL Server sorely misses!
-
OK These are my assumptions
1. Updates are not affecting large amount of rows!
2. Xp can actually be an independent application ( or service )
On the trigger you build the Control string. Then use xp_logevent with the control sting as the Message Parameter. Next your service which is either watching Windows Applog or has a Dependency hook on it uses the information and Broadcast that to the VB app wich will send that to a PLC. and handles all comunication back.
another way which is the one I preffer is if all DML are carried out through stored procedures (another reason to try to use sp always for DML
) then on the stored procedure simply send the message to a queue a where your service takes care of it. No trigger NeededBTW: Re-entrant means that the code can be executed without any dependency of previous status or data. A PLC Ladder Logic program is an example of reentrancy. SQL Server jobs are NOT allowed to re run while already running (not reentrant)
SQL Server can do many many things you just need to know where to look
Ah... and I come from an EE background, I used to build SCADAS and had to do many of these things a long time ago
Cheers!
* Noel
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply