SQL71501 SSDT unresolved references - compare schema

2
votes

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):

  • Create new project, SQL-> database project
  • Right click on project, choose "Compare Schema"
  • Set source (SQL Server instance on a server)
  • Set target (local database project - the current project)
  • Run the "Schema Compare"
  • Update the target

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?

1
sql-servervisual-studio-2012database-schemasql-server-data-tools
Do the objects/tables that the references point to actually exist? - Ed Elliott
Also, you should be able to double-click those warnings/errors and have SSDT take you to the affected lines (or at least pretty close to them). That can help you troubleshoot. I'd look for syntax issues, missing objects, or similar. We'd have issues w/ 3 part names for the same DB at times or implied "dbo" (e.g., DB..Table instead of just dbo.Table). - Peter Schott
Yes, the tables exist. This is a live/working database. When double-clicking, it does take me to the line. It says it can't determine which table the field comes from and it lists all of the tables involved in that select statement. The statement of course works when run against the database. - JBDev1

1 Answers

2
votes

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.