Stored Procedure

Question

Hi How do i mark the stored procedure to automatic execution ?

Jignesh Trivedi · Answer

hi there are many way to implement auto excesute SP but all are depending upon the Project requirement. 1) SP executed every time SQL Server starts.his is useful if you have operations that you want to perform regularly or if you have a stored procedure that runs as a background process and is expected to be running at all times. for this please refer. http://msdn.microsoft.com/en-us/library/ms191129%28v=sql.105%29.aspx http://www.mssqltips.com/sqlservertip/1574/automatically-running-stored-procedures-at-sql-server-startup/ 2) SP execute automatic when user Enter data in some table so this use trigger execute SP with in trigger. 3) SP execute after perticular time span the use SQL Batch Job. please refer http://msdn.microsoft.com/en-us/library/ms181153.aspx http://msdn.microsoft.com/en-us/library/ms187910.aspx hope this help.

Senthilkumar · Answer

Hi shubham The SQL server stored procedure can run independently but can't run automatically. We need to schedule the jobs with the specific requirement like needs to run every day in the particular interval or weekly basis or monthly basis. All you need to do is just set up a new job with s step TSQL execution. You can schedule that like any other jon in SQL Server. EXEC SPSomeProcedure Please follow this article to create the job and schedule this stored procedure to run automatically. http://www.mssqltips.com/sqlservertip/2223/how-to-setup-and-manage-sql-server-agent-shared-job-schedules/

Satyapriya Nayak · Answer

Hi Shubham You can use the sp_procoption system stored procedure to mark the stored procedure to automatic execution when the SQL Server will start. Only objects in the master database owned by DBO can have the startup setting changed and this option is restricted to objects that have no parameters. Syntax: sp_procoption [ @ProcName = ] 'procedure'  [ @OptionName = ] 'option'  [ @OptionValue = ] 'value' Sample Code: USE master EXEC sp_procoption 'ClearTempFiles' 'startup' 'true') Thanks

Mahak Gupta · Answer

Read this Article: http://msdn.microsoft.com/en-us/library/ms191129(v=sql.105).aspx