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

User · Answer

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

User · Answer

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

User · Answer

make sure that your schema name is in the connection string

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I had   USE  wrong place   GO  before  DECLARE

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] "could not find stored procedure"

Examples related to sql-server

Examples related to stored-procedures