current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question
add comment

3 Answers 3

up vote 3 down vote

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'"
share|improve this answer
add comment
up vote 3 down vote accepted

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.

share|improve this answer
add comment
up vote -1 down vote

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

share|improve this answer
    
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. –  silent__thought Oct 5 '09 at 20:01
add comment

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.