Invoke-Sqlcmd -ServerInstance parameter not accepting variable

Question

Hello

Seem to be having a issue where the -ServerInstance parameter of the Invoke-Sqlcmd command does not like a variable I am passing it e.g. Invoke-Sqlcmd -ServerInstance $SQLInstance -Query "EXEC sp_helpsrvrolemember 'sysadmin'" however if I manually type the server instance e.g. SERVER\INSTANCE it works. When I print the variable out, it prints out exactly the same when I type it in manually e.g. SERVER\INSTANCE. I have tried making the variable a string variable, a PSObject etc but no luck. Anyone have any ideas?

Cheers

Charlie

Answer 1

Hi,

As $SQLInstance is defined outside of the scriptblock, you have to use the "using:" scope modifier to access the variable.

Invoke-Command -ComputerName $ServerName -ScriptBlock {Invoke-Sqlcmd -ServerInstance $using:SQLInstance -Query "EXEC sp_helpsrvrolemember 'sysadmin'"}  

Or you can simply run Invoke-Sqlcmd without Invoke-Command

Invoke-Sqlcmd -ServerInstance $SQLInstance -Query "EXEC sp_helpsrvrolemember 'sysadmin'"  

Best Regards,
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello Ian

Thanks for replying. The full error message I am getting is below:

See output below of the $SQLInstance.GetType() command which is a string type variable:

Thanks

Charlie

Answer 3

Hi,

I tried Invoke-Sqlcmd -ServerInstance $SQLInstance and it worked well for me. Can you post the full error message? What does $SQLInstance.GetType() return?

Best Regards,
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.