Link to home
Start Free TrialLog in
Avatar of OzarkSunshine
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?
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

The problem is that you are passing parameters but the stored proc doesn't list any.  That is, you run this:

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.
Avatar of OzarkSunshine
OzarkSunshine

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_running sysname
 SET @job_name_to_check_if_running = "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_running) 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.
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.
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
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You were correct!  There was an embedded sp within the sp and that is where the true error was.  Thanks!