Share via

Error message stating invalid object name 'Store Procedure' when store procedure is present

Sylvia Rivera 21 Reputation points
2023-10-30T16:02:17.4166667+00:00

Since upgrading to SQL2022 we have had an issue with one of our store procedures that communicates with our website where we receive an error message stating invalid object name 'Store Procedure'. The store procedure is there and we have to drop it and recreate it every time. We have tried to update the store procedure, recreate it and we still receive the error a couple times a day. We are currently on SQL 2022 CU7. Has anyone encounter this issue? How do I resolve it?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,367 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2023-10-30T22:25:09.9866667+00:00

    You say that the procedure communicates with your web site. I assume that the web site is calling this stored procedure. Or is the stored procedure invoking a REST service?

    You say that the stored procedure is there. When you get this error message, do you run

    SELECT * FROM sys.procedures WHERE name = 'YourSP'

    If it is there, what is the value of schema_id? Does it match the schema that the web site uses?

    My guess is that there is something that changes this procedure, possibly moves it to a different schema. You could create a DDL trigger that logs all DDL events to a table, so see if you find something this way.

  2. Erland Sommarskog 107.2K Reputation points
    2023-10-31T22:30:38.04+00:00

    So I don't know what is going on, but I can see that this is a case odd enough to slip through the cracks. Also, I don't have access on a repro, and you don't seem to keen on sharing that much detail in a public forum. That is understandable, but that also makes it more difficult to assist.

    Here are three ideas:

    1. Use a table variable instead of a temp table.
    2. Add the hint OPTION (KEEPFIXED PLAN)
    3. Open a support case. Assuming that this is a bug in the product, that case you be free of charge of you in the end.

    The two first points are shots in the dark based on the idea that the issue is related to recompilation.

    By the way, do you have Query Store enabled for the database?