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

Drew

29.7k6 gold badges73 silver badges102 bronze badges

asked May 11, 2009 at 12:30

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.

answered May 11, 2009 at 15:21

ChloraphilChloraphil

2,6897 gold badges35 silver badges44 bronze badges

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.

answered May 11, 2009 at 12:32

Robin DayRobin Day

100k23 gold badges116 silver badges166 bronze badges

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)

answered Apr 18, 2013 at 1:05

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

answered May 11, 2009 at 13:28

DForck42DForck42

19.6k13 gold badges59 silver badges83 bronze badges

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:

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.

answered Sep 29, 2011 at 20:10

TalleyTalley

5615 silver badges6 bronze badges

I had:

USE [wrong_place]

before

DECLARE..

answered Jun 4, 2018 at 13:14

AJ AJAJ AJ

1892 silver badges12 bronze badges

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

Sunil Garg

14.3k25 gold badges131 silver badges186 bronze badges

answered Mar 16, 2017 at 10:36

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.

answered Jun 14, 2017 at 15:38

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.

answered May 13, 2019 at 8:41

Pati KPati K

1292 silver badges11 bronze badges

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

answered Dec 1, 2020 at 8:56

Источник

In this SQL Server tutorial, we will discuss how to solve the “Could not find stored procedure in SQL Server” error. Additionally, we will also try to resolve some of the related errors which are given below.

Could not find stored procedure in sql server
Could not find stored procedure ”. (microsoft sql server error 2812)
Could not find stored procedure ‘go’ sql server
Could not find stored procedure ‘sp_dboption’ sql server
Could not find stored procedure ‘sp_msforeachtable’ sql server

The error “Could not find stored procedure in SQL server” usually occurs when the SQL Server is unable to locate the specified procedure. The example related to this error is shown in the image below.

Could not find stored procedure in SQL Server

So, in this section, we will discuss some of the solutions that can help to overcome this error.

SQL Server stored procedure case statement

Fix-1: Refresh IntelliSense

If we are using SQL Server Management Studio, as a first solution, we can try to refresh the IntelliSense local cache.

For this implementation, click on the Edit option from the menu bar.
After this select “IntelliSense” and then, click on “Refresh Local Cache“. Alternatively, we can also use the “Ctrl + Shift + R” shortcut for the same.

Could not find stored procedure in sql server solution

Refresh Local Cache

Fix-2: Check the Existence of the stored procedure

This error usually occurs when SQL Server is unable to find the specified stored procedure in the database. So, as a precaution, we can check the existence of that stored procedure in the database.

Now, this process is very easy, while using SQL Server Management Studio.

For this, first, expand the database directory that you want to check.
After this, expand the “Programmability” directory. And then, right-click on “Stored Procedures” and click on “Refresh” to refresh the Object Explorer.

Could not find stored procedure microsoft sql server error 2812

Refresh in SQL Server Management Studio

Next, expand the “Stored Procedures” directory now, if the procedure exists in that database then, it will be visible under “Stored Procedures“.

Alter Stored Procedure in SQL Server

Fix-3: Permission to execute the procedure

Now, there can be one more reason behind this error while executing. The reason could be that we are using a role that does not have permission to execute the procedure.

In that case, we need to grant the EXECUTE permission to the role on that procedure.

For this implementation, we can use the following syntax.

GRANT EXECUTE ON OBJECT::dbo.YourProc TO YourRole;

Here is a simple example related to the above syntax. In the example, we are granting the EXECUTE permission of the “dbo.MaleStudents” stored procedure to the “DBA” role.

GRANT EXECUTE ON OBJECT:: dbo.MaleStudents TO DBA

Also, Read: Could not open a connection to sql server

Could not find stored procedure ‘go’ sql server

Now, in this section, we will discuss one more related error on “Could not find stored procedure“. This error states “Could not find stored procedure ‘GO’“. It simply means that the SQL Server could found the stored procedure with the name “GO“.

