Stored Procedure doesn't exist, or does it?

Question

I'm having a problem:

I have a db connection where I run stored procedures on. This same connection is used to create said stored procedures earlier on.

When I attempt to call a given stored procedure, later on, I get the following message:

Could not find stored procedure 'dbo.yaf_prov_upgrade'.

The problem is it actually does exist on the database. And there's also the fact that it shows up on the SQL Server Profiler.

RPC:Completed exec [dbo].[yaf_prov_upgrade] @PreviousVersion=46,@NewVersion=46 .Net SqlClient Data Provider Nico Matrix\Nico

I was wondering what could be the causes a particular query would throw such an exception even when it exists, it's called, and the call reaches the database.

It can't be a problem with the connection because it already executed other stored procedures. It can't be a problem with the procedure because it does exist, in fact the very same application, the very same web page, created it and put it there.

Update: forgot to mention I'm used integrated security, and I did run the SP on the database with the same user the application connects with, and I had no problem running it.

So what can it be?

Answer 1

Your RPC completed only means that the batch submitted to SQL Server was correct and completed. It doesn't mean the stored procedure ran and executed OK.

It will be (don't argue, check) one of:

• wrong permissions
• wrong database context
• wrong server
• stored proc is in a different database

To ensure that things are the same

SELECT
   @@SERVERNAME, 
   SUSER_SNAME(), 
   DB_NAME(), 
   USER_NAME(), 
   OBJECT_ID('dbo.yaf_prov_upgrade')

The OBJECT_ID will be NULL if the stored proc doesn't exist in that database or you don't have permissions.

Answer 2

I suspect it might be a permissions issue, check up if the user name your program is executing under has execute rights to the stored proc.