Sql-server – Add header to multi server or instance query (powershell)

powershellsql server

I have the following PowerShell script I am looking to add a header to this results without having a duplicate per each iteration but I am not familiar enough with Powershell if anyone can point me in the right direction it would be helpful.

$servers = 'E:\DBA\servers.txt'
$outfile = 'E:\DBA\out.csv'
$queryfile = 'E:\DBA\query.sql'

Get-Content $servers -First 1 | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -First 1 | Out-File
$outFile -Force} Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}

The error is

Best Answer

If you just want to hard-code the header, you can use the Powershell New-Item cmdlet. I used the -Force parameter to initialize the outfile with the single header row and the rest of your script uses -Append to add the detail information.

New-Item $outfile -value "Header,Header,Header,Header`n" -Force
Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}

If you want dynamic columns generated based on the actual query, you can run an initial Get-Content using -First 1 (to only get the first server) and limit the output to the first row only by using -First 1. Then, follow up with your usual Powershell script.

Get-Content $servers -First 1 | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -First 1 | Out-File $outFile -Force}
Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}

Related Solutions

Sql-server – How to connect Powershell to the SQL Server named instance

$ServerName = "[local\s12]";

Remove the square brackets.

Apart from that, the PowerShell code looks fine (and works on my test machine - the code I used is below, copied from my SQL Server test machine SERVER1).

PS C:\Users\Administrator.ADVENTURE-WORKS> [void][reflection.aSSEMBLY]::LoadWithPartialName("Microsoft.SQLServer.Smo" )
PS C:\Users\Administrator.ADVENTURE-WORKS> $ServerName = "server1\sql2012"
PS C:\Users\Administrator.ADVENTURE-WORKS> $ServerName
server1\sql2012
PS C:\Users\Administrator.ADVENTURE-WORKS> $Srv = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server $ServerName

Additionally, is the server actually called "local" or is that just anonymised? If you want to connect to the local server then the alias is a period.

$ServerName = ".\SQL2012"

Otherwise, try to connect to the server local\s12 using SQL Server Management Studio using windows authentication from the same machine that you are running the powershell script. if that fails then there is a connectivity issue. Check firewalls (on the SQL Server machine and on any routers in between the client and server). Check that the TCP/IP network library is enabled on both the client and the server. If TLS is enabled then confirm that the certificates are all correct.

Sql-server – SQL Server Job Powershell is failing

The syntax error you are getting is from the $() this is taken as a token in SQL Server Agent context...so it will always bark at that; removing it should fix the syntax error.

On the copy issue you will likely need to specify the property you want to pass into Copy-Item. Just passing $myfile is not going to work because that contains the full object of the output from gci. So change it to something like:

$myfile = gci $mypath | sort LastWriteTime | select -last 1 -ExpandPropety FullName
Copy-Item -path $myfile -destination $mydestination

Now $myFile will contain the full path to your last file:

Best Answer

Related Solutions

Sql-server – How to connect Powershell to the SQL Server named instance

Sql-server – SQL Server Job Powershell is failing

Related Question