Could not find stored procedure ‘go’ in SQL Server

Now, the main reason behind this error could be the misuse of the “GO” statement.

In SQL Server, GO is a command which indicates the SQL Server utilities to send the current Transact-SQL batch to the SQL Server instance. All statements entered since the last GO are included in the current batch.

So, we should always use the GO command to specify the Transact-SQL batch for execution. And if we use this command in between at some wrong place then, this error may be encountered.

Read: SQL Server find text in stored procedure

Could not find stored procedure ‘sp_dboption’ sql server

In this section, we will learn how to resolve “could not find stored procedure ‘sp_dboption‘”. This error usually occurs when we try to execute the ‘sp_dboption‘ stored procedure.

Could not find stored procedure ‘sp_dboption‘

In SQL Server, sp_dboption is a system stored procedure that is used to display or modify database options. But, this procedure is removed from SQL Server 2012 or later versions.

So, if we try to use this stored procedure in SQL Server 2012 or later editions then, we might get this error.

But, don’t worry there is an alternative for this stored procedure in SQL Server. So, instead of using sp_dboption, we can use the “ALTER DATABASE” command to modify a database. Moreover, we can also use the “sp_replicationdboption” stored procedure if we want to modify the database options associated with replication.

Here is an alternative example for sp_dboption in SQL Server.

ALTER DATABASE [MockData] SET AUTO_SHRINK ON 

ALTER DATABASE [MockData] SET RECOVERY SIMPLE

In the above example, we have enabled the simple recovery mode for the MockData database. And, we have also enabled the auto shrink option that will shrink the data and log files for this database.

Could not find stored procedure sp_dboption

Alternative for sp_dboption in SQL Server

Read: Full-text search in SQL Server

Could not find stored procedure ‘sp_msforeachtable’ sql server

In this section, we will discuss how to resolve the “Could not find stored procedure ‘sp_msforeachtable‘” error. This error occurs only when the SQL Server is unable to find the ‘sp_msforeachtable‘ stored procedure. The complete error message is shown in the image below.

Could not find stored procedure ‘sp_msforeachtable‘

The sp_msforeachtable is a system stored procedure in SQL Server which executes the given statement for each table in the database. And the statement to execute is passed as a parameter to this procedure.

This is a system stored procedure so, it can execute from any database in SQL Server. So, this error usually occurs when your database collation is case-sensitive.

Because the exact name of this stored procedure is “sp_MSforeachtable” whereas we are using “sp_msforeachtable” at the time of execution.

So, the solution to overcome this is error is either by using the exact stored procedure name at the time of execution.

Or check and change the collation for your database from SQL_Latin1_General_CP1_CS_AS to SQL_Latin1_General_CP1_CI_AS. Where CS represents case-sensitive and CI represents case-insensitive.

Here are some of the steps that we can use to check and change the database collation property.

Fix-1: Use the correct procedure name

As discussed, the simple solution to overcome this error is by using the exact procedure name. The example for this implementation is shown below.

Use [sqlserverguides]  
exec sp_MSforeachtable 'print "?"'

The above example simply returns the name of all the tables that are in the sqlserverguides database. The output for this is shown below.

Could not find stored procedure ‘sp_msforeachtable‘ solution

Fix-2: Change the database collation

First, run the following query to check the collation for your database.

SELECT Name AS [Database Name], 
       collation_name as [Collation Name]
FROM sys.databases

Could not find stored procedure sp_msforeachtable

Database collation in SQL Server

If the collation for your database is set to the case-sensitive then, you need to change the collation property to case-insensitive.

ALTER DATABASE database 
   COLLATE SQL_Latin1_General_CP1_CI_AS

In the above query, we simply need to specify the name of the database for which we want to change that collation. The example for this is shown in the image below.

Could not find stored procedure ‘sp_msforeachtable‘ solution-2

You may also like to read the following articles on SQL Server.

