Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Views that reference Synonyms in VSTS 2008 Expand / Collapse
Author
Message
Posted Thursday, March 8, 2012 5:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 2,176, Visits: 1,755
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:
Error 227 TSD03006: 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?
Post #1263559
Posted Monday, April 22, 2013 3:22 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:38 PM
Points: 846, Visits: 1,325
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:\Databases\
C:\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.



Post #1445202
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse