I am trying to use the following SQL query to pull the date of the most recent archived running config and last time NCM successfully downloaded a running config:
SELECT
[dbo].[NodesData].NodeID, [dbo].[NodesData].IP_Address, [dbo].[NodesData].Caption, [dbo].[NodesData].UnManaged,
[dbo].[NCM_ConfigArchive].DownloadTime, [dbo].[NCM_ConfigArchive].AttemptedDownloadTime
FROM
([dbo].[NodesData] INNER JOIN [dbo].[NCM_NodeProperties] ON [dbo].[NodesData].NodeID = [dbo].[NCM_NodeProperties].CoreNodeID)
LEFT OUTER JOIN [NCM_ConfigArchive] ON [dbo].[NCM_NodeProperties].NodeID = [dbo].[NCM_ConfigArchive].NodeID
WHERE
[dbo].[NCM_ConfigArchive].ConfigType='Running'
ORDER BY
[dbo].[NodesData].Caption, [dbo].[NCM_ConfigArchive].DownloadTime
When trying to run this in Database Manager, I get the following error:
"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
I have tried separating the joins into separate queries (selected only the appropriate fields to the join in each case), and both will run. Whenever I try to join all three tables, the error appears, even if I try to change the type of join on the second join.
I have been examining the constraints in the tables, but see nothing that sticks out as obviously triggering the error. I did note that [dbo].[NCM_ConfigArchive].BaseConfigID allows NULLs and has plenty of them, but if that was the cause, I figured it would hit when joining just NCM_NodeProperties and NCM_ConfigArchive. Yet, that query ran.
Does anyone have ideas?
