1

I am trying to figure out why Visual Studio does not report error SQL71501 (unresolved reference to an object) when joining temporary table.

I am using VS 2019 version 16.3.6 and the issue can be reproduced by creating a new SQL Server Database Project, adding a new table and stored procedures:

CREATE TABLE [dbo].[Table1]
(
    [Id] INT NOT NULL PRIMARY KEY
)

CREATE PROCEDURE [dbo].[Procedure1]
AS
    SELECT t1.[Id2] -- Causes error
    FROM [dbo].[Table1] t1
RETURN

CREATE PROCEDURE [dbo].[Procedure2]
AS
    CREATE TABLE #test ([Id] int)

    SELECT t1.[Id2] -- No error and project builds
    FROM [dbo].[Table1] t1
    JOIN #test t2 on t1.[Id]=t2.[Id]
RETURN

We need to refactor a large database and this hides a lot of errors that should occur when deleting/renaming a database object. Is there a way to fix this?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Matej Darebnik
  • 11
  • 1
  • It is not a VS problem, using SSMS `[Procedure2]` is created with no errors too. There was a disscusion https://social.msdn.microsoft.com/Forums/en-US/9721ae05-f1f9-4628-8d84-94940d552edc/no-error-invalid-column-name-if-temp-table-used?forum=vstsdb . This sql-server behaviour will not be changed. – Serg Oct 23 '19 at 09:22
  • @Serg: the VS error messages are not the same as the SQL Server behavior. Specifically, it is completely legal to create a stored procedure that references nonexistent tables, relying on deferred compilation (as much as people hate this "feature"), so creating `Procedure1` would be accepted with not even a warning. In database projects missing references *do* produce warnings. – Jeroen Mostert Oct 23 '19 at 11:16
  • VS will issue SQL71502 warnings with an unresolved reference to `[dbo].[Table1]` for both procedures if the table is not present at all. If the table is there but the column is not, SQL71501 is triggered only for `Procedure1`. It seems the analyzer errs on the side of caution when a temporary table is involved, turning off validation of column references entirely, not just for the temporary table in question. Either way you slice it this behavior is inconsistent -- it has more stringent checks than SQL Server itself, except when it doesn't. – Jeroen Mostert Oct 23 '19 at 11:25
  • @JeroenMostert, my point is there is no way to get a compile-time error for a temp table in a proc OP wants for `[Procedure2]`. This is an sql-server feature, not VS or SSMS feature. It doesn't look so deep in the proc code to detect missing temp table column. – Serg Oct 23 '19 at 12:33
  • @Serg: SQL Server doesn't check *anything* (temp table or no), but the analyzer included with Visual Studio database projects *does*. SQL Server is literally not even running when these messages are produced. The issue here is *not* that it it's ignoring temp tables (that might be forgivable) but that it's ignoring even the incorrect column reference of a *non* temp table, just because a temp table is involved in the query. This is consistent neither with how SQL Server compiles stored procedures, nor with its own more extensive analysis. – Jeroen Mostert Oct 23 '19 at 12:41
  • @JeroenMostert Yes, you needn't any sql-server connection to compile sql within VS. But the VS compiling the code in question is consistent with sql-server itself. https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=23c87949b6431b9ae72560d8becbf4bd – Serg Oct 23 '19 at 13:39
  • @Serg: except, of course, that removing the `CREATE TABLE` altogether will cause SQL Server to accept `Procedure1` with no complaint at all, whereas you get a helpful message in VS. It seems extraordinarily silly for the analyzer to acknowledge that missing references are a useful thing to detect even if SQL Server will not complain about them, but then not do so *sometimes* because... it's consistent with what SQL Server does? At least SQL Server has an excuse: compilation is deferred and will happen when the procedure runs. They can't run at all in database projects. – Jeroen Mostert Oct 23 '19 at 13:44

0 Answers0