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

Question

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

Sylvia Rivera 21

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?

0 comments

2 answers

Your answer

Answer 1

Erland Sommarskog 134.3K MVP Volunteer Moderator

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:

Use a table variable instead of a temp table.
Add the hint OPTION (KEEPFIXED PLAN)
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?

Sylvia Rivera 21 Reputation points

2023-11-08T01:25:00.27+00:00

Hello Erland,

It has been over a week since we updated MASKED WITH (FUNCTION = 'partial ....') to use STUFF and we have not seen the error any more. Maybe there was an issue with applying Dynamic Data Masking on a create #TempTable statement.

I do have Query Store enabled.

Thank you so much for your ideas and your time.
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2023-11-08T22:28:59.3466667+00:00

So you are no longer using Dynamic Data Masking for your temp table?

I asked about Query Store, since there is another odd case where people are getting spurious syntax errors, and where we have tracked down as much as turning off Query Store stops that error from coming. Of course, Query Store is a marvellous asset, so we don't like turning off. But it can help to troubleshoot what is happening.

But if you are no longer using DDM, I guess this means that the case is closed.
Sylvia Rivera 21 Reputation points

2023-11-09T22:14:57.98+00:00

Hello Erland,

Correct, we are no longer using Dynamic Data Masking on our temp table.

Thank you so much for your time and input.

Answer 2

Erland Sommarskog 134.3K MVP Volunteer Moderator

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.

Sylvia Rivera 21 Reputation points

2023-10-31T17:41:07.09+00:00

Thank you for your response Erland. I saw that the sproc with the issue was using Dynamic data masking and replaced that yesterday. I have not seen the issue today to test your suggestion. Do you know if any issues with SQL 2022 and Dynamic data masking? I will test your suggestion if the issue reappears.
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2023-10-31T21:43:05.67+00:00

There were some enhancements with Dynamic Data Masking in SQL 2022, particularly, you can grant masking permission per table. But that should not affect stored procedures.

I'm not sure what you mean with that stored procedure is using Dynamic Data Masking as that is a property you apply to a table column. Possibly, you mean that the procedure is reading tables with masked columns.
Sylvia Rivera 21 Reputation points

2023-10-31T22:02:48.6433333+00:00

Sorry, yes Dynamic Data Masking was applied to one of the columns in a temp table created in the procedure.

Our Web Error email pointed to that column in the error line:

Error Number: 208

Error Message: Invalid object name 'StoreProcedure'.

Error Severity: 16

Error State: 82

Error Line: 170

It was in a Select statement.
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2023-10-31T22:10:34.27+00:00

And "StoreProcedure" is the name of the stored procedure? Or is it the name of something else?

And what exactly is on line 170? The creation of the temp table? Or a statement that operates on the temp table?

Dynamic data masking on a temp table is maybe, hm, a little unusual.
Sylvia Rivera 21 Reputation points

2023-10-31T22:20:58.3766667+00:00

Correct StoreProcedure is the name I used to replace the store procedure. Line 170 points to the column in a Select From TempTable.

Example:

Select

Column -- line 170

From #TempTable

We updated MASKED WITH (FUNCTION = 'partial ....') to use STUFF.

Share via

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

2 answers

Your answer