In a SQL Server database project, how do I dynamically reference an Oracle linked server used in a view which uses OpenQuery?

Question

I have two views in a database project which OPENQUERY an Oracle LinkedServer. When I publish to production the Oracle Linked server needs to be named "OracleBI". When I publish to test the Oracle linked server needs to be named "OracleTestBI". How do I accomplish this?

I have tried using using SQLCMD variables and suppressing T_SQL warnings SQL71501. Errors would not suppress.

I have tried creating a skeleton view then altering the view with a post deployment script but the alter view wasn't allowed, 'incorrect syntax near ALTER.' in the batch .....

I tried creating a view with a select statement on a table function. Creating a skeleton table function and then altering the function on a post deployment script but the alter statement wasn't allowed, 'incorrect syntax near ALTER.' in the batch .....

I tried creating an additional database project for the linked server with both test and prod linked server names, added it as a reference, then use a SQLCMD variable to switch between linked server names, "...View: [compass].vwBIInvForecastBegVolume has an unresolved reference to object [$(OracleServer)]"

My Post Deployment script calls other scripts and when I say I added an alter script to the post deployment script, what I really did was add a reference to the script in the post deployment script. My post deployment script looks like this:

PRINT 'Create Environment Users'
------------------------------------------------------------
IF '$(TargetEnv)' = 'PROD'
BEGIN
    :r .\PostDeployment\CreateEnvironmentUsers.Prod.sql
END
ELSE
IF '$(TargetEnv)' = 'TEST'
BEGIN
    :r .\PostDeployment\CreateEnvironmentUsers.Test.sql
END
ELSE
BEGIN
    :r .\PostDeployment\CreateEnvironmentUsers.Local.sql
END

Roshan Roshan · Accepted Answer · 2019-04-10T02:15:15

Yes. Database Project Solution doesn't support the Alter Statements. You have to CheckIn the Script with Create Statement. During the Deployment DACPAC will Create the Alter Statement at run time. Add the Script to check if the view exist then drop and recreate.

In a SQL Server database project, how do I dynamically reference an Oracle linked server used in a view which uses OpenQuery?

1 Answers