Calling sqlcmd.exe in batch file using variables causes error: "unexpected argument"

Question

In a Windows batch file, this works:

sqlcmd.exe -b -S xxMySqlServerNamexx -Q "BACKUP DATABASE xxMyDatabaseNamexx TO DISK='d:\data\xxMyDatabaseNamexx.bak' with init, compression"

As does this:

set vSource_SqlServer="xxMySqlServerNamexx"      
sqlcmd.exe -b -S %vSource_SqlServer% -Q "BACKUP DATABASE xxMyDatabaseNamexx TO DISK='d:\data\xxMyDatabaseNamexx.bak' with init, compression"

But this:

set vSource_SqlServer="xxMySqlServerNamexx"  
set vSource_SqlDbName="xxMyDatabaseNamexx"  
sqlcmd.exe -b -S %vSource_SqlServer% -Q "BACKUP DATABASE %vSource_SqlDbName% TO DISK='d:\data\xxMyDatabaseNamexx.bak' with init, compression"

...causes error:

Sqlcmd: 'xxMyDatabaseNamexx" TO DISK='d:\data\xxMyDatabaseNamexx.bak' with init, compression"': Unexpected argument. Enter '-?' for help.

As you can see it is choking on using a variable %vSource_SqlDbName% in place of xxMyDatabaseNamexx

Is there a correct way to do this in this form (by that I mean, yes I should probably be using Powershell or an alternative approach, but I have several existing batch files in this form that I would prefer to convert to use variables in this form, even if it is not the perfect way to do it)?

For people trying to use SQL Authentication with sqlcmd and running into similar issue. Other things to consider: 1. -U is not same as –U. The character "-" is the correct one. 2. -u <username> does not work, need to use uppercase "-U". Hope it helps! — DevCod
– DevCod, Commented Jul 12, 2018 at 18:49

Zastai · Accepted Answer · 2016-04-27 21:19:37Z

2

The set statement does not dequote its arguments. So set a=b c means %a% expands to b c, and set a="b c" means it expands to "b c".

So in your non-working example you're putting double-quote text inside a double-quoted string, which does not work.

Remove the quoting from the sets and it should work.

answered Apr 27, 2016 at 21:19

Zastai

1,28515 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aschipfl · Accepted Answer · 2016-04-28 16:21:45Z

0

The most secure way to set a variable is to use the syntax (in case command extensions are enabled, which is the default setting in Windows anyway):

set "vSource_SqlDbName=xxMyDatabaseNamexx"

Note the quotation marks around the entire expression, so they do not become part of the expanded value. %vSource_SqlDbName% is therefore expanded to vSource_SqlDbName.

answered Apr 28, 2016 at 16:21

aschipfl

35.3k12 gold badges62 silver badges106 bronze badges

Collectives™ on Stack Overflow

Calling sqlcmd.exe in batch file using variables causes error: "unexpected argument"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related