Execute SQL Script against Multiple Servers by PowerShell

Question

Execute SQL Script against Multiple Servers by PowerShell

Raushan Srivastava

Old Hand

Points: 391
More actions
August 6, 2015 at 5:50 pm

#398859

Comments posted to this topic are about the item Execute SQL Script against Multiple Servers by PowerShell

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

dave.rogers 954 SSC Enthusiast Points: 190 More actions · Answer 1

Assuming that your target audience is SQL Server capable and PowerShell newbie, I think you have omitted some key details.

The Invoke-SqlCmd PowerShell cmdlet is not an integral part of PowerShell. So how does one install it?

Your use of the Invoke-SqlCmd PowerShell cmdlet uses SQL Server Windows Authentication on every instance against which you run it. The person using this script needs to know this, but what can they do if they must use SQL Server authentication (perhaps because they have to invoke it through a firewall)?

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

August 24, 2015 at 7:12 am

#1821940

Thanks, I'll pass this on to our DBM.

jswong05 Hall of Fame Points: 3553 More actions · Answer 3

http://www.sqlsaturday.com/308/Sessions/Details.aspx?sid=17746

Jason
http://dbace.us
😛

gmercier SSC Rookie Points: 49 More actions · Answer 4

Having trouble getting this to run against named instances. Works fine for default instances. A value like EXCL1VS1\NGSDW in the server file cannot be parsed. I have tried every possible combination of quotes etc with no luck.

Gary Mercier, OCM
Senior Manager, Development
Newgistics, Inc...The leader in returns management
512-225-6059
gmercier@newgistics.com

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 9, 2016 at 6:58 am

#1877470

I remember this from last year. Thanks again.

AZJim Default port Points: 1432 More actions · Answer 6

I found this one somewhere (whoever you are, I really thank you!). Anyway, it is slightly different, but works great. For what it is worth.

#

cls

$SQLCmd = get-content "C:\PSFiles\PS1Executables\RunYourSqlFromHere.txt"

foreach ($svr in get-content "C:\PSFiles\ServerList\AllServers.txt")

{

$con = "server=$svr;database=master;Integrated Security=sspi"

$da = new-object System.Data.SQLClient.SQLDataAdapter ($SQLCmd, $con)

$dt = new-object System.Data.DataTable

$da.fill($dt) | out-null

$svr

$dt | Format-Table

}

boun.louvan Valued Member Points: 51 More actions · Answer 7

I am getting this error:

The term 'invoke-sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try agai

n.

At D:\Powershell\ExecScriptOnMultiServer\ExecuteQueryOnMultiServers.ps1:15 char:15

+ invoke-sqlcmd <<<< -ServerInstance $server -query $ExecuteQuery -querytimeout 65534 | ft -autosize | out-string -width 4096 >> $OutputFile

+ CategoryInfo : ObjectNotFound: (invoke-sqlcmd:String) [], CommandNotFoundException

+ FullyQualifiedErrorId : CommandNotFoundException

AZJim Default port Points: 1432 More actions · Answer 8

You will have to troubleshoot it by breaking it down into its parts. From the error message, it looks like you cannot execute the sqlcmd statement. To test it, open a PowerShell window and try something simple like this:

sqlcmd -S SQLDev -Q "select @@version" -E

where SQLDev is one of your servers. You should not get a failure. If you do get a failure, then it might be one of a million things. I would check your execution policy to see if you are set to RemoteSigned.

dave.rogers 954 SSC Enthusiast Points: 190 More actions · Answer 9

The Invoke-Sqlcmd cmdlet is part of SQLPS which is part of a SQL Server installation (or possibly just the SQL Server tools). Where are you running your script?

If this in not the problem then ensuring that SQLPS is "loaded" is your next obstacle. If you are running your script in a SQL Server Agent job then SQLPS should be loaded. Otherwise, your solution depends on the version of SQLPS (or possibly PowerShell). I have SQL Server 2012 and PowerShell 4 installed on my laptop so I execute the following commands:

Import-Module SQLPS -DisableNameChecking

Invoke-Sqlcmd ...

iurie.mereuta SSC Rookie Points: 29 More actions · Answer 10

Hi,

My issue is when I run the powershell script the output file is empty. The script works fine in SSMS and I get data when executing it. What can be the cause? Thanks

Results -- >

---------------------------------------------------------------------------------------------------------
SQL-A
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
NODE1
---------------------------------------------------------------------------------------------------------

Parthiban Rjendran SSC Rookie Points: 30 More actions · Answer 11

Please help me in calculating the total number of servers to be displayed on this report...

Parthiban Rjendran SSC Rookie Points: 30 More actions · Answer 12

Hi All,

I want the report with the columns HostName, SQLInstanceName, IsClustered, SQLEdition, SQLVersion.

Also on the footer of the report, I want to display the total number of Servers.