CODEWITHSUNDEEP
sql-serverstored-procedures
Chloraphil
Chloraphil

Reputation: 2849

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

Upvotes: 27

Views: 259325

Answers (12)

BHP
BHP

Reputation: 526

Assuming you are using C# and ADO.NET, It can happen if you don't set SqlCommand.CommandType to CommandType.Text.

Upvotes: 0

adnan Alyaari
adnan Alyaari

Reputation: 61

My answer according to the problem and what is causing it for me when i called stored procedure just there is a space before the stored procedure name. Sometimes it just takes checking the simplest needs

 datatabel = dataAccessLayer.ReadDataFromDB(" Get_All_Customers",null);

when i deleted the space before Get_All_Customers it's work finely

Upvotes: 0

somoria
somoria

Reputation: 151

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

Upvotes: 0

Pati K
Pati K

Reputation: 139

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.

Upvotes: 0

AJ AJ
AJ AJ

Reputation: 325

I had:

USE [wrong_place]

GO

before

DECLARE..

Upvotes: 1

Ken.Shipman
Ken.Shipman

Reputation: 1

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.

Upvotes: 0

Rama Krishna
Rama Krishna

Reputation: 11

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

Upvotes: 0

prozilla
prozilla

Reputation: 61

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)

Upvotes: 6

Talley
Talley

Reputation: 561

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.

Upvotes: 2

Chloraphil
Chloraphil

Reputation: 2849

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.

Upvotes: 86

DForck42
DForck42

Reputation: 20387

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

Upvotes: 2

Robin Day
Robin Day

Reputation: 102578

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.

Upvotes: 11

Related Questions