Find Invalid Objects reports invalid "Invalid Object"

NigelC

Hi All,

When running the Invalid Object Checker, it complains about this:

IF OBJECT_ID('tmp_LatestTRCPHCEvolution', 'U') IS NOT NULL
        DROP TABLE tmp_LatestTRCPHCEvolution;

It states that tmp_LatestTRCPHCEvolution does not exist which it may or may not hence the check:
IF OBJECT_ID('tmp_LatestTRCPHCEvolution', 'U') IS NOT NULL

This is in Prompt 10.8.1.23216. Is this a defect?

Jon_Kirkwood

Hi Nigel,

Thank you for your patience whilst I understood and replicated this issue.

I have received confirmation that our development team have accepted this as a bug and has been placed in backlog queue to be rectified in a future release of SQL Prompt.

There is a ticket logged in our system against your account and you should receive a notification when the product version that resolves this bug is released.

Jon_Kirkwood

Hi Nigel,

Thanks for reaching out to the Redgate Forums about your concern with SQL Prompt.

I've replicated this code in Prompt 10.8.1.23216 as a stored procedure and unfortunately (fortunately) it is not appearing in an Invalid Object Checker.


Can I ask for a bit more info around this code snippet to better determine what is occurring?

* Can you confirm the exact error message that is appearing in the Invalid Object Checker window

* Enable verbose logging, run the Invalid Object Checker and provide copies of the log file code where this object is mentioned
Finding SQL Prompt Log Files

* Update to the latest version of SQL Prompt (10.8.2.23489) and verify if the issue persists
Download here

Whilst you are doing that I will flag this concern with our devs and see if this may be a false-negative report and should Prompt assume that it's okay to reference a non-existent object in an "IF object IS NOT NULL" check (since it's checking if the object exists and won't make the query fail if it doesn't)

NigelC

Hi Jon,

I have upgraded to the latest version; no change unfortunately.

The following is a cut-down version of the proc that erroneously reports an Invalid Object:

USE Operations;

GO

CREATE OR ALTER PROCEDURE dbo.uSP_Update_Sum_LatestTRCPHCEvolutionTest

AS

BEGIN

IF OBJECT_ID('tmp_LatestTRCPHCEvolution', 'U') IS NOT NULL

DROP TABLE tmp_LatestTRCPHCEvolution;

SELECT TOP(0)

*

INTO tmp_LatestTRCPHCEvolution

FROM Sum_LatestTRCPHCEvolution;

INSERT tmp_LatestTRCPHCEvolution(Year)

SELECT 2021;

END;

The message is: Invalid object name 'tmp_LatestTRCPHCEvolution'

The log snippet is:

2021-10-07 10:07:58.376 +01:00 [Information] Checking validity of dbo.uSP_Update_Sum_LatestTRCPHCEvolutionTest of type "StoredProcedure"

2021-10-07 10:07:58.954 +01:00 [Information] Checking validity of dbo.uSP_Update_Sum_LatestTRCPHCEvolutionTest of type "StoredProcedure"

2021-10-07 10:07:58.954 +01:00 [Debug] Script to run is "EXEC [dbo].[uSP_Update_Sum_LatestTRCPHCEvolutionTest]"

2021-10-07 10:07:59.012 +01:00 [Information] Found error: Invalid object name 'tmp_LatestTRCPHCEvolution'.

Jon_Kirkwood

Hi Nigel,

Thanks for the extra code snippets.
I do believe I found where it is being reported as Invalid.

Within the SELECT statement there are two references to tmp_LatestTRCPHCEvolution

I replaced each with a unique value and found the second reference is what is being picked up

SELECT TOP(0) *

INTO a_tmp_LatestTRCPHCEVolution

FROM ErrorLog

INSERT b_tmp_LatestTRCPHCEVolution(Year)

SELECT 2021;

END;

I then replaced the second reference with a value in a database that I knew existed and the Invalid Object error stopped appearing.

From this, I suspect that the INSERT statement is trying to enter a value into a non-existent table/field.

Are you able to investigate that further and advise?

NigelC

Hi Jon,

You are correct, it is the INSERT that fails BUT, the previous statement creates the table:

SELECT TOP(0)

*

INTO tmp_LatestTRCPHCEvolution

FROM Sum_LatestTRCPHCEvolution;

... so after this is executed, the table exists. This really is a defect in the Invalid Object checker.

The SQL Compiler is fine with the code; it is just the Invalid Object checker that is not accepting that the code is correct ...

Jon_Kirkwood

Hi Nigel,

Thanks for your patience whilst our team investigate this one.

It has been found that this is almost certainly being picked up by SQL Server directly as an invalid object. 

This was checked by running SET SHOWPLAN_TEXT ON before running the procedure (EXEC uSP_Update_Sum_LatesTRCPHCEvolutionTest). 

The error which comes back from SQL Server is the same one that our tool is reporting.

Unfortunately, there is not much our team can do to solve your issue through a software fix and they will not be doing anything further.

Apologies for this being less than an ideal solution and hope you are able to find a suitable workaround through a code review.