Wednesday, November 7, 2012

SSDT: Errors and Warnings

SSDT includes an Errors and Warnings window that is well worth your attention. Ideally, your project should have no errors or warnings.

clip_image001

 

However, sometimes coding errors slip in to your project or you get warnings that an object can't be found because it exists in another database. Sometimes a warning might appear because an object is missing completely, in this project or another one. In these cases, it's well worth checking this section to find out where you may have some issues.

Warnings are not necessarily a problem. SSDT will bring possible issues to your attention, but warnings will not stop a project from building and publishing unless you have set the option to treat warnings as errors or unless there really is an underlying problem that causes an issue during the publish phase.

For example, if I modify the Person.Address table to add a new column in the code, but forget to add a comma, I'll get an error something like this.

clip_image002

If you double-click on the line, the editor should open the appropriate file and take you pretty close to your problematic line. Correct the problem, save the file, and move on to the next error.

 

Some common warnings/errors

  • 4151 - Unresolved database reference. This is often caused when one of the objects in a database references another. This can often be resolved by creating a database reference.
  • 71562 - Unresolved database reference warning.
  • 71502 - Another unresolved database reference warning.

How to globally suppress certain warnings

  • Right click the root of the project and select properties
  • Click on the "Build" tab
  • Enter in the numeric portion of the codes, separated by commas. Remove the "SQL" and any leading zeroes when entering the code.

clip_image003

9 comments:

  1. Hi, First thank you for your succinct SSTD blog, this was a great place for me to start learning about the available features.
    I run into an issue with essentially a circular references and I was hoping you could maybe point me towards some more information on.
    The simplest case scenario would be two databases, A and B where database A may reference some tables or views from B, and vice versa, B may reference some data from A.
    I can not create a dacpac file for either A nor B, I tried importing from visual studio and using the "Extract Data Tier Application" option from SSMS, but I get the object not supported error because of the cross dependencies.
    Any pointers would be much appreciated.
    Thanks,

    Chris

    ReplyDelete
    Replies
    1. That's a fun one. I actually had to use the SQLPackage command to extract the database into a dacpac. I referenced that at the bottom of this article:
      http://schottsql.blogspot.com/2012/10/ssdt-importing-existing-database.html

      Actual code (example):
      Sqlpackage.exe /a:extract /ssn:localhost /sdn:Adventureworks2008 /tf:Adventureworks2008.dacpac

      Use these files as DB References to your external DBs and you should be all set. Just don't choose to make them variable names unless you use different DB names in different environments.

      Delete
    2. Thanks Peter,
      That got me through that hurdle, it's interesting that I can generate the dacpac file with default parameters through the sqlpackage but not SSMS.
      Thanks again, really appreciate your help and your blog.

      Cheers,

      Chris

      Delete
  2. Thanks! I was looking for how to suppress SQL71502 against files. This was the only article that pointed out to remove the "SQL" and any leading zeroes when entering the code.

    ReplyDelete
    Replies
    1. Glad it helped. I think I just played around with it until it did what I expected. still want to watch out for those warnings if they pop up and try to eliminate them as much as possible, though.

      Delete
  3. This method works great inside Visual Studio, but when I build the solution with a TFS Build Server, the warnings are not suppressed. Any idea how to tell the build server to suppress the warnings as well?

    ReplyDelete
    Replies
    1. I'm not aware of a way to suppress the warnings within TFS Builds other than eliminating the source of the warnings. However, I never encountered a fatal error when doing a build that produced warnings. Can you give some more details about why the warnings are causing an issue other than that they show up?

      Delete
  4. I did what you said...but still had to add in all the "missing" references that were referenced from other projects....and the errors went away. Seems I saw one time where there was a place in the solution explorer properties where you could put an error number as well but I can't find any documentation....

    ReplyDelete
    Replies
    1. You can plug in error/warning numbers (just the number part of the warning), but ... if there's truly an error like an external database reference, that won't fix the problem. You will definitely need to handle that. If it's just a warning like a case mismatch, that can suppress the warnings. You set that either at the object level in the properties or you can set global options for ignoring warning numbers at the project level.

      Delete