locked
DB Advice RRS feed

  • Question

  • I have a database already created and in production. Now a 3rd party system needs to have access to run 3 specific stored procedures to pull data. The 3 stored procedures must be run in a specific order and will be executed via a job from that application, not from sql. Once those rows/data are sent we never send them again.

    My question is how would I design a way to first select rows that have yet to be sent, and 2nd a way to record what has been sent. I assume after the 3rd stored procedure has successfully run the records have been sent ok as the 3rd party system will not execute if the previous one failed. Any suggestions?

    Tuesday, September 17, 2013 4:17 PM

Answers

  • My question is how would I design a way to first select rows that have yet to be sent, and 2nd a way to record what has been sent.


    Then you have to add an additional column to the table(s) to store the state "not send"/"send"; the SP which queries and "sends" the data have to update the data to "send" afterwards.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Saeid Hasani Tuesday, September 17, 2013 4:48 PM
    • Marked as answer by Sofiya Li Wednesday, September 25, 2013 9:20 AM
    Tuesday, September 17, 2013 4:27 PM
    Answerer

All replies

  • My question is how would I design a way to first select rows that have yet to be sent, and 2nd a way to record what has been sent.


    Then you have to add an additional column to the table(s) to store the state "not send"/"send"; the SP which queries and "sends" the data have to update the data to "send" afterwards.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Saeid Hasani Tuesday, September 17, 2013 4:48 PM
    • Marked as answer by Sofiya Li Wednesday, September 25, 2013 9:20 AM
    Tuesday, September 17, 2013 4:27 PM
    Answerer
  • "Once those rows/data are sent we never send them again."

    Sorry that but is simply foolish optism.  In reality, things break.  Something goes wrong somewhere and you do, in fact, need to resend rows that have already been sent.  It will happen eventually; of that I can assure you.  As Olaf suggested, you will need to store the "state" of each row, though you don't necessarily need to do it in the same table.  I also suggest you plan now to put in sufficient information to be able to diagnose problems - especially problems related to incorrect usage by this 3rd party system.  I would also reconsider a design that requires the execution of separate procedures in a specific order, even if that means you have to duplicate logic. 

    Tuesday, September 17, 2013 5:10 PM
  • Olaf solution is good one... However , if you cannot add the columns and change the logic you will have to create another table  and move in all needed data( after job is completed  you can delete that data from the table)  , meaning the 3d party stored procedures take the data from that table....

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Sofiya Li Wednesday, September 18, 2013 7:59 AM
    Wednesday, September 18, 2013 5:56 AM