[sql-server] "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).

I had the same problem. Eventually I found why. I used a code from web to test output of my procedure. At the end it had a call to Drop(procedure) so I deleted it myself.

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.

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.

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

I had:

USE [wrong_place]

GO

before

DECLARE..

If the error message only occurs locally, try opening the sql file and press the play button.

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)

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.

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}";