question

CharlieRix-8893 avatar image
0 Votes"
CharlieRix-8893 asked CharlieRix-8893 commented

Invoke-Sqlcmd -ServerInstance parameter not accepting variable

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

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered CharlieRix-8893 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Ian

Yeah using the "using:" scope modifier to access the variable works and fixes the error I was getting.

Thanks for this!

Charlie

0 Votes 0 ·
IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieRix-8893 avatar image
0 Votes"
CharlieRix-8893 answered CharlieRix-8893 commented

Hello Ian

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

85725-image.png


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

85726-image.png



Thanks

Charlie


image.png (50.1 KiB)
image.png (13.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

I noticed the PSComputerName in your error message. Did you run the command with Invoke-Command? Can you help to post more of your script?

0 Votes 0 ·

Hey

Here is the complete part of the code.


85843-image.png



Thanks

Charlie

0 Votes 0 ·
image.png (63.3 KiB)