Could not find stored procedure in SQL Server

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

Could not find stored procedure in 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
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.

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

Fix-3: Permission to execute the procedure

Now, there can be one more reason behind this error while execution. The reason could be when 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 sql server
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 proceduresp_dboption‘”. This error usually occurs when we try to execute the ‘sp_dboption‘ stored procedure.

Could not find stored procedure sp_dboption sql server
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 sql server
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
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
Could not find stored procedure ‘sp_msforeachtable‘ solution-2

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

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