Post reply

Linked Server Trigger Issue

  • August 31, 2005 at 10:52 am

    #91009

    All,

    What I amn trying to accomplish is I have a .dbf file that I would like to "trigger" the creation of a new SQL record in a table whenever a new record is added to the .dbf table.  I have played around with Linked Servers and triggers in SQL but it only works in reverse (SQL-Linked) and not vice-versa.  Does anyone have any sggestions on how this can be accomplished?  Any info would be appreciated.

    Thanks! 

  • August 31, 2005 at 11:47 am

    #586588

    i think you'd need to create a job on sql server that would scan the linked server table every x minutes, and perform the new record addition when any changes are found.

     

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 31, 2005 at 12:31 pm

    #586600

    What kind of overhead would this produce if I was scheduling this job to run every 2-5 minutes?  Would it pig things out?

  • September 1, 2005 at 6:30 am

    #586785

    If the dbf table has some kind of autonumber or timestamp field (or one could be added), so your scheduled task can use something like "WHERE {timestamp} > {last run time}", the scheduled job shouldn't be too bad.  Assuming the autonumber/timestamp field is indexed.

    Can you add a flag to the dbf table, so you can query for "WHERE {flag} = False"?  Then update the flag to True after copying them.  This would be slower than a timestamp, but probably tolerable (depending on concurrent use and locking on the DBF side).

    Otherwise it will have to scan the entire dbf table and join to the SQL records to identify new entries.  That will be a pig.

  • September 1, 2005 at 2:51 pm

    #587029

    It does have a status field that I can query.  I am going to try this approach and see if it will work.  Thanks so much for the info!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply