August 23, 2011 at 2:09 am
When building a Visual Studio 2010 database project, having views that use OPENQUERY (instead of 4-part reference) to resolve a linked server queries, an error is generated on the syntax of the view's scripts, when using a sqlcmdvar as substitution for the LINKED_SERVER name, e.g.
CREATE VIEW [dbo].[BlahBlah]
AS
-- Natural state
SELECT * FROM OPENQUERY([LINKED_SERVER],N'SELECT BlahBlah FROM [Database].[dbo].[BlahTable]')
-- sqlcmdvar state
SELECT * FROM OPENQUERY([$(LinkedServername)],N'SELECT BlahBlah FROM [$(DatabaseName)].[dbo].[BlahTable]')
The "natural state" won't be able to resolve LINKED_SERVER (unresolved reference error SQL03006) and substituting that with sqlcmdvar [$(LinkedServername)] throw a syntax error (SQL02010 Incorrect syntax near $(LinkedServername).
IMHO, I think the syntax error is thrown because of the SELECT * FROM expecting a 4/3 - part reference, e.g. [Server].[Database].[dbo].[Table] reference, which violates the compiler and doesn't even get to the sqlcmdvar substitution during the build process.
I have tried a few combinations of variable substitution, all violate the SELECT * FROM expectation of some valid reference syntax.
Anybody have ideas on how to avert this problem?
Much obliged.:-P
August 23, 2011 at 3:37 am
For now I am excluding all views [using theOPENQUERY (via LINKED_SERVER) from the project] and building them manually after the deploy.
August 30, 2011 at 8:55 am
The solution to the problem is to include the View create scripts (for all those of the syntax SELECT * FROM OPENQUERY()) in the post-deployment script file as included files.
I came across a post from Gert Drapers saying something like "Inside pre/post deployment script you can basically do what ever you want" [sic], so I embarked on this notion and created a sub-folder to [Post-Deployment] in my Visual Studio 2010 database project, called [Views].
I added each View's create script, substituting the Linked Server with its reciprocal $(Linked_Server) variable and voilla!
Note: Ensure that the script.postdeployment's "Build Action" property is set to PostDeploy and the included files to NotInBuild.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply