Execute SQL Script against Multiple Servers by PowerShell

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

  • 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)?

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

  • 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

  • I remember this from last year. Thanks again.

  • 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

    }

  • 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

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

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

  • 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
    ---------------------------------------------------------------------------------------------------------

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

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

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

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