Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm have a command line that works in the regular old Windows Command Shell, but somehow gets misinterpreted in Powershell (I'm fairly new to Powershell).

sqlcmd -S .\SQLEXPRESS -i "f:\SQLBackups\ExpressMaint.sql" -v DB="ksuite" -v OPTYPE="DB" -v BACKUPFOLDER="f:\SQLBackups" -v REPORTFOLDER="f:\SQLBackups\Reports" -v DBRETAINUNIT="days" -v DBRETAINVAL="7"

Powershell seems to be stripping the drive letters out of the arguments that require paths. For example, I get the following when I attempt to run the above command in Powershell:

Sqlcmd: ':\SQLBackups': Invalid argument. Enter '-?' for help.

Well sure it's invalid without the drive letter. I have tried variations on double quoting it, escaping it, etc. but can't get it to work. What am I missing that Powershell does differently?

share|improve this question

3 Answers 3

up vote 0 down vote accepted

Jaykul's start-process is how I do it as well. SQLCMD was designed with cmd.exe in mind and as a result there's some conflict between it wanting to see the quote characters and the way that Powershell uses them (or doesn't use them more to the point).

Here's what you can use for a straight-up call, should work:

sqlcmd -S .\SQLEXPRESS -i "`""f:\SQLBackups\ExpressMaint.sql`"" -v 'DB="ksuite"' -v 'OPTYPE="DB"' -v 'BACKUPFOLDER="`"'f:\SQLBackups'`"" -v 'REPORTFOLDER="`"'f:\SQLBackups\Reports'`"" -v 'DBRETAINUNIT="days"' -v 'DBRETAINVAL="7"'
share|improve this answer
    
I ended up with some variation of this on my own that works, but it is ugly. I basically escape every double quote and it works. Thanks for the input. –  Casey Wilkins Jan 10 '11 at 15:24

For starters, PowerShell assumes that all parameters are either just a value, or a -name value pair. When you have quotes in the middle of the value, weird things happen...

You most likely just need to quote the whole parameter in those cases:

sqlcmd -S .\SQLEXPRESS -i "f:\SQLBackups\ExpressMaint.sql" -v 'DB="ksuite"' -v 'OPTYPE="DB"' -v 'BACKUPFOLDER="f:\SQLBackups"' -v 'REPORTFOLDER="f:\SQLBackups\Reports"' -v 'DBRETAINUNIT="days"' -v 'DBRETAINVAL="7"'

However, my usual suggestion is to just use Start-Process, which takes all the arguments in a single string, so you can do:

Start-Process sqlcmd @"
-S .\SQLEXPRESS -i "f:\SQLBackups\ExpressMaint.sql" -v DB="ksuite" -v OPTYPE="DB" -v BACKUPFOLDER="f:\SQLBackups" -v REPORTFOLDER="f:\SQLBackups\Reports" -v DBRETAINUNIT="days" -v DBRETAINVAL="7"
"@ -RedirectStandardOutput f:\SQLBackups\CapturedOutput.log

The -RedirectStandardOutput f:\SQLBackups\CapturedOutput.log on the last line there will just capture stdout to a file in case you needed it... if you need to log the error output, or need to provide input, you can redirect those to/from files also.

share|improve this answer
    
Can't get the second example to work, PowerShell tells me that the @ symbol is "Unrecognized token in source text." I believe the first example would work and seems like a clean solution, but in the real script I'm replacing the BACKUPFOLDER argument with a variable (BACKUPFOLDER=$backupfolder), but can't get the quoting right to make the variable expand to a string. Any ideas? –  Casey Wilkins Jan 10 '11 at 15:23
    
PowerShell 2.0? You're pasting it exactly? The string is the at sign, a double quote, then the newline ... then the parameters ... then a new line, the double quote, then the at sign. There are three characters at the beginning ( at, quote, newline ) and end ( newline, quote, at ). –  Jaykul Jan 13 '11 at 4:16
    
Honestly though, if you don't need to use single-quotes or $variables in the parameters, you can just use single quotes, like: Start-Process sqlcmd '-S .\SQLEXPRESS -i "f:\SQLBackups\ExpressMaint.sql" -v DB="ksuite" -v OPTYPE="DB" -v BACKUPFOLDER="f:\SQLBackups" -v REPORTFOLDER="f:\SQLBackups\Reports" -v DBRETAINUNIT="days" -v DBRETAINVAL="7"' -RedirectStandardOutput f:\SQLBackups\CapturedOutput.log –  Jaykul Jan 13 '11 at 4:17

I don't have sqlcmd so I can't comment on how that is interpreting the parameters it is being passed but if I use the structure you are using to delivers the parameters to a batch file that just echoes the command that invoked the cmd shell (echo %cmdcmdline%) shows that what is being passed as the parameters is as follows:

cmd /c ""C:\Users\Joe\sqlcmd.cmd"  -S .\SQLEXPRESS -i f:\SQLBackups\ExpressMaint.sql -v DB=ksuite -v OPTYPE=DB -v BACKUPFOLDER=f:\SQLBackups -v REPORTFOLDER=f:\SQLBackups\Reports -v DBRETAINUNIT=days -v DBRETAINVAL=7"

Sqlcmd's parser probably does not ike the way that Powershell is stripping outthe quotes around some of the parameters like the query location and the way the DB="ksuite" parameter is seriously changed.

One option that might work is to just wrap the whole lot in single quotes to preserve the internal double quoted strings and hope that the sqlcmd parser can deal with this:

sqlcmd '-S .\SQLEXPRESS -i "f:\SQLBackups\ExpressMaint.sql" -v DB="ksuite" -v OPTYPE="DB" -v BACKUPFOLDER="f:\SQLBackups" -v REPORTFOLDER="f:\SQLBackups\Reports" -v DBRETAINUNIT="days" -v DBRETAINVAL="7"'

That will deliver the unmodified parameter list to sqlcmd but it does so as a single parameter as far as the shell is concerned and I would be surprised if it worked. You are much more likely to have success if you selectively quote all parameter blocks that are being modified with single quotes as follows:

sqlcmd.cmd -S .\SQLEXPRESS -i '"f:\SQLBackups\ExpressMaint.sql"' -v 'DB="ksuite"' -v 'OPTYPE="DB"' -v 'BACKUPFOLDER="f:\SQLBackups"' -v 'REPORTFOLDER="f:\SQLBackups\Reports"' -v 'DBRETAINUNIT="days"' -v 'DBRETAINVAL="7"'

If this still doesn't work there may be other items that need to be explicitly escaped and not just avoided by switching between single and double quoting by using the backtick character which is used as the escape character in Powershell string parsing.

You can get more info on all aspects of quoting and escaping via help about_quoting from within PowerShell.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.