SQL Agent Job to pass NULL value

  • Hello. I have a stored procedure I need to run monthly. When run, the procedure looks for null values for two variables. I have created a job with only one step, to exec the stored procedure. How can I pass the null values so the procedure will run?

    Thanks in advance.

    Regards,

    R Reid-Smith

  • I think this is what you are asking for

    execute sp_procedure @var1 = null,@var2 = null

  • I will test that out ASAP tomorrow and let you know what happens. Thanks for your help.

    RRS

  • I actually had most of that correct in my job. The only thing I was missing was the 'sp_'. But my job still errors. If I run the stored procedure and check off the boxes to pass the null value for the two variables it runs fine. When I run the job it errors out but the history does not give me any info as to what is causing the error. Very irritating.

  • can you post what you have in you job so I can see what you are currently passing through?

  • Sure. I have a step in a job that runs 'execute sp_dbo.processname @var1 = null, @var2 = null

    The procedure, below, basically moves data from one place to another and sets nulls. I've changed the procedure, table, column, and variable names around so I hope I didn't make it unreadable:

    USE [database]

    GO

    /****** Object: StoredProcedure [dbo].[processname] Script Date: 08/28/2008 09:55:20 ******/

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ALTER PROCEDURE [dbo].[processname]

    @var1 int,

    @var2 int

    AS

    IF DatePart(day,GetDate()) = 8

    BEGIN

    IF @insert > 0

    BEGIN

    --PRINT 'INSERT'

    Insert Into Table1

    Select *, GetDate() as Column1

    From Table2

    END

    IF @update > 0

    BEGIN

    --PRINT 'UPDATE'

    Update Table2

    Set Column2 = NULL, Column3 = null

    END

    END

  • try thi in your job

    execute dbo.processname @var1 = null, @var2 = null

  • Here is what the error says:

    Executed as user: NT AUTHORITY\NETWORK SERVICE. Could not find stored procedure 'dbo.procedurename'. [SQLSTATE 42000] (Error 2812). The step failed.

    The procedure is there!! Do you think it may have something to do with the nt authorityetwork service and rights?

  • there is a dropdown in the job step to specify database. verify that the database displayed there is the database that contains the procedure.

  • I found the issue. I assure you when I setup this job the proper database was selected. I found when I opened the job and edited the step....it was set to run on the default 'master' db. Yes...I'm an idiot. 🙂

    It works fine now.

    Thanks for your help!!

    R Reid-Smith

  • no problem. glad you got it going.

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

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