Stored procedure at specified time

Question

Stored procedure at specified time

sqlquery-101401

SSCarpal Tunnel

Points: 4951
More actions
March 2, 2004 at 3:59 am

#161167

plz suggest how i can execute my stored procedure at specified time.

Viewing 15 posts - 1 through 15 (of 18 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Take a look at jobs -> How to create a job in BOL

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 2

is there any other way through coding...................

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Why reinvent the wheel and not rely on a job?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 4

Reinvention of wheel can be useful in crunch so plz suggest if there is other way round ...........

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Honestly, I think that not only a job is the most simple alternative, it should also be the most reliable one.

However, you might be able to develop a small app in say VB, for example, and create a task in Windows to execute the program. Now the only thing inside this programm would be to call your stored procedure. Nothing fancy, but it should also work.

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 6

sqlquery-101401

SSCarpal Tunnel

Points: 4951

March 2, 2004 at 5:21 am

#496634

Thanks Frank

ThomasH SSCertifiable Points: 7437 More actions · Answer 7

I agree with the idea that a Job is most likely the best method. This can be done in code fairly easily (4 or 5 T-SQL statements), and the Job can even get rid of itself when completed.

If your "time to execute" if fairly short, you could use WAIT FOR.... This will keep the thread running until it completes, and will be lost if for any reason SQL service restarts.

If it's a repeating task, but does not happen at consistant intervals, you may want to make a Job that has a combination of schedules and WAIT FOR ... or after each run, have the Job's Schedule updated to reflect the next execution.

Once you understand the BITs, all the pieces come together

sharrell SSC-Addicted Points: 477 More actions · Answer 8

I'm not quite sure why you think creating a job means coding. Unless you really like writing T-SQL, creating a job in Enterprise Manager doesn't require any coding.

Since you are asking to have this scheduled, I'm guessing it doesn't return any data and is a periodic update of some sort. If that is the case, then you could do some coding and create a Batch or VBScript file to execute your stored procedure and then schedule it via the Windows Scheduled Task interface.

Regards,
Steve

Meddle not in the affairs of dragons, for you are crunchy and taste good with ketchup.

ThomasH SSCertifiable Points: 7437 More actions · Answer 9

After Frank posted about using a Jobs, the original poster asked "is there any other way through coding?", so I put in my 2 cents about being able to T-SQL code the Job without a whole lot of code needed. I use this kind of strategy on a regular basis when within my code I determine that I need to begin executing other SQL code at a specific time. Since my T-SQL can not operate EM, the T-SQL code simply creates the Job and its required parts and goes on without any human intervention required. The 3 or 4 required Job related SPs are not to difficult to deal with for anyone comfortable in T-SQL, and using EM to manipulate the Job's properties may not fit into the original poster's senario.

Once you understand the BITs, all the pieces come together

sharrell SSC-Addicted Points: 477 More actions · Answer 10

My mistake, I thought he was asking for a way to do it WITHOUT coding.

I agree that your solution is perfectly sound and I do the same on a regular basis. For some reason I read the thread wrong.

Regards,
Steve

Meddle not in the affairs of dragons, for you are crunchy and taste good with ketchup.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

After thinking over it again, I think there is basically no need for a separate small app for this. You should also be able to create a *.bat file that call ISQL or OSQL, schedule this with the Windows Scheduler and there you go.

BTW, can someone please tell which one ISQL or OSQL is deprecated? Some day I will learn not to forget this.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 12

FROM BOL:

All DB-Library applications, such as isql, work as SQL Server 6.5–level clients when connected to SQL Server 2000. They do not support some SQL Server 2000 features. The osql utility is based on ODBC and does support all SQL Server 2000 features. Use osql to run scripts that isql cannot run.

So I can safely assume that isql is on its way out

* Noel

ThomasH SSCertifiable Points: 7437 More actions · Answer 13

I just want to know the German word for "deprecated" , just so i know what it means from now on.

I've been on the MS SQL Srv bandwagon only since SQK2K, and have only ever used OSQL.

Once you understand the BITs, all the pieces come together

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

Hm...just took a look at this one

http://dict.leo.org/?p=2Ib6..&search=deprecated

And now I'm not sure if this is the right word for it. I think outdated is better.

Anyway, thanks for your answers.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]