We have some SQL "normal" and graph tables, a script that syncs the information between them.
Everything is working ok from SSMS but when building the database project in Visual Studio using msbuild we get warnings (see code and warnings details below).
If we set TreatTSqlWarningsAsErrors to True these warnings become errors.
We don't want to ignore warnings but its unclear why we even get them. Are these warnings correct?
Why are they showing in Visual Studio only and not in SSMS?
How can we resolve them without ignoring them?
The details:
We have the following two "normal" SQL tables:
CREATE TABLE [dbo].[Currency]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NOT NULL,
[UId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Currency]
PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Currency]
ADD CONSTRAINT [DF_Currency_UId] DEFAULT (NEWID()) FOR [UId]
GO
and
CREATE TABLE [dbo].[Portfolio]
(
[Id] [INT] IDENTITY(1,1) NOT NULL,
[Name] [NVARCHAR](250) NULL,
[CurrencyId] [INT] NOT NULL,
[UId] [UNIQUEIDENTIFIER] NOT NULL,
CONSTRAINT [PK_Portfolio]
PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Portfolio]
ADD CONSTRAINT [DF_Portfolio_UId] DEFAULT (NEWID()) FOR [UId]
GO
ALTER TABLE [dbo].[Portfolio] WITH CHECK
ADD CONSTRAINT [FK_Portfolio_Currency]
FOREIGN KEY([CurrencyId]) REFERENCES [dbo].[Currency] ([Id])
GO
ALTER TABLE [dbo].[Portfolio] CHECK CONSTRAINT [FK_Portfolio_Currency]
GO
and we created the following SQL graph schema together with two SQL node tables and one SQL edge:
CREATE SCHEMA [graph] ;
CREATE TABLE [graph].[Currency]
(
[Id] UNIQUEIDENTIFIER NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
INDEX [GRAPH_UNIQUE_INDEX_4FFC60C0FCBE4843A7F4B9AB0729FF78] UNIQUE NONCLUSTERED ($node_id)
) AS NODE;
CREATE TABLE [graph].[Portfolio]
(
[Id] UNIQUEIDENTIFIER NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
INDEX [GRAPH_UNIQUE_INDEX_F39F92BE8DD34DD791D6CA955DA0DA9A] UNIQUE NONCLUSTERED ($node_id)
) AS NODE;
CREATE TABLE [graph].[isOf]
(
[IsActive] BIT CONSTRAINT [DF_isOf_IsActive] DEFAULT ((1)) NOT NULL,
INDEX [GRAPH_UNIQUE_INDEX_AD8C5B40D277413580EDD943AC192869] UNIQUE NONCLUSTERED ($edge_id)
) AS EDGE;
CREATE UNIQUE NONCLUSTERED INDEX [UQ_FromTo]
ON [graph].[isOf] ($from_id, $to_id) ON [PRIMARY];
GO
A sync stored procedure which will populate the nodes and the edge from the "normal" tables that will be executed daily.
Attached parts of it:
PRINT ( 'Sync graph.[Portfolio]' );
INSERT INTO graph.Portfolio ( Id )
SELECT P.[UId]
FROM dbo.Portfolio P
WHERE NOT EXISTS ( SELECT 1
FROM graph.Portfolio G
WHERE G.Id = P.[UId] );
PRINT ( 'Sync graph.[Currency]' );
INSERT INTO graph.Currency ( Id )
SELECT C.[UId]
FROM dbo.Currency C
WHERE NOT EXISTS ( SELECT 1
FROM graph.Currency G
WHERE G.Id = C.[UId] );
PRINT('Sync Portfolio isOf Currency edge');
;
WITH UidCTE
AS ( SELECT P.[UId] AS PortfolioUid ,
C.[UId] AS CurrencyUid
FROM dbo.Portfolio P
JOIN dbo.Currency C ON C.Id = P.CurrencyId )
MERGE graph.isOf AS TGT
USING graph.[Portfolio] AS SourceFrom
JOIN UidCTE CTE ON SourceFrom.Id = CTE.PortfolioUid
JOIN graph.[Currency] AS SourceTo ON CTE.CurrencyUid = SourceTo.Id
ON MATCH(SourceFrom-(TGT)->SourceTo)
WHEN NOT MATCHED BY TARGET THEN
INSERT ( $from_id , $to_id )
VALUES ( SourceFrom.$node_id, SourceTo.$node_id)
WHEN MATCHED AND TGT.[IsActive] = 0 THEN
UPDATE SET TGT.[IsActive] = 1;
and the following script which updates the edge's IsActive column in case the relationship doesn't exist anymore in the "normal" tables(we don't want to delete it from the graph nodes/edges)
;WITH PortfolioIsOfCurrency
AS ( SELECT P.[UId] AS PortfolioUid ,
C.[UId] AS CurrencyUid
FROM dbo.Portfolio P
JOIN dbo.Currency C ON C.Id = P.CurrencyId ) ,
NodeCTE
AS ( SELECT P.$node_id AS FromNode ,
C.$node_id AS ToNode
FROM PortfolioIsOfCurrency CTE
JOIN graph.Portfolio P ON P.Id = CTE.PortfolioUid
JOIN graph.Currency C ON C.Id = CTE.CurrencyUid )
UPDATE ISOF
SET isOf.IsActive = 0
FROM graph.isOf ISOF
WHERE NOT EXISTS ( SELECT 1
FROM NodeCTE N
WHERE N.FromNode = ISOF.$from_id
AND N.ToNode = ISOF.$to_id );
We created a database project in VS 2019 with the following settings:
<DSP>Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider</DSP>
<TargetFrameworkVersion>v4.5</TargetFrameworkVersion>
<TreatWarningsAsErrors>False</TreatWarningsAsErrors>
<TreatTSqlWarningsAsErrors>False</TreatTSqlWarningsAsErrors>
From SSMS everything works perfect.
The problem is with the last syntax(UPDATE isof.active) I get the following warnings:
Warning SQL71502: Procedure: [graph].[SyncEngagementContentGraphs] has an unresolved reference to object [graph].[isOf].[N]. graph\Stored Procedures\SyncEngagementContentGraphs.sql 57
Warning SQL71502: Procedure: [graph].[SyncEngagementContentGraphs] has an unresolved reference to object [graph].[isOf].[N]. graph\Stored Procedures\SyncEngagementContentGraphs.sql 58
Warning SQL71509: The model already has an element that has the same name NodeCTE.$node_id. graph\Stored Procedures\SyncEngagementContentGraphs.sql 47
Warning SQL71509: The model already has an element that has the same name NodeCTE.$node_id. graph\Stored Procedures\SyncEngagementContentGraphs.sql 48
and if we set TreatTSqlWarningsAsErrors to True these warnings become errors and we cannot leave it like this (set to False) in the long run.
