Time of Day Trigger

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

  • 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

  • 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)

  • 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

  • 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)

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

    TR

  • 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

  • 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

  • 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

  • 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)

  • 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

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

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