0

I have this create view script that references INFORMATION_SCHEMA.COLUMNS in two SQL projects.

Both projects have the same target platform "Microsoft Azure SQL Data Warehouse", same compability level "Sql Server 2017 (140)" and otherwise same settings as far as my knowledge of settings stretch.

Both projects are a single solution with a single project.

Using Visual Studio 2022 one project builds just fine, no errors. The other fails and displays SQL71501 errors for unresolved references for a particular script that is part of both projects.

I'd like to find out why the same code builds just fine in one project but fails in the other?

The error message:

Severity Code Description Project File Line Suppression State Error SQL71501: Computed Column: [dv].[vw_GetObjectColumnDefinitions].[SourceObjectName] has an unresolved reference to object [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME]. fake-database-name C:\Users\fake\source\repos\fake-solution-name\fake-database-name\dv\Views\vw_GetObjectColumnDefinitions.sql 13

The SQL script:

CREATE VIEW dv.vw_GetObjectColumnDefinitions

AS
    SELECT SourceObjectName =  TABLE_NAME 
                , COLUMN_NAME as ColName
                , CASE IS_NULLABLE WHEN 'YES' THEN ' NULL ' ELSE ' NOT NULL ' END as ColIsNull
                , REPLACE(DATA_TYPE, 'Datetime', 'Datetime2') as ColDataType
                , CASE WHEN NULLIF(CHARACTER_MAXIMUM_LENGTH, -1) IS NULL THEN NULL ELSE '(' + CAST(CHARACTER_MAXIMUM_LENGTH as varchar) +  ')' END as ColCharLength
                , CASE WHEN DATA_TYPE LIKE 'NUM%' OR DATA_TYPE LIKE 'DEC%' THEN '(' + CAST(NUMERIC_PRECISION as varchar) + ', '  + CAST(NUMERIC_SCALE as varchar) + ')' ELSE NULL END as ColNumPrecision
        FROM INFORMATION_SCHEMA.COLUMNS
Molotch
  • 365
  • 7
  • 20
  • Out of interest, have you considered using the `sys` objects rather than `INFORMATION_SCHEMA` (which is just there for compatibility). – Thom A Apr 21 '22 at 09:24
  • I have but they give the same SQL71501 error unless you add and a reference to the master database as far as I've understood (just referencing sys give me errors, haven't extracted the master db as project in the solution yet). I.e. I would like to not have to do that unless absolutely necessary. Also I'm very curious of the difference in behaviour, somewhere there's a setting that differs I reckon? – Molotch Apr 21 '22 at 09:27
  • 1
    This may help u: https://stackoverflow.com/questions/18096029/unresolved-reference-to-object-information-schema-tables – D A Apr 21 '22 at 10:57
  • Thanks, that did the trick. Just took forever to find where the "add database reference" dialog box was hidden in VS2022. I'll add that bit of info in the edited answer. – Molotch Apr 21 '22 at 11:26

1 Answers1

0

The answer is as commented by D A in the original question is to add a "database reference" to a generic master database in your project.

The dialog box for that in Visual Studio 2022 is found by right clicking on the "Reference" item in Solution Explorer.

Molotch
  • 365
  • 7
  • 20