Post reply

Using Views that reference Synonyms in VSTS 2008

  • March 8, 2012 at 5:22 am

    #258368

    I have recently started using Visual Studio 2008 for Database Professionals. We work in an environment with may queries running across databases on the same server and have one set of databases per country. Therefore I use synonyms to keep the code constant and ease the deployment task.

    However, if a use synonyms in a View, I am hit with a multitude of Build Errors in VS. These are of the form:

    Error227TSD03006: View: [dbo].[vw_AddressProfile] has an unresolved reference to object [dbo].[TransactionHistory].C:\[Database Project Name]\Schema Objects\Views\dbo.vw_AddressProfile.view.sql

    In the above case vw_AddressProfile is the View I am creating and dbo.TransactionHistory is a synonym which references a table in another database. The View works a treat if scripted directly in SSMS - the problem is purely with Visual Studio.

    I found one source which suggested turning off the "Enable Extended Transact-SQL Verification" option on the Build tab. However that option is not present when I look.

    Any ideas on how to solve this?

  • April 22, 2013 at 3:22 pm

    #1608877

    Way overdue for this, but ... create a DBSchema file for your "TransactionHistory" database project. Put it in some shared location in a level above the database projects. You can usually get this if you can successfully build the project

    C:\DatabasesC:\Databases\Schemas <-- This folder for your shared schema files

    C:\Databases\TransactionHistory

    C:\Databases\MyProject1

    C:\Databases\MyProject2

    ...

    In your project, right-click the "References" folder and choose to add a Database Reference. Point that file to the DBSchema file in your Schemas folder. Having this as a reference will eliminate most of the reference issues. I found that I needed to also add the schema to the table names referenced to get them to work, but it will allow 3-part naming conventions for referenced databases.

    This works for VS 2008 and VS 2010. However, if you have the freedom to upgrade to SSDT SQL Projects, I'd say it's worth it. You'll need to switch out your DBSchema files for DACPAC files, but there are a lot of small changes that make them much nicer to use over the older VS2008/2010 projects. Referenced projects would work in a similar manner.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply