"could not find stored procedure"
I am maintaining a classic ASP website that has a SQL Server 2005 backend. For a small piece of new functionality I wrote a stored procedure to do an insert. This is the only user stored procedure in the database.
When I attempt to call the stored procedure from code I get the following error:
Microsoft OLE DB Provider for SQL Server error '80040e14' Could not find stored procedure 'InsertGroup'. /newGroup.asp, line 84
The DB uses SQL Server authentication. When I connect to the DB server in Visual Studio using the same user/pw as in the connection string the stored procedure is not visible but all tables are.
The user has datareader and datawriter roles and explicit execute permission on the stored procedure.
What am I missing?
UPDATE: My apologies, the server admin misinformed me that it was a 2000 server when it is actually a 2005 server (running on Windows Server 2003 x64).
Walk of shame:
The connection string was pointing at the live database. The error message was completely accurate - the stored procedure was only present in the dev DB. Thanks to all who provided excellent answers, and my apologies for wasting your time.
You may need to check who the actual owner of the stored procedure is. If it is a specific different user then that could be why you can't access it.
Sometimes this can also happen when you have a stored procedure being called with parameters. For example, if you type something like:
set @runProc = 'dbo.StoredProcedure' exec @runProc
This will work, However:
set @runProc = 'dbo.StoredProcedure ''foods''' exec @runProc
This will throw the error "could not find stored procedure dbo.StoredProcedure 'foods'", however this can easily be overcome with parantheses like so:
set @runProc = 'exec dbo.StoredProcedure ''foods''' exec (@runProc)
make sure that your schema name is in the connection string?
There are 2 causes:
1- store procedure name When you declare store procedure in code make sure you do not exec or execute keyword for example:
string sqlstr="sp_getAllcustomers";// right way to declare it. string sqlstr="execute sp_getAllCustomers";//wrong way and you will get that error message.
From this code:
MSDBHelp.ExecuteNonQuery(sqlconexec, CommandType.StoredProcedure, sqlexec);
CommandType.StoreProcedure will look for only store procedure name and
ExecuteNonQuery will execute the store procedure behind the scene.
2- connection string:
Another cause is the wrong connection string. Look inside the connection string and make sure you have the connection especially the database name and so on.
- You should probably mark this as the answer, to help others who may not read to the bottom. It's great that you posted this. I'm sure others have done and will do similar things, and an example of an oversight like this will probably help somebody else find his error.
- @Chloraphil: It's always funny to see that developers tend to think that the most probable explanation is not worth consideration, exploring everything else beforehand. :) +1 for providing the cause of the problem.
- I was Googling for a solution to that error and found this question, and it turns out I did the same thing, haha. 6 years later, you're still helping people ;) Thanks!
- This has happened to me several times and is quite annoying. Open the DB in SQL Server Management Studio if you can and look for 'dbo' or 'youruser' before the stored procedure. If it doesn't have 'dbo.', drop the existing procedure and recreate it with that as a prefix for the procedure name.
- Or, just mention the "owner" (="schema") the stored procedure belongs to as part of the call -- EXEC [dbo].[InsertGroup]
- calling it as dbo.InsertGroup or just InsertGroup fails
- Fails in what way? If it is an access denied error, GRANTing the user you are accessing the database with EXECUTE to dbo.InsertGroup would fix it.
- @Michael Haren - bro you just save my sleepless night!!! I am using Godaddy's Plesk Windows Hosting Account - and the way it generates it - is somehow inserts some username crap in the front of the stored procedure - instead of dbo, even though when I ran query I had it like dbo.StoredProcedureName. I had to drop and create it like you said - and now it works like a charm!
- can you be more specific? I'm not familiar with how to do that. Regardless, the database is only using the "dbo" schema.
- You may have two databases, and you think you are pointing to one database, but in actuality, you are pointing to another. Thus the StoredProc is on one database, but not the one you are running against. This can be a config error.