Sqlcmd that I think is an issue.

[Trevor]

Hi I am A DBA, and my client uses your Buildmaster tool do deploy code to SQL 2012 database. I have observed that with the the sqlcmd runs code to drop and recreate Procedures all Stored Procedures after Deployed now have SET QUOTED_IDENTIFIER OFF. Where the same code deployed via SSMS shows set to ON; The Default. Was validated via a Profile trace.

I know that this can be fixed by adding -I to the sqlcmd as a switch to fix. And why it is not a default setting? Can you tell me if you have come across this before and what are thoughts on this?

Command run:
sqlcmd -S '$targetInstances' -d $targetDatabase -i '$tmpFile'

I would think it should be for 2012/2014/2016 databases
sqlcmd -I -S '$targetInstances' -d $targetDatabase -i '$tmpFile'

The -I switch should be used for to turn on the Quoted_identifier.

Also our databases are SQL 2012 or SQL 2016. So we no longer connect to SQL 2000,2005 or 2008/R2.

Product: BuildMaster
Version: 5.7.3

[atripp]

This is likely the way that your client has it configured.

While there is support for Database Changes, BuildMaster does not use sqlcmd.exe to run these scripts, but instead lets you specify such options in a connection string.

However, I think your client is instead executing sqlcmd directly, using the "execute process" operation or something like that. So, you can probably fix it by checking the deployment plan where the scripts are actually run, and recommending they edit it.

[Trevor]

Thanks for the Quick reply.

So the Client has the connection string to use sqlcmd.exe

Since I have not used the Buildmaster myself, I don't know if I have this info correct.
If they have configured it to run the Scripts.
for example:
SP_1.sql
SP_2.sql

And they have it to run with:
sqlcmd -S '$targetInstances' -d $targetDatabase -i 'SP_1.sql'
sqlcmd -S '$targetInstances' -d $targetDatabase -i 'SP_2.sql'

What could they change to execute these scripts correctly? or is there another way to execute the scripts with in BuildMaster?

Or do you mean changing :
sqlcmd -I -S '$targetInstances' -d $targetDatabase -i 'SP_1.sql'
sqlcmd -I -S '$targetInstances' -d $targetDatabase -i 'SP_2.sql'

Thanks again

[atripp]

It's hard to say, because we don't have enough information. The "code" you shared looks like it's part of a PowerShell script, or maybe it's a cmd script, which maybe they're executing from BuildMaster? Or they could be executing the process directly? Where does $targetInstances and $targetDatabase come from?

if they're just running a PowerShell script, then I guess they should just edit that powershell script as you suggested?

Can you just ask for the deployment plan? Unless it's v4 or earlier, it will be in OtterScript (a text file), and once you see it, it should be fairly clear how to change it.