Execute stored procedure A and systems is running stored procedure B????

Question

Execute stored procedure A and systems is running stored procedure B????

maring.richard

SSC Rookie

Points: 27
More actions
October 3, 2016 at 1:44 pm

#330977

Running MSSQL 2008/R2 on a Windowes 2000 R@ server.
I ran the following from MSSQL the management studio:
use production
exec proc_InsertDocumentLink 700074, 208, 1025, 1021, 95707
go
and the message that returned was the following:
Msg 8144, Level 16, State 2, Procedure procRecurringApptsCreate, Line 0
Procedure or function procRecurringApptsCreate has too many arguments specified.
The statement has been terminated.
Have restarted MSSQL and also the entire server and I keep getting same message.
Cannot find anything doing a Google search that comes close to addressing this. Please help, as this is a production server.
What could be causing MSSQL to try and execute a different stored procedure rather than the one querried?
Thank you
Rich

Viewing 7 posts - 1 through 6 (of 6 total)

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

Eric M Russell SSC Guru Points: 125526 More actions · Answer 1

Perhaps the procedure you are executing is calling the other procedure. Look at the T-SQL contents to confirm. That would be my first guess.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

maring.richard SSC Rookie Points: 27 More actions · Answer 2

Thank you for the quick response Eric.

I have checked the procedure and it has nothing in common with the procedure it's actually trying to run.

Both of these procedures were running without an issue. Both have been in production for over 3 years.

I'm not that familiar with the inner workings of how MSSQL keeps track of stored procedures and their associated names, but it seems like somehow it is seeing 'proc_InsertDocumentLink' in the query and pointing it to 'procRecurringApptsCreate'.

Somewhere their is a connection or association in stored procedures that has become corrupted.

I just don't know how to find out if that is the case or how to rebuild the connections.

Thank you.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Never seen MS SQL Server run a different procedure than the one invoked in an EXEC statement.

Look at the stored procedure you are executing, is it calling any other procedures? Also look to see if there are any synonyms pointing to the procedure being run.

If all else fails, try dropping and then recreating both procedures making sure to the appropriate scripts for each from your source control system.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 4

Could there be a trigger causing this?

Or maybe a synonym?

The Redneck DBA

Steve Jones - SSC Editor SSC Guru Points: 734615 More actions · Answer 5

There must be a call to the procRecurringApptsCreate procedure somewhere.

Does proc_InsertDocumentLink call anything else? Any other procs/functions/assemblies? Computed columns? triggers?

There's likely a link somewhere. You can use trace or XE to catch the statements being called here and in what order. I'd look at XE and use a specific login so you can filter and track what's happening.

maring.richard SSC Rookie Points: 27 More actions · Answer 6

Thank you so much.

Yes, I found a trigger on the Assignments table that called 'procRecurringApptsCreate' without testing to see if the insert was not coming from the 'Documents' procedures and not the 'Appointments' procedures. Added a test for the actual record type and all is working again.

Thank you so much for your help.