question

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

Powershell command to have separate column in output file

I m using this Dbatools powershell command to select a table from two same database in both instance as below

Query: SELECT TOP (1000) [name]
,[age]
,[where]
FROM [AdventureWorks2017].[dbo].[table11]

Command:
Invoke-DbaQuery -ServerInstance $InstanceList -Query $sql -AppendServerInstance | Out-File C:\b2\output1.txt

and the results I get is this

74644-output22.txt


for every server in the list, I need it to have separate column name , I m supposed to get results as below

74732-output23.txt

Appreciate your expertise to advice me what need to add in the scripts

sql-server-generalwindows-server-powershell
output22.txt (756 B)
output23.txt (966 B)
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.

IanXue-MSFT avatar image
1 Vote"
IanXue-MSFT answered Mario2286-5314 commented

Hi,

Does this work for you?

 $servers = 'DESKTOP-6U9IKQD','DESKTOP-6U9IKQD\bm'
 $result = Invoke-DbaQuery -ServerInstance $InstanceList -Query $sql -AppendServerInstance
 foreach($server in $servers){
     $result | Where-Object {$_.ServerInstance -eq $server} | Out-File -FilePath C:\b2\output1.txt -Append
 }

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



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

I m getting this error below

80117-image.png


0 Votes 0 ·
image.png (87.4 KiB)

The closing single quotation mark at the end of $sql is missing.

0 Votes 0 ·

Thank you, i missed that. Now i have different error

79990-image.png


0 Votes 0 ·
image.png (113.1 KiB)

I think the parameter should be "-SqlInstance", not "-ServerInstance".
https://docs.dbatools.io/#Invoke-DbaQuery

1 Vote 1 ·

It works , Thank you so much.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered Mario2286-5314 commented

Use the function SERVERNAME

 SELECT TOP (1000) [name]
 ,[age]
 ,[where]
 ,@@SERVERNAME
 FROM [AdventureWorks2017].[dbo].[table11]
· 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.

Thank you but this is not the results I want.

0 Votes 0 ·
Mario2286-5314 avatar image
0 Votes"
Mario2286-5314 answered

Any idea will be helpful to me

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.