CODEWITHSUNDEEP
sqlsql-serversql-server-expresssql-server-2014-express
Pravin Pawar
Pravin Pawar

Reputation: 1

How call Procedure in Mssql on Specific Time

I am Using SQL server 2014 and i want to run a procedure automatically as per scheduled time.. I want to Sync Data from One Database to Another Database...

It is any Option in Express to Scheduled to call a specific procedure....

Upvotes: 0

Views: 431

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

Provided you don't have complex scheduling needs but only need something to run, say, once a day at a fixed time, you can combine a few features.

You can create a stored procedure that never ends, with a body something like:

WHILE 1=1
BEGIN
    WAITFOR TIME '22:00'

    EXEC SomeOtherTask /* parameters, etc */
END

You put this procedure in master, and then call sp_proc_option on it to mark it as a startup procedure.

Then restart your SQL Server instance and, at the appointed time, SomeOtherTask should be executed.

Note that you wouldn't want to over-use this technique - it keeps a connection permanently tied up just waiting for time to pass. Much easier (but of course with a cost) is to move up to a more fully-featured edition. You're starting to do things that are causing you pain - such as job scheduling and what appears to be some form of replication which are built into higher level editions. You may have outgrown Express edition.

Upvotes: 1

Squirrel
Squirrel

Reputation: 24803

in SQL Server Express edition, there isn't a SQL Agent for scheduling. You can use Windows Task Scheduler and use sqlcmd to execute your stored procedure

Upvotes: 1

Related Questions