How do I resolve 4-part-name linked server query errors in VSTS DB project?

Question

I am using VSTS 2008 with both the developer and database editions installed. I have the DB edition GDR installed (version 9.0.31124). We have several places in code that we reference linked servers (DB2 mainframe, iSeries, and SQL Server). One such example is this code that we have in a store procedure:

SELECT VendorID   
INTO     #tmpVendors   
FROM    DataWhse.BIDataWhse.BP.Vendors   
WHERE  EffectiveDate < DATEADD(day, -1, DATEADD(month, 1, CAST(LTRIM(RTRIM(CAST(@Month AS CHAR(2)))) + '/01/' + CAST(@YEAR AS CHAR(4)) AS DATETIME)))   
AND     ExpirationDate > CAST(LTRIM(RTRIM(CAST(@Month AS CHAR(2)))) + '/01/' + CAST(@YEAR AS CHAR(4)) AS DATETIME)

I'm getting unresolved reference errors (TSD03006) on this type of code. What is the current recommendation for how to handle this type of linked server reference? In this case, we are dealing with another SQL Server 2005 instance. The database we're linking to has tons of tables that we'd never use. So, I'd like to avoid having to pull down the whole schema for that DB if possible.

The info that I find when I search seems to be outdated. The GDR has changed so much over the last year and there is lots of guidance out there from previous CTP versions that is now oboslete.

Answer 1

I finally got this resolved. I created a database project named BIDataWhse, added a bp schema, added a bp user, and added the bp.vendors table definition. Then compiled that project and added a db reference to the project where I was getting the error. In the reference I specified variables for the server and database and allow the variable substitutions. The resulting code was:

SELECT VendorID      
INTO     #tmpVendors      
FROM    [$(DataWhse)].[$(BIDataWhse)].BP.Vendors      
WHERE  EffectiveDate < DATEADD(day, -1, DATEADD(month, 1, CAST(LTRIM(RTRIM(CAST(@Month AS CHAR(2)))) + '/01/' + CAST(@YEAR AS CHAR(4)) AS DATETIME)))      
AND     ExpirationDate > CAST(LTRIM(RTRIM(CAST(@Month AS CHAR(2)))) + '/01/' + CAST(@YEAR AS CHAR(4)) AS DATETIME)

Answer 2

To expand on this situation, the Add Database Reference will do most of the work for you. You need to create the project for the linked database and import the db schema from the database (or import scripts for the tables you need if you don't want to do the whole database). "In the reference I specified variables for the server and database and allow the variable substitutions" - This means that on the Add DB Ref dialog you can check Define Server Variable, Define Database Variable, and Update Schema Objects and Scripts. Enter names for the variables (it will surround them with $(..)) and enter the values you want to substitute (the real server/database names to insert when the deployment script is generated). The Update option will cause it to go thru your project and edit all the scripts to replace the server/database names with the variables. cool.