Difficulties working with powershell Invoke-SQLCmd

powershellsqlcmd

I've setup a simple sqlcmd script. Contents below.

  use $(dbname);
  select * from $(tablename)

I'm then running the following Invoke SQLCMD

$SQLCMDVarArr = "dbname='MyDatabase'", "Tablename='MyTable'"

INVOKE-SQLCMD -ServerInstance MyServer\MyInstance -inputFile '\\MyShare\Database\Sprints\csrtemp2\csrtest.sql' -variable $SQLCMDVarArr

And I'm getting the error:

  INVOKE-SQLCMD : Incorrect syntax near 'MyDatabase'.

I pulled the syntax straight from the invoke-sqlcmd get-help examples. Can anyone tell me what is wrong with this?

Thanks,

as per request this is running on windows Server 2008 R2

Best Answer

So it turns out that the get-help invoke-sqlcmd -example shows the array variable as using 'dbname="MyDatabase"' as the syntax. However this gives the error above but if you swap the quote positions such that the variable uses "dbname='MyDatabase'" then the script works.

Related Solutions

Sql-server – SQL Agent – PowerShell step “syntax error”

This is a not very intuitive and I was never able to find anything concrete on the explanation [e.g. no exact BOL or white paper was found].

The syntax error in the SQL Agent job is a T-SQL syntax error based on User Tokens. So that basically means that a PowerShell Sub-Expression Operator is treated as a Token to SQL Server Agent. So in PowerShell this $( ) appears to be treated as reserved character sequence for SQL Server Agent. So SQL Agent would be looking for something like this T-SQL example from the BOL article referenced: PRINT N'Current database name is $(A-DBN)' ; .

So in my script when it reached ,@DriveLetter = '$($c.DriveLetter)', the "$c.DriveLetter" is not one of the tokens allowed.

The workaround to this is simply to not use sub-expression statements in PowerShell. I would make the adjustments in my script so this:


$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
    @ServerName = '$($c.ServerName)'
    ,@DriveLetter = '$($c.DriveLetter)'
    ,@DiskSpaceCapacityGB = $($c.DiskSpaceCapacityGB)
    ,@DiskFreeSpaceGB = $($c.DiskFreeSpaceGB)
"@

would have to be modified to something like this:


$severName = $c.ServerName
$driveLetter = $c.DriveLetter
$capacityGB = $c.DiskSpaceCapacityGB
$freeGB = $c.DiskFreeSpaceGB

$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
   @ServerName = '$serverName'
   ,@DriveLetter = '$driveLetter'
   ,@DiskSpaceCapacityGB = $CapacityGB
   ,@DiskFreeSpaceGB = $freeGB
"@

Made the above adjustments to my script and it runs perfectly now.

Sql-server – Connect to LocalDB via SQLCMD in PowerShell

The casing of the instance name does not matter.

You need to try:

sqlcmd -S "(localdb)\MSSQLLocalDB" -d testdb01

The "localdb" part needs to be enclosed in parenthesis as that is a special syntax that points to a SQL Server Express LocalDB-specific API that allows for automatic instance start-up upon being referenced in a connection string. The whole instance name needs to be enclosed in double quotes. The command-line shown above works for me, at least outside of PowerShell.

Best Answer

Related Solutions

Sql-server – SQL Agent – PowerShell step “syntax error”

Sql-server – Connect to LocalDB via SQLCMD in PowerShell

Related Question