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?
OzarkSunshineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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.
0
OzarkSunshineAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
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.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

OzarkSunshineAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
Maybe that proc is exec'ing another proc and passing a param when it shouldn't.  Something is passing in an unexpected param somewhere, or you wouldn't get that error.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OzarkSunshineAuthor Commented:
You were correct!  There was an embedded sp within the sp and that is where the true error was.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.