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.
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)