Post reply

How to do - While Loop, Courser, CTE ???

  • July 5, 2010 at 6:48 am

    #222891

    I have situation where I am pulling customer order information on daily basis through my stored procedure where order information is on serverB. My stored procedure is on ServerA and is running script via linked server.

    The stored proceudre pull any order that been created in last 24 hrs and dump's it into CSV file.

    Issue: Now, the real problem is ServerB goes off-line every several days during beginning of each month, so, sometime it goes off-line for 4 days, or 6 days. there is no fix date where it comes live. Now, during that time my SP runs but do not export out CSV file because the DBs is off line.

    For example: Sp will run daily at 7AM now let say it ran on 31st and there were few order created so, it create a CSV file. Now it comes 1st of the month, DB off-line..

    2nd,,,,DB still off line

    3rd....DB still off line,

    ..

    ..

    ..

    7th...DB still off line,

    8th,..DB is live and my daily schedule SP execute and pull data for last 24 hrs (since it is my script clause where createDate BETWEEN GETDATE()-1 AND GETDATE() )

    Now, What I am looking for is to create some sort of loop or process that go back 1st throuh 6th and pull data for the missing days.

    Please let me know if you need more details or advice me how do i accomplish this by during some sort of script so, i do not have to do manually every month.....

    Please advice,:hehe:

    Keyun

  • July 5, 2010 at 7:10 am

    #1188765

    Keyun, that's a lot of work, buddy! first get the today's date, then the first day of the month, then loop through each day to pull data from the DB 🙂 hmmm.. big SP in the making...

  • July 5, 2010 at 8:38 am

    #1188797

    If I'm reading this correctly, you need a separate file for each day. If so, then it sounds like you need two basic steps:

    1. Procedure to generate output file for a day.

    2. Procedure to determine what days have been missed, and run the first procedure for each day.

    For the main procedure, this does not need to be loop based.

    For the 2nd part, you just might have to break it down into a loop. I don't see a CTE working here.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 6, 2010 at 6:57 am

    #1189083

    WayneS - It are correct, My sp runs daily but at some point there db goes off line and do not have connection but as soon as connection open for user, I need to pull the data for the missing date.

    so, I think my big challenge is how to write my script to pull data from missing date or how does my script know that it needs to pull missing date data.

    I guess i can create a log table that enters the count/number of order by each date and then have it loop through...something like that...I am still doing brainstorm...

    let me know your thougths guys...any idea help greatly appreciated...:-)

    Thanks

    keyun

  • July 6, 2010 at 9:47 am

    #1189163

    keyun (7/6/2010)

    WayneS - It are correct, My sp runs daily but at some point there db goes off line and do not have connection but as soon as connection open for user, I need to pull the data for the missing date.

    so, I think my big challenge is how to write my script to pull data from missing date or how does my script know that it needs to pull missing date data.

    I guess i can create a log table that enters the count/number of order by each date and then have it loop through...something like that...I am still doing brainstorm...

    let me know your thougths guys...any idea help greatly appreciated...:-)

    Thanks

    keyun

    In order to determine that there are days missing, you will need to either have a log table that gets populated when the job does write out the file, or possibly scan the output files for any days that are missing. Without some sort of logging mechanism, I just don't see any other way to determine that you need to go back and run missing dates.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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