Major Monotonous Job need ideas

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Major Monotonous Job need ideas

Author

Topic

chrisnorris007
Starting Member

18 Posts

Posted - 2009-06-16 : 11:39:09

OK YOU SQL GURUS!

I have written a stored procedure that writes out on a daily basis .eod files containing important financial data. I need to go back and run this procedure EVERY day since we opened in 2007. What is the easiest way to do this?

Currently the stored proc using bcp to write out to a csv file every hour.

The only thing changing to run it for another day is the variable @rundate.

Chris

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 11:44:08

Add a date to the stored procedure as parameter then loop through the dates running it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chrisnorris007
Starting Member

18 Posts

Posted - 2009-06-16 : 11:45:29

I would but its like 3 years worth of days. Kinda hard manually doing it. I thought of scripting a job (dont know how) to create multiple steps (one for each day) then run that job.

Whats your thoughts?

Chris

chrisnorris007
Starting Member

18 Posts

Posted - 2009-06-16 : 11:47:13

so if I create a loop around the procedure (proc_chris_dailywriteout @rundate) and increase @rundate....it will wait for the proc to finish before repeating it?

Chris

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 11:51:00

yes
declare @d datetime
select @d = '20070101'
while @d < getdate()
begin
exec mysp @d
select @d = @d + 1
end

In the sp set the parameter to default to null and if null set it to the current date - then you can run it for any date and it will default to the current date if run without a parameter.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chrisnorris007
Starting Member

18 Posts

Posted - 2009-06-16 : 11:53:32

thank you very much. I didnt think it would make sure it was finished before running again because i know it doesnt work like this.

chris_writeoutdaily '2009-06-03'
chris_writeoutdaily '2009-06-04'
chris_writeoutdaily '2009-06-05'
chris_writeoutdaily '2009-06-06'
chris_writeoutdaily '2009-06-07'

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 12:02:52

You would need exec in fromt of that
exec chris_writeoutdaily '2009-06-03'
exec chris_writeoutdaily '2009-06-04'
exec chris_writeoutdaily '2009-06-05'
exec chris_writeoutdaily '2009-06-06'
exec chris_writeoutdaily '2009-06-07'

It will wait for the previous call to complete before the next call (although the completion notification will wait until the output buffer is full or the last statement completes).
You might put something asynchronous in the SP (like a start of a job) but a bcp call will be synchronous.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chrisnorris007
Starting Member

18 Posts

Posted - 2009-06-16 : 12:08:22

thanks dude :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources