StoredProcedure has too many arguments specified

I've renamed some stored procedures and every control associated with them, because I want to apply a similiar system to a replicate project (of add/edit/delete records). However, strangest thing, this error is rendered whenever I add a record: "cg_AddTitle has too many arguments specified". But the record in fact get added. Moreover, everything else--add/edit/delete--work fine without error. Any clue why this might be happening?
John AccountAsked:
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.

Maverick_CoolCommented:
can u put code and sproc signature
0
maradamCommented:
I think I understand your problem. I have applied the worst practice, becuse you renamed procedural object with sp_rename. It can lead to such errors. look at the code below. I will try to comment why it works in such a bad way.

The conclusion. NEVER use sp_rename against procedural objects, because it almost always lead to very difficoult errors to manage. Sometimes the signature doesn't change and the fix of functionality is minor and hard to discover. Much better way to backup a stored procedure is to drop it and create under new backup name.
create database temp
go
use temp
GO
create table t(a int null, b int null, c int null)
GO
create proc insert_t (@a int, @b int) -- proc with 2 params
as
insert t(a,b) select @a, @b
GO
sp_rename insert_t, insert_t1 --WRONG! Although the name in sysobjects
-- is insert_t1, the body still points to insert_t
GO
-- proc with old name and 3 params
create proc insert_t (@a int, @b int, @c int)
as
insert t(a,b,c) select (a,b,c)
GO
-- test. will work for now
exec insert_t 1,2,3
GO
exec sp_refreshsqlmodule insert_t1 -- body still points to insert_t
-- problems are comming ;)
GO
exec insert_t 1,2,3 -- will be an error here (too many parameters)
GO
sp_refreshsqlmodule insert_t -- will not fix the problem
GO
-- this will fix
GO
alter proc insert_t (@a int, @b int, @c int)
as
insert t(a,b,c) select (a,b,c)
GO
insert_t 1,2,3 -- will work until you refresh insert_t1

Open in new window

0
John AccountAuthor Commented:
You can download the project here: http://www.versacore.info/Dataentry.zip
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

John AccountAuthor Commented:
Oh, no-no, maradam. I didn't rename it like that. What I did was go into the Stored Procedures folder, then double-clicking a SP, then where it says ALTER PROCEDURE [dbo].[cg_AddTitle]  and replaced the world ALTER with CREATE and replaced the "[dbo].[cg_AddTitle]" with something new like "[dbo].[cg_AddNEWTitle]" for instance. Then re-refereneced all the controls to the new SP--ie, in this case would be "cg_AddNEWTitle".
0
maradamCommented:
Are you sure that you did not use rename option in popup menu at the list of stored procedures? Maybe by mistake. The database you shared does not have cg_AddNEWTitle procedure and nothing like that in porcedure texts. There are also no inconsistencies between object_names and their definitions. Maybe its stage is before your change. Or maybe only client application is using another procedure version (and name) that the one you think. The easyiest way is to check it with profiler.
0
John AccountAuthor Commented:
cg_AddNEWTitle was an example I showed you while responding. cg_AddTitle is actually the new name--it was previously named something else. So forget about cg_AddNEWTitle--that's completely irrelevant, so there will be any consisitencies. Everythign else should be consistent though, and therein is the problem.
0
John AccountAuthor Commented:
The problem being: if, everythign is consistent, why is there a problem? Why the error msg when I attempt to add a record?
0
maradamCommented:
The only way for me is to check with Profiler what's going on. Add SQL:BatchStarting, SQL:StatementStarting, RPC:Starting, Exception and try do get your exception step by step. Does the problem occur on every run?
0
John AccountAuthor Commented:
I don't know a thing about the Profiler, and adding SQL:BatchStarting, SQL:StatementStarting, RPC:Starting, etc. Exceptions. I'll have to research and learn that first, then get back to you. Sorry, my bad.  I probably should not have put intermediate in the subject of this question.
0
Maverick_CoolCommented:
just type profiler in strat-> run, profiler will pop-up, for MSSqlServer 2k5 type profiler90
Go New->new Trace, trace select dlg will come, go to event selection tab and select batchStarting,.. RPC started fot TEXtData Field and run trace after that do your opertion in propgram ui and see the trace and search for sproc, u will have all the details
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
John AccountAuthor Commented:
I've tried profiler and profiler90, but I just get the error message, "Windows cannot find 'Profiler'.
0
John AccountAuthor Commented:
I gave up trying to use stored procedures, and have thus abandoned all efforts to try to learn this--at least for now. Too many bigger fish to fry. So, now I ask this question: Would you like me to split the points with a grade "C" (average) rating, or request a refund?
0
John AccountAuthor Commented:
Oh...and, by the way, many thanks for trying to help. Greatly appreciated!
0
Maverick_CoolCommented:
did the solution work 4 u!
check the screen shot below, to find the profiler
Profiler.JPG
0
Maverick_CoolCommented:
if its sqk2k5
0
John AccountAuthor Commented:
Thanks, but it's ms sql express.
0
Maverick_CoolCommented:
ok.
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
ASP.NET

From novice to tech pro — start learning today.