How not to include sql file content when publishin

2019-06-07 02:49发布

问题:

I have a question that is very similar to an older question of mine Using variable in sql postdeployment build script? But now I don´t want to do an if/else and have the sql code in my published script. I don´t want to share information beetween customers.

So... here goes

  • I have a database project in Visual Studio.
  • I also have 2 customers with different needs

I know I can

  • use post.deployment script to include content of sql files I need by setting a varible in Project.Properties."SQLCMD Variables".

And then use it like this in Script.PostDeployment.sql

:r ..\$(Customer)Setup.sql

Where I have 2 files

  • Customer1Setup.sql
  • Customer2Setup.sql

And then I set the SQLCMD Variable as $(Customer) "Customer1" or "Customer2" and only the one I had put in the SQLCMD is included.

But when I want to use the variable in the publishing window the default one overrides it

I know I can do this somewho in a build script but I want to know if I can use the current tools and just create Customer1.publish.xml and Customer2.publish.xml files so that I know that something that is for Customer1 does not go with the script if published to Customer2.

EDIT 1:

I created a public GitHub repository to try to address my problem. Hopefully somebody can take a look and edit it to work.

EDIT 2:

This is the result from my test project (same as my real one) where in every one of the 3 tests I have there result in the same. All the "stuff" in Customer2/Customer3 is included in the result. If this is supposed to work there is some switch or setting I´m missing in my setup (VS2015 database project)

SET NOEXEC ON
Print 'This print statement should not be in the publishing script if     Customer variable is not customer1'
print 'Customer1 stuff from Customer1.sql'
SET NOEXEC OFF

IF('$(Customer)' <> 'customer2')
SET NOEXEC ON
Print 'This print statement should not be in the publishing script if     Customer variable is not customer2'
print 'Customer2 stuff from Customer2.sql'
SET NOEXEC OFF

IF('$(Customer)' <> 'customer3')
SET NOEXEC ON
Print 'This print statement should not be in the publishing script if    Customer variable is not customer3'
print 'Customer3 stuff from Customer3.sql'
SET NOEXEC OFF

EDIT 3

So if I visualize this little better This is what I want to get

But this is what I get

回答1:

The below won't work as per expectations:

:r ..\$(Customer)Setup.sql

Instead though, you could use the IF ELSE approach:

IF ('$(Customer)'='customer1')
BEGIN
    :r .\script_customer1.sql
END
IF ('$(Customer)'='customer2')
BEGIN
    :r .\script_customer2.sql
END

After a bit more research I found similar SO question. It turns out the above would not work in case you use GO in your scripts. So could use the below:

IF ('$(Customer)'<>'customer1')
    SET NOEXEC ON

:r .\script_customer1.sql
SET NOEXEC OFF

IF ('$(Customer)'<>'customer2')
    SET NOEXEC ON

:r .\script_customer2.sql
SET NOEXEC OFF