What is a stored procedure in sql server
SQL Server stored procedure modified date
Try catch in SQL Server stored procedure
Rename stored procedure in SQL Server
SQL Server stored procedure parameters
Stored procedure in SQL Server for insert and update
Unable to retrieve data for this section of the report

In this tutorial, we have discussed how to solve the “Could not find stored procedure in SQL Server” error. Additionally, we also explained how to resolve some of the related errors which are given below.

Could not find stored procedure in sql server
Could not find stored procedure ”. (microsoft sql server error 2812)
Could not find stored procedure ‘go’ sql server
Could not find stored procedure ‘sp_dboption’ sql server
Could not find stored procedure ‘sp_msforeachtable’ sql server

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.

Источник

I have created a table testtable inside the database testbase that have the following structure:

product_no (int, not null)
product_name (varchar(30), not null)
price (money, null)
expire_date (date, null)
expire_time (time(7), null)

which I used the Microsoft SQL Server 2008 Management Studio.

I created a stored procedure testtable_pricesmaller as follows

use testbase
go
create procedure testtable_pricesmaller
    @pricelimit money
as
select * from testtable where price = @pricelimit;
go

and are able to view the Stored Procedures on the Object Explorer of the Microsoft SQL Server Management Studio. (It is listed in the following tree structure of the Object Explorer)

Databases
    + testbase
        + Tables
            + dbo.testtable
        + Programmability
            + Stored Procedures
                + dbo.testtable_pricesmaller

I find it very strange when I receive the following error:

Could not find the stored procedure 'dbo.testtable_pricesmaller'.

when I execute the following SQL statement:

execute dbo.testtable_pricesmaller 50

What could it be missing?

asked Sep 10, 2012 at 4:38

IntelliSense Refresh local Cache should fix it

answered Apr 18, 2015 at 21:58

user63684user63684

3813 silver badges2 bronze badges

You should not have to restart the database after adding a new stored procedure, although you will need to refresh your object explorer to see it there.

The next time you add a stored procedure try running the right click execute option from the object explorer and enter your parameters and see if it runs. If it does not run then I’m not sure what your problem is. If it does run then it could be something simple like SQL is trying to query from the wrong database.

Tom V

15.6k7 gold badges62 silver badges86 bronze badges

answered Sep 10, 2012 at 15:44

ZaneZane

3,5013 gold badges23 silver badges45 bronze badges

At last I know why the message appear in the MS SQL Server Management Studio.

The MS SQL Server Management Studio require one to restart it after creating a stored procedure in it.

After restarting the MS SQL Server Management Studio, there is no such error anymore.

(Strange, does that mean that every time I create a stored procedure, I have to restart it?)

answered Sep 10, 2012 at 5:45

JackJack

2,44914 gold badges35 silver badges42 bronze badges

In SQL Server 2008, when logged in under a Windows account, if you don’t have SYSADMIN security level, when you create an object without explicitly specifying the schema, it may/will create it under the [DOMAINusername].[ObjectName] instead of [dbo].[ObjectName] (it was fixed in SQL Server 2012 I think).

I had this problem happen to me when I reduced a user’ security level, and one of the procedure he was executing was dropping and recreating tables without a schema, so the rest of the procedure was crashing because it couldn’t access the object again. Turns out the tables were now created under his domain username.

Here is the Microsoft post about this behavior:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-2017 (look for the section «Implicit Schema and User Creation»)

Table not getting created under dbo schema

SQL 2008 R2 creates user/schema when Windows user creates tables

So, in short, you probably either have a database problem (you create your table in a database but try to access it from another one) or you have the problem like I just described.

Paul White♦

80.3k28 gold badges396 silver badges618 bronze badges

answered Feb 20, 2019 at 13:46

PhilippePhilippe

4976 silver badges14 bronze badges

Your create command should be

create procedure dbo.testtable_pricesmaller
    @pricelimit money

you are missing dbo. before procedure name. Whenever you create a procedure, it is good practice to explicitly define the user/schema with the name of a procedure i.e. procedure name should have fully qualified signatures.

