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.

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.

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.

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

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.

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.

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.

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.

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

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.

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