How do you send in multiple commands to Sql PowerS

问题:

Not sure if this belongs on serverfault or not...

I am following the instructions on this site for adding registered servers to sql studio management studio via powershell. It works great one at a time, but I need to do it for 60 servers.

I have a batch set up with the code for each create that I need. I can't get the syntax right for calling sqlps by the command line and passing in the whole series of commands. My batch is set up like so:

sqlps -NoExit -Command { cd 'SQLSERVER:\sqlregistration\Database Engine Server Group\' new-item $(Encode-Sqlname server1) -itemtype registration -Value "server=server1;integrated security=true" ... and so on }

Any help is appreciated.

回答1:

If you have each individual new-item listed on a separate line in a PS1 file, for example assuming I have a file named register.ps1 with the following lines.:

cd 'SQLSERVER:\sqlregistration\Database Engine Server Group\'; new-item $(Encode-Sqlname server1) -itemtype registration -Value "server=server1;integrated security=true" 
cd 'SQLSERVER:\sqlregistration\Database Engine Server Group\'; new-item $(Encode-Sqlname server2) -itemtype registration -Value "server=server1;integrated security=true" 

You could call sqlps like this:

sqlps -NoExit -Command "&{C:\bin\register.ps1}"

A better solution would be to add parameters to the register.ps1

param($ServerInstance)

cd 'SQLSERVER:\sqlregistration\Database Engine Server Group\'
New-Item $(Encode-Sqlname $server) -itemtype registration -Value "server=$serverInstance;integrated security=true"

Then then create a file with the list of SQL Instances, for example server.txt:

server1
server2

Call register.ps1 for each line:

get-content C:\bin\server.txt | foreach {C:\bin\register.ps1 $_ }

回答2:

Put semi-colons between the commands.

回答3:

Thanks to @Chad pointing me in the right direction, I came up with this, which worked:

A batch file with multiple lines, each line looking like this:

sqlps -Command "&{ cd 'SQLSERVER:\sqlregistration\Database Engine Server Group'; new-item $(Encode-Sqlname serverX) -itemtype registration -Value \"server=serverX;integrated security=true\";}"