I hope this will help you.

dezso

30.4k13 gold badges98 silver badges143 bronze badges

answered Jul 12, 2013 at 3:21

I know this is old; I came across this question while I was searching for a solution to this very same problem, and I’m posting this answer in the hope that it helps others who also find this question.

In my case, I got the error message while running an SSRS report using a shared data source. This shared data source did not specify a default database (the Default Catalog= parameter), and I couldn’t add it to the connection string because I don’t have the password (and when you change something in an SSRS data source it tends to want you to re-enter the password).

To solve this, I changed the default database for the login in the SQL Server instance from master to the database containing the stored procedure the report wanted to execute.

When running things from SSMS, keep in mind the Object Explorer pane is one connection while whatever editor you have is an entirely different connection. So you may see the objects for SQL01 in Object Explorer, but the code you’re running in an editor will run against SQL02 — I’ve run into this problem a few times over the years and after much cussing and «Why won’t it work?» realized my mistake. For the editor, look in the lower right corner to see which instance and database you’re connected to.

answered Sep 7, 2017 at 20:40

TL;DR: You may have a stored procedure that’s calling another stored procedure that doesn’t exist.

I had this problem and found a fix. Here’s what happened. I created one stored procedure:

create procedure dbo.MyProc
    ...

I then created another stored procedure that executed the first one

create procedure dbo.MyProcCaller
    ...
    exec dbo.MyProc
    ...

Some time later, I renamed dbo.MyProc to dbo.MyProc2. After renaming it, when I tried to call dbo.MyProcCaller, I’d get this error message:

exec dbo.MyProcCaller

Could not find stored procedure ‘RLM.usp_getSecondaryRestrictedLists_Old’.

My solution was to alter my second stored procedure to use the new name:

create procedure dbo.MyProcCaller
    ...
    exec dbo.MyProc2
    ...

Here’s a simple way to check if you have this problem. Click to modify the text of the stored procedure and then execute that text. If you get a warning like this, you need to rename your stored procedure:

The module ‘dbo.MyProcCaller’ depends on the missing object ‘dbo.MyProc’. The module will still be created; however, it cannot run successfully until the object exists.

(1 row(s) affected)

answered Oct 12, 2016 at 15:54

user2023861user2023861

1591 gold badge1 silver badge7 bronze badges

This question is a few years old, but I just want to throw in another possibility for anyone like me who found it later.

I ran this command:
EXEC SP_CONFIGURE ‘Agent XPs’

And got the error described:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘SP_CONFIGURE’.

But then I remembered that this server is set up to be case-sensitive. So this command worked just fine:
EXEC sp_configure ‘Agent XPs’

HTH

answered Sep 30, 2019 at 14:08

jrdevdbajrdevdba

2782 silver badges7 bronze badges

In a similar vain, I got this error calling from ASP.NET VB code behind and it was due to an extra space character in front of the stored procedure name e.g. » dbo.mysproc» instead of «dbo.mysproc».

answered Nov 19, 2020 at 15:37

ZeekZeek

4114 silver badges4 bronze badges

@Zane’s answer is the best answer. I will also add … For those who are in Azure Data Studio, you can clear the cache by CTRL<CMD>-SHIFT-P and typing intellisense in the command palate. You will see an option to refresh the cache there. source

answered Mar 5, 2021 at 15:57

Источник

User868845042 posted

I’m trying to add and execute a stored procedure from VB.NET code. I’m using VB.NET in Visual Studio 2010 in an ASP.NET project. I’m using the Northwind database.

I get the error above — Could not find stored procedure — when I execute the stored procedure in VB.NET code.

* Using the Server Explorer, I can connect to the database and execute the query successfuly.

* Using the Sql Server Management Studio, I can also execute the query successfully.

* I can add a stored procedure using either Visual Studio or Sql Server Management Studio and it show up in the other tool after clicking Refresh.

