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?

Scott Weinstein · Accepted Answer · 2010-10-31 20:33:45Z

5

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'"

answered Oct 31, 2010 at 20:33

Scott Weinstein

19.1k15 gold badges83 silver badges117 bronze badges

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

Comments

joshuapoehls · Accepted Answer · 2009-10-12 20:41:58Z

4

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.

answered Oct 12, 2009 at 20:41

joshuapoehls

35.7k11 gold badges54 silver badges61 bronze badges

Comments

Marco Shaw · Accepted Answer · 2009-10-02 22:52:08Z

-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...

answered Oct 2, 2009 at 22:52

Marco Shaw

1,1286 silver badges13 bronze badges

1 Comment

joshuapoehls Over a year ago

According to the Books Online example, assigning the 2 strings to the array is exactly what needs to happen since the cmdlet is expecting an array of variable assignments.

Collectives™ on Stack Overflow

Invoke-Sqlcmd cmdlet throws exception when using -Variable parameter

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related