Tutorial :“could not find stored procedure”



Question:

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).


Solution:1

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.


Solution:2

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.


Solution:3

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)  


Solution:4

make sure that your schema name is in the connection string?


Solution:5

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:

C#

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.


Solution:6

Could not find stored procedure?---- means when you get this.. our code like this

String sp="{call GetUnitReferenceMap}";    stmt=conn.prepareCall(sp);    ResultSet rs = stmt.executeQuery();    while (rs.next()) {    currencyMap.put(rs.getString(1).trim(), rs.getString(2).trim());   

I have 4 DBs(sample1, sample2, sample3) But stmt will search location is master Default DB then we will get Exception.

we should provide DB name then problem resolves::

String sp="{call sample1..GetUnitReferenceMap}";  


Solution:7

One more possibility to check. Listing here because it just happened to me and wasn't mentioned;-)

I had accidentally added a space character on the end of the name. Many hours of trying things before I finally noticed it. It's always something simple after you figure it out.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »