SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Views that reference Synonyms in VSTS 2008


Using Views that reference Synonyms in VSTS 2008

Author
Message
Richard Warr
Richard Warr
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3328 Visits: 1991
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?

_____________________________________________________________________
MCSA SQL Server 2012
Peter Schott
Peter Schott
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2804 Visits: 1931
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search