* There are prebuilt stored procedures in Northwind, such as «Ten Most Expensive Products» and I can execute them using the same code in my VB.NET project.

Here’s the stored procedure that I can’t execute (copied and pasted from Visual Studio 2010):

ALTER PROCEDURE «CountEmployees» AS
SELECT COUNT(EmployeeID) FROM Employees

Here’s the VB Code (the stored procedure that does work is commented out — I don’t get robust results from the «Ten Most Expensive Products» stored procedure, but I get enough to tell that it’s running.)

        Dim empCnt As Integer
        Dim objResult As Object
        Dim connectionString As String = «Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI»
        Dim con As New SqlConnection(connectionString)
        Try
            con.Open()
            Response.Write(con.State.ToString())
            Dim cmd As New SqlCommand(«CountEmployees», con)
            ‘Dim cmd As New SqlCommand(«Ten Most Expensive Products», con)
            cmd.CommandType = CommandType.StoredProcedure
            empCnt = CInt(cmd.ExecuteScalar())
            ‘objResult = cmd.ExecuteScalar()
            Response.Write(empCnt)
            ‘Response.Write(objResult)
        Finally
            con.Close()
        End Try

Why can’t the other procedure be found????

Источник

Remove From My Forums

Вопрос
Good Morning,

I have a scheduled job, which has 5 steps for 5 cities.Recently we added a new city and The command is

EXECUTE dbo.usp_DailyProcessing @cityAbbrev = ‘LA’;

The scheduled job has 5 steps , one for each city. When executed from the sql server agent only the last step for the new city fails, with the following error

Executed as user: ceconsvc_SQLServerAgent. Could not find stored procedure ‘dbo.usp_DailyProcessing’. [SQLSTATE 42000] (Error 2812). The step failed.

But when i execute from SSMS it updates the records. Please suggest an idea.

Thanks

SV

Ответы

Make sure the database context of the newly added T-SQL step has the correct database specified.

Dan Guzman, Data Platform MVP, http://www.dbdelta.com
- Помечено в качестве ответа
  
  11 октября 2017 г. 17:11
Can you script out your job to check the difference of each step in your job?
- Помечено в качестве ответа
  saivenkat77
  11 октября 2017 г. 17:12
> Default database chosen could different from the database containing the procedure

Use fully qualified name and see.

Please use Marked as Answer if my post solved your problem and use
Vote As Helpful if a post was useful.
- Помечено в качестве ответа
  saivenkat77
  11 октября 2017 г. 17:11
Checked and i have the

USE [MAINDB]

GO

before the Execute

Thanks

SV
- Помечено в качестве ответа
  saivenkat77
  11 октября 2017 г. 17:11
I would guess the sp name is mispelled, if you’re 100% certain that the jobstep is in the correct database context before executing sp.

Even an extra non-printing character (like extra space) in the execution string could cause a difference from the real sp name that will cause it to error. Try copy-pasting the execute statement again from one of the other jobsteps and see if that resolves.

HTH,

Phil Streiff, MCDBA, MCITP, MCSA
- Изменено
  philfactor
  10 октября 2017 г. 17:37
- Предложено в качестве ответа
  Xi Jin
  11 октября 2017 г. 1:36
- Помечено в качестве ответа
  saivenkat77
  11 октября 2017 г. 17:11
SV,

Please mark answers that help you, to close this thread.

Thanks,

Phil Streiff, MCDBA, MCITP, MCSA
- Помечено в качестве ответа
  saivenkat77
  11 октября 2017 г. 17:11

Источник

Fix-1: Refresh IntelliSense

Fix-2: Check the Existence of the stored procedure

Fix-3: Permission to execute the procedure

Could not find stored procedure ‘go’ sql server

Could not find stored procedure ‘sp_dboption’ sql server

Could not find stored procedure ‘sp_msforeachtable’ sql server

Fix-1: Use the correct procedure name

Fix-2: Change the database collation

Вопрос

Ответы