Invoke-Sqlcmd cmdlet throws exception when using -Variable parameter

Question

When I try to use the Invoke-Sqlcmd cmdlet from SQL Server 2008 to execute a query that contains scripting variables, ex. $(MyVar), I receive the following exception:

Invoke-Sqlcmd : Object reference not set to an instance of an object.

Here's the code I'm trying to run (which is copy/paste from the Books Online example with only the connection parameters added).

$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray -ServerInstance "localhost" -Database "master" -UserName "who" -Password "me"

If I replace $(MyVar1) and $(MyVar2) in the -Query with 'x' and 'y' then it runs perfectly.

$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"
Invoke-Sqlcmd -Query "SELECT 'x' AS Var1, 'y' AS Var2;" -Variable $MyArray -ServerInstance "localhost" -Database "master" -UserName "who" -Password "me"

Can anyone tell me why this is not working?

Answer 1

Try this alone: PS>$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"

Now: PS>$MyArray and PS>MyVar1

Now: PS>$MyArray|get-member

PowerShell thinks you've assigned 2 string objects to $MyArray, nothing more. This approach does not result in defining the variables $MyVar1 and $MyVar2 to PowerShell.

Sorry, I can't fire up my SQL2008 VM right now to comment on the other parts...

Answer 2

Ok. I posted this same question on the SQL Server forums and, apparently, this is a bug in SQL Server 2008's PowerShell cmdlets... follow the thread here.

Answer 3

Indeed this is a bug in SQL Server - tracked and fixed here https://connect.microsoft.com/sqlserver/feedback/details/358291/invoke-sqlcmd-powershell-cmdlet-fails-when-array-passed-via-variable

However, there's a posted workaround. Remove the spaces around the assignment. So instead of

$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"

use

$MyArray = "MyVar1='String1'", "MyVar2='String2'"