Time of Day Trigger

Question

Post reply

Time of Day Trigger

Tergum Rufus

Say Hey Kid

Points: 681
More actions
March 1, 2014 at 9:55 pm

#288845

Please can someone tell me how to trigger an sp at a fixed time of day without using SQL server agent?

Viewing 11 posts - 1 through 10 (of 10 total)

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

Create a *.bat file and use sql command to execute it - then create a scheduled task to execute it @Echo Off

set ServerName=YOURSERVER

set DBName=YOURDATABASE

set sql_login=USERNAME

set sql_passwd=PASSWORD

if exist *.outdel *.out

echo working... DO NOT CLOSE

REM ************************************************

sqlcmd -Q "exec YOURDATABASE.dbo.YOURPROCEDURE" -S %ServerName% -d %DBNAME% -o C:\YOURPROCDURE.out

echo All Scripts have been Executed

:end

pause

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Tergum Rufus (3/1/2014)
Please can someone tell me how to trigger an sp at a fixed time of day without using SQL server agent?

Why? What's the problem with using SQL Server Agent?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

I thought the same thing Jeff, but figured it was probably because either they didn't have permissions to create an Agent job or because they have hundreds of them and didn't want to add another to the list :hehe:

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

MyDoggieJessie (3/2/2014)
I thought the same thing Jeff, but figured it was probably because either they didn't have permissions to create an Agent job or because they have hundreds of them and didn't want to add another to the list :hehe:

One can only hope that if the OP doesn't have the privs to create an Agent job nor the nads to run it through the resident DBA, that he also wouldn't have the privs to do it through Windows Scheduler. :hehe: And, hopefully, a DBA doesn't have a problem with hundreds of jobs. 😉

@Tergum Rufus,

What say ye, man? Why do you need to schedule jobs without creating an Agent job?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tergum Rufus Say Hey Kid Points: 681 More actions · Answer 5

Thanks everyone for responses. JM - it's for clients with SQL EXPRESS Editions

TR

Tergum Rufus Say Hey Kid Points: 681 More actions · Answer 6

Hi

Thank you for the messages.

We don't like using sqlcmd cos credentials too exposed. As above the question relates to our clients who are using SQL express Editions without Agent.

Was looking for a time trigger to fire our BU script something like this [but can't do of course on sys.views]

DECLARE @T varchar (16),@Time varchar(16)

SET @Time= '11:55'

SET @T = (SELECT(CAST((SELECT TOP 1 [last_batch]

FROM [msdb].[sys].[sysprocesses]

ORDER BY last_batch desc) AS TIME)))

IF @T > @Time

BEGIN

PRINT 'Starting backup script at ' +@T

END

ELSE BEGIN

PRINT 'Skipped Backup DB'

END

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

You should be able to use sqlcmd without specifying credentials (I just provided a bad example above). Here's an article pretty much outlining exactly what you are trying to accomplish:

http://www.nimbleuser.com/blog/posts/2010/scheduling-a-backup-job-for-sql-express-without-a-sql-agent/

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 8

Actually this is the better article (it's the source for the one I previously posted, and is much more detailed)

http://www.mssqltips.com/sqlservertip/1174/scheduling-backups-for-sql-server-2005-express/

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

Tergum Rufus (3/2/2014)
Thanks everyone for responses. JM - it's for clients with SQL EXPRESS Editions
TR

Ah... got it. Thanks.

If you have Windows security running, SQLCmd does't require exposed credentials. Whatever user the Windows Scheduler job is using could use a "trusted connection". Or, the link in the post immediately above seems to be ok. It doesn't expose passwords.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tergum Rufus Say Hey Kid Points: 681 More actions · Answer 10

Thank you folks. Use Trusted in the SQLCMD is something we have previously missed.

Pursuant to this, I would be interested to know a way to produce a server-wide trigger [or other device] on the current time of day, as illustrated above.

TR