sql-serversql-server-2008sqlcmdbuildforge

Using double-quotes in a SQLCMD query

I'd like to issue the following command through SQLCMD:

IF DB_ID('My_DB') IS NOT NULL DROP DATABASE My_DB

There are a few caveats though...

  1. The database name is dynamic
  2. The program which is running the command (BuildForge) will not replace a variable if it appears within single quotes

So, I can't use:

-Q"IF DB_ID('${db_name}') IS NOT NULL DROP DATABASE ${db_name}

because it will use the literal ${db_name} in the single-quotes. I also can't use:

-Q"IF DB_ID("${db_name}") IS NOT NULL DROP DATABASE ${db_name}

because SQLCMD gets tripped up on the begin and end of the double quotes.

I know that I can create a script and pass in a parameter for the database name, but is there any way to do this in a single command line without a script? Any way to escape the double-quotes, etc.? Is there a BuildForge syntax that would let me build the string correctly?

Thanks!

Solution

  • I'd normally use -q and a script to do this but some thoughts...

    Can you do this?

    -Q"IF DB_ID(RTRIM(LTRIM('    ${db_name}    '))) IS NOT NULL DROP DATABASE ${db_name}"

    Or this?

    -Q"BEGIN TRY DROP DATABASE ${db_name} END TRY BEGIN CATCH IF ERROR_NUMBER() <> 3701 RAISERROR('some error not related to db not there', 16, 1) END CATCH"