Tuesday, March 1, 2011

Visual Studio 2010 Database Projects Warning SQL04151: Unresolved Reference

In Visual Studio 2010 Database Projects you may get a SQL04151 Unresolved Reference warning when building a solution that contains multiple database projects. This can stem from a multitude of things including unresolved or ambiguous references in store procedures, references to linked servers, or incorrectly setup database references in the project.

You have two options:
1.       Fix all the references. When all you have to do is fix scripts that have table aliases but the column aliases are missing, it’s pretty easy. But when Visual Studio starts barking about linked server references, it’s a bit harder to get rid of those warnings. Example below

Instead of:
Select Col1
      ,Col2
  From Table1 T1 inner join Table2 T2
    on T1.Col1 = T2.Col1


Use:
Select T1.Col1
      ,T2.Col2
  From Table1 T1 inner join Table2 T2
    on T1.Col1 = T2.Col1

Or in the instance of cross-database queries, you may have to replace the hard coded database name with a variable that is defined in the .sqlcmdvars file of the project

For instance,
                Select * From DatabaseName.dbo.Table

Would become
                Select * From [$(DBName)].dbo.Table

2.       Suppress SQL04151 warnings in your project. You can do this by right clicking on your database project and selecting properties. Once you are there, you should see a “Build” tab on your left side.  In the “Suppress Warnings” check box, input 4151. Note: If you are using multiple configurations, you may want to select “All Configurations” at the top of the page.

No comments:

Post a Comment