Sql-server – How to automatically execute a stored procedure every day at a particular time

scheduled-taskssql serversql-server-2008-r2sql-server-express

Can you please help me how to execute a stored procedure automatically in SQL database without any transaction in SQL? I want to execute on particular fix time.

Best Answer

If you are not comfortable using Powershell, then you can go for native service broker functionality.

This might sound complex, but below links has all the scripts :

Scheduling Jobs in SQL Server Express - Part 1 and

Scheduling Jobs in SQL Server Express - Part 2
Alternatively, you can use Standalone SQL Agent - still in BETA statge from codeplex.
You can use the native sqlcmd functionality and then schedule it using windows scheduler or Schtasks
```
sqlcmd -E -S server_name -d database_name -Q "EXEC schema_name.StoredProcedureName @p1"
```

Related Solutions

SQL Server – Automatically Execute Stored Procedure After RESTORE DATABASE Event

Does it have to execute immediately after the restore finishes, or can it be briefly delayed? One idea I had is to have a job that runs every minute, and checks for the Audit Backup/Restore Event in the default trace.

DECLARE @fn VARCHAR(MAX);

SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1) 
  + CHAR(92) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT 
    DatabaseName,
    StartTime, 
    TextData
FROM sys.fn_trace_gettable(@fn, DEFAULT)  
WHERE EventClass = 115
AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

You could store the cleanups you've already done based on StartTime, and even limit the trace query that runs every minute (or whatever your acceptable delay threshold is) to only look at StartTime values greater than the last row you pulled, or the last time the job ran, whichever is less.

Sql-server – How to execute a stored procedure whose name is in a variable

You can use the variable directly:

exec @stored_procedure_name;

Please see the BOL Reference on EXECUTE.

Best Answer

Related Solutions

SQL Server – Automatically Execute Stored Procedure After RESTORE DATABASE Event

Sql-server – How to execute a stored procedure whose name is in a variable

Related Question