CODEWITHSUNDEEP
sql-servervisual-studio-2012database-schemasql-server-data-tools
JBDev1
JBDev1

Reputation: 41

SQL71501 SSDT unresolved references - compare schema

Ok, there are a bunch of posts about this, but all different versions of the problem. I could not find a post with my specific (albeit, basic) version of the problem. First, these are the steps that I followed to create a database project in Visual Studio 2012 (shell) with SQL Server Data Tools (SSDT):

This gives me a populated database project of all the objects in SQL server database. However, upon building the project, I get 200+ errors of unresolved references:

X- contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: X, Y, Z

AND

X- has an unresolved reference to object

Adding a database reference to Master cut the errors down to 127, and now it's more manageable, but this not resolved. It only affects 5 or 10 objects out of 100's. Here's some things to keep in mind:

  1. Only one database is used in the SQL Server objects (views, etc.)

  2. Only 2 part naming ie (dbo.Table as T)

  3. The "Enable Extended Transact-SQL verification for common objects" option is not present in my version of VS 2012, this feature was removed by Microsoft, and is already turned off.

  4. I ran the command line sqlpackage.exe and created a dacpac of the database, this was then added as a database reference.

The database project will still not build. The errors only pertain to certain views and procedures. Has anyone had this problem?

Upvotes: 2

Views: 1703

Answers (1)

JBDev1
JBDev1

Reputation: 41

Ok, I found the problem....

There are fields in a table that have their names padded with spaces, ie:

[Field1 ]

The views, procs, etc that reference those fields of course only use the name portion without padding, ie: Field1

That name [Field1] is acutally wrong, the real name is [Field1 ] This caused the schema to break.

The tricky part is.... they still work in SQL Server. Although SQL Server shows these field names in a query with errors, it still is able to successfully process the query! I feel like there is some setting that has been turned off server-side... Anyway, SQL Server should have never allowed those statements to successfully run and the problem would have been caught.

Upvotes: 2

Related Questions