question

Mario2286-5314 avatar image
0 Votes"
Mario2286-5314 asked MotoX80 commented

Include more servers in Powershell scripts

I m new to Powershell and I need guidance on this Powershell scripts below. I m working in multi tenant environment. I need to execute a scripts on 15 servers and every servers have around 20 databases. This scripts help me a lot to execute a scripts in all database and output the results for every single database. I need to include more servers in $instancelist but when I add more servers it does not work. I need help to include more servers and this scripts must execute in all servers in $instancelist and the the output file must have server name, currently the output file have $path name, database name.log, it must have server name if I m able to include more servers.

One more thing, can I include BCP in this powershell scripts to output the results since it will come out with millions of rows

 $InstanceList = "DESKTOP-6U9IKQD" $filepath = "C:\b1\script1.sql" $path = "C:\b1\jack"
    
 $databases = invoke-sqlcmd -ServerInstance $InstanceList -Database "master" -Query "select name from sys.databases where name like 'adventureworks%'"
    
 foreach ($database in $databases) { $DBname = $database.name # Outputs one file per database $outfile = $path + "."+$database.name+".log" #Execute scripts Invoke-Sqlcmd -ServerInstance ${InstanceList} -Database $database.name -InputFIle $filepath | out-file -filepath $outfile
    
 $database.name
 }



windows-server-powershell
· 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 @Mario2286-5314 ,

could you please post your script/code in a Code sample "window" (Ctrl + K) ?
The Q&A editor sometimes cuts of some code parts if you paste the code as normal text.
This makes reading the code easier.

Thank you!


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

0 Votes 0 ·

Thank You, I have updated

0 Votes 0 ·
RichMatheisen-8856 avatar image
1 Vote"
RichMatheisen-8856 answered Mario2286-5314 commented

You need to change this line (#13 in my previous answer) from this:

 Invoke-Sqlcmd -ServerInstance ${InstanceList} -Database $database.name -InputFIle $filepath | 

To this:

 Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath | 

Sorry I missed that in my reply, :-(

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

It works very well. Thank you so much.

I need one more help @RichMatheisen-8856 . Is that possible I can tune this Powershell scripts to output one file for every one server in $instancelist and it must contain all the database and rows for that specific server using input file in Powershell scripts . Example as below file

103018-image.png


0 Votes 0 ·
image.png (83.5 KiB)

Probably the easiest way would be to create on CSV file for each server. Use the server name when you construct the fie name and then add the database name and whatever data you want from your query.

Nobody can tell you more because you haven't divulged the TSQL code in file "C:\b1\script1.sql".

In any case, this thread already has an answer. You should ask a new question using the working PowerShell code, the TSQL code, and an example of the results returned from the Invoke-Sqlcmd (just enough to expose the property names).

1 Vote 1 ·
Mario2286-5314 avatar image
0 Votes"
Mario2286-5314 answered MotoX80 commented

Still the same error

102724-image.png



image.png (146.4 KiB)
· 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.

You didn't modify all occurrences. "${InstanceList}" is not valid.

103112-capture.jpg

0 Votes 0 ·
capture.jpg (66.1 KiB)
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered Mario2286-5314 commented

This might be what you're looking for:

 $InstanceList = "DESKTOP-6U9IKQD", "DESKTOP-another", "DESKTOP-andonemore"
 $filepath = "C:\b1\script1.sql" 
 $basename = "C:\b1\jack"
    
 $InstanceList |
     ForEach-Object{
         $databases = invoke-sqlcmd -ServerInstance $_ -Database "master" -Query "select name from sys.databases where name like 'adventureworks%'"
    
         foreach ($database in $databases) {
             # Outputs one file per database
             $outfile = $basename, $database.name, "log" -join "."
             #Execute scripts 
             Invoke-Sqlcmd -ServerInstance ${InstanceList} -Database $database.name -InputFIle $filepath | 
                 out-file -filepath $outfile
    
             $database.name
         }
 }

BCP is just a command line executable so you can run it from your script.

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

Thank You Rich but i m getting this error as below

102694-image.png


0 Votes 0 ·
image.png (146.5 KiB)

I have try like this but still error

102650-image.png


0 Votes 0 ·
image.png (145.2 KiB)

Just like you have this:

 foreach ($database in $databases) {

Do the same with the SQL instance:

 Foreach ($SQLInstance in $InstanceList) {
     Invoke-SqlCmd -ServerInstance $SQLInstance   ........



0 Votes 0 ·