Friday, March 09, 2012 11:02 AM
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? Apologies if this isn't the best place to ask - first time I've used the forum in 28 years of working with Microsoft stuff - it's been fine up until now ;)
Monday, March 12, 2012 7:52 AMModerator
Do the [dbo].[vw_AddressProfile] view and the [dbo].[TransactionHistory] synonym in the same database project? If not, I think you need to add the database reference in the view database project in order to use the synonym. When you add the database reference, you also create one database variable in the Database.sqlcmdvars file. And you may have your view script to be similar to:
CREATE VIEW [dbo].[vw_AddressProfile] AS SELECT * FROM [$(DBName)].[dbo].[TransactionHistory]
Vicky Song [MSFT]
MSDN Community Support | Feedback to us
- Marked As Answer by Vicky SongModerator Monday, March 19, 2012 4:01 AM