存储过程不存在，或者存在？

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'.

找不到存储过程'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.

问题是它确实存在于数据库中。而且还有它出现在SQL Server Profiler上的事实。

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

RPC:已完成exec [dbo]。[yaf_prov_upgrade] @ PreviousVersion = 46,@ NewVersion = 46 .Net SqlClient数据提供者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.

更新:忘了提到我使用了集成安全性,我确实使用应用程序连接的同一用户在数据库上运行SP,运行它没有问题。

So what can it be?

那又怎样呢?

3 个解决方案

#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.

RPC完成仅表示提交给SQL Server的批处理正确并已完成。这并不意味着存储过程运行并执行正常。

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.

如果存储过程在该数据库中不存在或您没有权限,则OBJECT_ID将为NULL。

#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.

我怀疑它可能是权限问题,检查程序正在执行的用户名是否具有对存储过程的执行权限。

#3

I'm no expert by far on ms-sql, but I do know it keeps SPs in a global cache. Is it possible the local connection only gets the global list of SPs upon connection? Maybe reinit the connection or re-select the cache?

到目前为止我在ms-sql上都不是专家,但我知道它将SP保存在全局缓存中。是否可能本地连接仅在连接时获取SP的全局列表?也许重新启动连接或重新选择缓存?

#1