Delaying execution of code.

[kuruption213]

I have a problem with an application that is being executed. Here is the situation.

There is a legacy application (built in Delphi) that inserts data into a SQL Server 2000 database. I have built triggers that takes this data and sends it to a series of temporary table and calls the first console application. The console application creates a message in the Message Queue passing the temporary table name. A MSMQ trigger is then fired which calls another console application. This console application does a query from the correct temporary table to pull all the data so that it can be sent to another application in realtime using web services. The data being passed is a single product (data stored in the products temp table) which has multiple attributes (data stored in the attributes temp table).

The application runs ok except for products, but not for attributes. Only the first attribute is sent, disregarding the others. The attributes are being entered into the temporary table but my datareader only returns one row. This occurs because I fire the event on the first insert of an attribute (because if I fire it every time I get a T-SQL error stating the connection is in use). All of the items are entered into the database as a dump rather then one at a time by user involvement. So what I have tried to do as a counter balance is to put a Thread.Sleep of 5 seconds before my data reader pulls all the data from the database. However it always only brings back 1 row for the attributes.

The following is my code, edited to make sense in abbreviated form...

1. Triggers of attributes...

'insert into temp table

CREATE TRIGGER [TRG_SEND_ATTRIBUTES] ON [dbo].[ATTRIBUTE]
AFTER INSERT, UPDATE
AS

INSERT INTO [ATTRIBUTES_TEMP]
([ATTRIBUTE_ID]
,[ATTRIBUTE_DESCRIPTION]
,[ATTRIBUTE_PRICE],...)
SELECT [C_Case_Number]
,[ATTRIBUTE_ID]]
,[ATTRIBUTE_DESCRIPTION]
,[ATTRIBUTE_PRICE]...
FROM inserted

--THIS IS JUST A JOB THAT CALLS THE STORED PROCEDURE IN THE NEXT SECTION
EXEC msdb..sp_start_job 'RUN ATTRIBUTE MSMQ TRIGGER'

return


2. Stored Procedure
CREATE PROCEDURE [dbo].[sp_msmq_ATTRIBUTE_trg] AS
--SENDS NAME OF THE TEMP TABLE WHERE DATA HAS BEEN ENTERED TO A MESSAGE QUEUE BY CALLING A SMALL APPLICATION

BEGIN
DECLARE @cmd varchar(100)

SET @cmd = 'D:\Triggers\MSMQTRIGGER\bin\SendInfoToMSMQ.exe ATTRIBUTE_TEMP'
EXEC master..xp_cmdshell @cmd

END

CONSOLE


3. CONSOLE APPLICATION #1 CALLED AND SENDS DATA TO MSMQ WITH THE TABLE NAME. A MSMQ TRIGGER IS THEN CALLED TO SEND THE DATA TO CONSOLE APPLICATION #2

4. CONSOLE APPLICATION #2
..
Sub Main1()
..
'sleep for 5 seconds to wait for all the rows to be inserted.
Thread.Sleep(5000)
MainSub2()
..

Sub Main2()
'loop through messages in the queue
myEnumerator = myQueue.GetMessageEnumerator()
..
Try
While myEnumerator.MoveNext

'get table name from msmq message
strTableName = StringFromMessage(myEnumerator.Current)

if strTableName = "ATTRIBUTE_TEMP" Then

' get all the rows from ATTRIBUTE_TEMP, but only one is returned even though more then one is in the ATTRIBUTE_TEMP table.
end if


Loop
Catch
...
End Try

If anyone can please help me out with this I would really appreciate it. I'm really stuck on this one.

 

[cjard]

did you ever consider that what youre doing here is basically attempting to insert a patch on top of a bodged 7 step process? There comes a time when its faster and cleaner to rewrite a section of nasty nasty code...
Triggers calling console exes, that requery the database and send data to a webservice? Huh? Dithc the whole lot and make the webservice get the correct data it needs straight up..