OzarkSunshine
asked on
Error with variables in SQL Job
I have a SQL job that assigns variable in the first step to check status of another job. If job not running, it continues to next step. That step executes a stored procedure that does not declare variables as part of the definition. But I'm getting this error when it gets to that step.
Procedure has no parameters and arguments were supplied. [SQLSTATE 42000] (Error 8146). The step failed.
I didn't think SQL job carried variables from one step to another, but is there a way to clear before proceeding to next step?
Procedure has no parameters and arguments were supplied. [SQLSTATE 42000] (Error 8146). The step failed.
I didn't think SQL job carried variables from one step to another, but is there a way to clear before proceeding to next step?
ASKER
I'm only passing this through the job step --
EXEC sp_proc1
I thought because there were variables being declared in the 1st job step that possibly they are flowing through to this job step as well. (Which doesn't seem viable to me, but only thing that comes to mind)
The 1st job step runs this --
DECLARE @job_name_to_check_if_runn ing sysname
SET @job_name_to_check_if_runn ing = "Job Name"
IF EXISTS(
SELECT 1
FROM msdb.dbo.sysjobactivity ja
WHERE
ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions) AND
ja.job_id = (SELECT TOP (1) j.job_id FROM msdb.dbo.sysjobs j WHERE j.name = @job_name_to_check_if_runn ing) AND
start_execution_date IS NOT NULL AND stop_execution_date IS NULL
)
BEGIN
RAISERROR('Force this step to end now, which forces job to end also.', 18, 1)
END
Upon success, it goes to job step # 2 which is simply --
EXEC sp_proc1
No variables or additional characters follow that command.
EXEC sp_proc1
I thought because there were variables being declared in the 1st job step that possibly they are flowing through to this job step as well. (Which doesn't seem viable to me, but only thing that comes to mind)
The 1st job step runs this --
DECLARE @job_name_to_check_if_runn
SET @job_name_to_check_if_runn
IF EXISTS(
SELECT 1
FROM msdb.dbo.sysjobactivity ja
WHERE
ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions) AND
ja.job_id = (SELECT TOP (1) j.job_id FROM msdb.dbo.sysjobs j WHERE j.name = @job_name_to_check_if_runn
start_execution_date IS NOT NULL AND stop_execution_date IS NULL
)
BEGIN
RAISERROR('Force this step to end now, which forces job to end also.', 18, 1)
END
Upon success, it goes to job step # 2 which is simply --
EXEC sp_proc1
No variables or additional characters follow that command.
Verify that you are running the exact proc you expect to run. If it starts with "sp_", check the master db. And make sure you don't have different versions of the proc under different schemas.
ASKER
Good point, but not issue. I'm also using database.dbo before stored procedure name and have that step set to use the correct db.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You were correct! There was an embedded sp within the sp and that is where the true error was. Thanks!
EXEC sp_proc1 1
or
EXEC sp_proc1 @param1
But the stored proc is created as:
CREATE PROCEDURE sp_proc1
AS
...
without any params. You can't pass params to the proc unless it can receive them. You need to run this:
EXEC sp_proc1
with no param specified.