SQL Agent Job to pass NULL value

Question

Post reply

SQL Agent Job to pass NULL value

rsmiff

Mr or Mrs. 500

Points: 554
More actions
August 27, 2008 at 12:40 pm

#124995

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

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

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

I think this is what you are asking for

execute sp_procedure @var1 = null,@var2 = null

rsmiff Mr or Mrs. 500 Points: 554 More actions · Answer 2

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

RRS

rsmiff Mr or Mrs. 500 Points: 554 More actions · Answer 3

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.

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 4

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

rsmiff Mr or Mrs. 500 Points: 554 More actions · Answer 5

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 6

try thi in your job

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

rsmiff Mr or Mrs. 500 Points: 554 More actions · Answer 7

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?

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 8

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

rsmiff Mr or Mrs. 500 Points: 554 More actions · Answer 9

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 10

Adam Angelini

SSCrazy Eights

Points: 8548

August 28, 2008 at 8:35 am

#864444

no problem. glad you got it going.