February 11, 2004 at 4:54 am
hi every body ,i have some problems and i hope you help me..i used sql server with job schedule it step is : INSERT INTO t_mazz_sms_esf (caller_mobile_num, call_time, sms_body, sms_type, original_sms, operator_id, lang, status, voting_flag) SELECT Jawwal, [Date], Answer, 0, Answer, 10, Langaue, 0, 0 FROM MYAccess...Answers WHERE MARK=0 UPDATE MYAccess...Answers SET MARK=1 WHERE MARK=0 --- ok now all recordset have mark =0 will be insert to sql srvr database then set mark=1 the problem is while set mark=1 the access database recived new record and set mark =1 without go to sql srvr database,, are you understand my problem..what is the right T-sql statment ,,any help please??
February 11, 2004 at 5:46 am
The problem is the way your flagging mechanism works you have time for new records to enter without being seen. I suggest using a 3 value flag. Like so
UPDATE MYAccess...Answers SET MARK=2 WHERE MARK=0
INSERT INTO t_mazz_sms_esf (caller_mobile_num, call_time, sms_body, sms_type, original_sms, operator_id, lang, status, voting_flag)
SELECT Jawwal, [Date], Answer, 0, Answer, 10, Langaue, 0, 0
FROM MYAccess...Answers
WHERE MARK=2
UPDATE MYAccess...Answers SET MARK=1 WHERE MARK=2
What this does is set your items apart for handling from anything new that may come in while you are processing. Then when done processing you set the flag to yet another value to signify this.
Now as for using a linked server with INSERT I would not do. Convert the process over to DTS and dropped the linked server unless you absolutely need. When you create linkd servers they are handy for querying remote data sources by only attaching to the single server but I have found most people don't need and most people create security holes they don't realize. DTS is a better choice and gives you a lot more flexibility with all it's features.
February 12, 2004 at 2:04 am
ok ,that work fine with your idea , thank you Antares . what about DTS can you tell me hou use it and can DTS schedule ,thank very well
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply