August 27, 2008 at 12:40 pm
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
August 27, 2008 at 3:20 pm
I think this is what you are asking for
execute sp_procedure @var1 = null,@var2 = null
August 27, 2008 at 6:26 pm
I will test that out ASAP tomorrow and let you know what happens. Thanks for your help.
RRS
August 28, 2008 at 6:41 am
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.
August 28, 2008 at 7:40 am
can you post what you have in you job so I can see what you are currently passing through?
August 28, 2008 at 8:01 am
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
August 28, 2008 at 8:06 am
try thi in your job
execute dbo.processname @var1 = null, @var2 = null
August 28, 2008 at 8:28 am
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?
August 28, 2008 at 8:30 am
there is a dropdown in the job step to specify database. verify that the database displayed there is the database that contains the procedure.
August 28, 2008 at 8:32 am
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
August 28, 2008 at 8:35 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy