0

I have created a stored procedure in my SQL Server Express that I am calling my Web API in and it is running fine. I want it to play in the background like a job / schedule using waitfor time in SQL Server to auto start it at startup of master.

Stored procedure:

USE [master]

CREATE PROCEDURE [dbo].[MyBackgroundTask]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @timeToRun NVARCHAR(50)
    SET @timeToRun = 'time'

    WHILE 1 = 1
    BEGIN
        WAITFOR time @timeToRun
        BEGIN
            EXECUTE [master].[dbo].[CALLWEBSERVICE];
        END
    END
END

To start with the master startup I have used sp_procoption:

sp_procoption    @ProcName = 'MyBackgroundTask',
                @OptionName = 'startup',
                @OptionValue = 'on'
GO

I have also used this query to get the list of routine task running in background:

SELECT ROUTINE_NAME
FROM MASTER.INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

I have also got it running in background but how can I check if it is really working in background how can I check the result I got in background running stored procedure?

I tried SQL Server Express Profiler but not helpful it is not showing background procedures in it.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Mahavirsinh Padhiyar
  • 1,299
  • 11
  • 33
  • Why not create a sql job that simply runs your procedure. Then put that job on a schedule. What you have created here is an endless loop. – Sean Lange Mar 30 '18 at 13:35
  • @SeanLange yes you are right but I have SQL Express installed and don't have SQL Agent in it as it is available in Enterprise addition so can't create job in Express that's why I have put it running endless that is how I have scheduled it by my own call / method. – Mahavirsinh Padhiyar Mar 30 '18 at 13:37
  • Or download SQL Server Developer Edition if you are not planning on using your code to generate revenue or for commercial purposes. That has the job agent. – dfundako Mar 30 '18 at 13:38
  • 2
    Doing this in an endless loop is a clear sign that something is not right. You could always create an application that calls your procedure and set it up to run as a windows task. That is still rather kludgey but way better than an endless loop. – Sean Lange Mar 30 '18 at 13:40
  • I agree with @SeanLange, Good day to you sir. – Ryan Wilson Mar 30 '18 at 13:41
  • @dfundako as of now I can only use sql express. – Mahavirsinh Padhiyar Mar 30 '18 at 13:42
  • sp_whoisactive/sp_who2 would show the open spid running your endless process. – dfundako Mar 30 '18 at 13:44
  • 1
    @MahavirsinhPadhiyar This question is a possible duplicate of (https://stackoverflow.com/questions/7201061/how-to-create-jobs-in-sql-server-express-edition), please see the post I added, it gives 3rd party software extensions for use with Express to do what SQL Agent does, it also shows how to create a batch file to run your stored procedure and then set that up as a windows service, as Sean suggested. – Ryan Wilson Mar 30 '18 at 13:46

0 Answers0