SQL Server Performance Forum – Threads Archive

Need help – DTS, Triggers, and Data

Hi I am running SQL Server 2003 SP3 and need some help in trying to send data from SQL Server to AS400 and then update the source table field in SQL Server. Here is the problem: I have a table that contains all items that have been paid for. The table includes a bit field (issenttoas400) which by default is set to 0 on insert. At this point, I have a DTS that runs every 5 minutes to take the newly added records and copy them to the as400. So the job, runs in 2 steps. 1. copy all records with issenttoas400=0
2. set all records to issenttoas400=1. The problem is that there have been cases where an insert will occur while step 1 is running and a record will be missed from being sent over to the as400, but will get marked as sent when step 2 runs. How do i solve this problem? Should I use an AFTER or an INSTEADOF trigger? Stick with DTS? Write the selected records to a temp table, then bulk insert and then update? Any suggestions and sample code would be helpful. I have looked at many books and nothing seems to answer this question. Thank you
Bharat

What about grouping the 2 transactions with a begin before the first and a commit at the end of the second? I dont know how long its taking, or if you’ll have any locking, but it might work.

If you can set the default value of your field issenttoas400 to NULL and update it to ‘0’ before you copy them to your AS400 then this should do the trick. In this case, everytime a new record is inserted it will have a value of NULL and not 0 since i supposed you are executing your 2nd query using "where issenttoas400=0". Thus, your 2nd query will only affect those records with "issenttoas400=0" and not "issenttoas400 is NULL". I hope I’ll explained it well. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] If not, try one of the following: 1. Add a field to your existing table let’s say "processedtag". This field should help you determine which records are currently being processed. Update this field to ‘Y’ before you copy them to your AS400. All new records that are being inserted will have an initial value of NULL or ‘N’. 2. Create a temporary table that will hold all records currently being processed then update your table afterwards. But I’ll go with #1 or the above solution if it is possible. Jon M

Thank you for the responses. I was also wondering what type of locking could i do in DTS? In response to Jon M’s reply—
I can set issenttoAS400 to default to NULL but i don’t know how i would loop through each record, set it = 0, send it to the AS400, then update it to = 1 and loop through until all records are sent in DTS. I can do this via VBScript/ASP from the web – that is not a problem! I need some help on doing this within SQL Server. Also, how about triggers? AFTER trigger – would that work and how? I appreciate all your help (ChrisFretwell and Jon M) Any more help is greatly appreciated! Thank you
Bharat

Assuming that you will set issenttoAS400 to default to NULL, I think you may stick with DTS: 1. Include in your DTS Package an "Execute SQL Task" that will execute similar sql stmt: update mysqltable
set issenttoas400=0
where issenttoas400 is NULL 2. On success (workflow) of #1, copy your records using "Transform Data Task" or whatever you have in place. The idea is: insert into myAS400table
select fields.. from mysqltable where issenttoas400=0 3. On success (workflow) of #2, update your issenttoas400 to 1, that is: update mysqltable
set issenttoas400=1
where issenttoas400=0 The next time your DTS run, which runs every 5 minutes, new batch of records will be sent again which I hope will eliminate your problem below. "The problem is that there have been cases where an insert will occur while step 1 is running and a record will be missed from being sent over to the as400, but will get marked as sent when step 2 runs.
" Jon M

Jon M, Your solution works great! I appreciate all your help. Thanks a bunch! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Bharat

]]>