STORED PROCEDURE DOESN'T EXIST, OR DOES IT?

Ankita Pandey

Total Post:183

Points:1285
Posted by  Ankita Pandey
C# 
C#
 1290  View(s)
Ratings:
Rate this:

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?

  1. Sumit Kesarwani

    Post:378

    Points:2694
    Re: Stored Procedure doesn't exist, or does it?

    Hi Ankita,


    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

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!