a1

Hi Folks

Last week  i  had a chat with my friend Raj. he want to execute a query  on multiple SQL Server.

I suggest him to create a script using CONCATENATE,But he was not satisfied with my Answer. so i decided to automate this here is the script

here is the code

requirements

create a file with list of server name or instance (C:\PowerShell\ServerList.txt)

create a .sql file with query which needs to be executed(C:\PowerShell\SQLScript.sql)

This  script can be used to create jobs on multiple server

 

script

Import-Module SQLPS -DisableNameChecking
$SerName = Get-Content “C:\PowerShell\ServerList.txt”
$DatabaseName = “master”
$SerName |
ForEach-Object {
$ServerObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
Invoke-Sqlcmd `
-ServerInstance $_ `
-Database $DatabaseName `
-InputFile “C:\PowerShell\SQLScript.sql”
#-Query $SQLQuery
}

a1

SQL Query Filea2

Server list

a3

 

 

7 thoughts on “Execute SQL Server Query on Multiple SQL Servers using Powershell

  1. HI Ramesh anna,
    i created powershell folder in c and created serverlist and sqlserverscript.sql files and executed.
    but it is showing error: the lable c already has been declared . lable1 must be unique
    within query batch or stored procedure.

  2. How to add an user using the query in sql server. I want add an user through Query can any one please tell me the query

  3. Hi ,
    Import-Module SQLPS -DisableNameChecking
    $SerName = Get-Content “C:\dump\ServerList.txt”
    $DatabaseName = “master”
    $SerName =”MC0XENTC” |
    ForEach-Object {
    $ServerObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
    Invoke-Sqlcmd
    -ServerInstance $_ ‘
    -Database $DatabaseName ‘
    -InputFile “C:\dump\SQLQuery1.sql”
    #-Query $SQLQuery
    }
    it is not working..

Leave a Reply

Your email address will not be published. Required fields are marked *