0

I am trying to write this code in a Storage Procedure in Visual Studio 2019 Preview.

(To mention is that this code works anyway. But I wonder about those unresolved references if this will result in any problem?)

But as seen in the image below, I get this error for the line: INFORMATION_SCHEMA.TABLES
SQL71502: Procedure: [dbo].[createNewTable] has an unresolved reference to object [INFORMATION_SCHEMA].[TABLES].

I don't know exactly what error this is. I googled and found this but are not sure if this is the problem?. However, I can't find any "References" in the Solution Explorer(See image below):

Add a database reference to master:
Under the project, right-click References.
Select Add database reference....
Select System database.
Ensure master is selected.
Press OK.

CREATE PROCEDURE createNewTable
AS

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'testTable5')
BEGIN
CREATE TABLE testTable5
(
    [DateTime]    SMALLDATETIME  NOT NULL,
    [FeatureNbr]  SMALLINT       NOT NULL,
    [Value]       FLOAT (53)         NULL,
    [Bool]        BIT                NULL,
    CONSTRAINT UC_testTable5 UNIQUE (DateTime),
    CONSTRAINT PK_testTable5 PRIMARY KEY (FeatureNbr, DateTime)
);
END

Shows code and error



Shows Solution Explorer



enter image description here

coding
  • 61
  • 8
  • Right click on *dependencies* – Panagiotis Kanavos Oct 07 '20 at 12:29
  • @Panagiotis I right clicked there. I added a picture how it looks like. If I choose `Add Project Reference` I just get a blank window. I am not sure what I should do? – coding Oct 07 '20 at 12:33
  • 1
    You opened the wrong project then. Tables and stored procedures are stored in Database projects, what you posted is a library. In fact, you *can't* treat SQL Server database as a single file, unless you use eg LocalDB, an embedded engine feature of SQL Express. In normal deployments the database files are managed by the server, not deployed by the application – Panagiotis Kanavos Oct 07 '20 at 12:35
  • 1
    BTW most scripts that check for table existence use `OBJECT_ID` and `sys.objects`, or `sys.tables`, not `INFORMATION_SCHEMA`. That schema is very, very limited – Panagiotis Kanavos Oct 07 '20 at 12:39
  • I understand. I am not sure if my new image is the right one? I see, I tried using `OBJECT_ID` but gets the same type of error there. I am not sure if this is error is a big problem as the code still works? (I should use `OBJECT_ID` instead perheps) – coding Oct 07 '20 at 12:43
  • SQL Server 2016 (the oldest version still in mainstream support) introduced [DROP TABLE IF EXISTS](https://techcommunity.microsoft.com/t5/sql-server/drop-if-exists-new-thing-in-sql-server-2016/ba-p/384562). – Panagiotis Kanavos Oct 07 '20 at 12:44
  • That is good to know. Thank you. Fcharisto – coding Oct 07 '20 at 12:46

0 Answers0