The output of your SQL is not a table but is a formatted list. Post in SQL forum to learn how to write a SQL statement that returns a table.
Here is one method:
invoke-sqlcmd -ServerInstance $instance -Query 'SELECT name, description, is_advanced FROM sys.configurations ;'
Hi Chen,
Thanks. I'll send you the ouput tomorrow i have it on my work.
Kind regards,
Andr
I'm sorry maybe my question is not clear. In the file i use SQL code that returns a table.
My question is about the export-csv that puts everyting in one column instead of my 8 colums.
I have this working in batch with sqlcmd. Only when i replace this to powershell with invoke-sqlcmd i get this issue.
Your report is not returning a table. It is returning text. The code I posted returns a dataset that can be exported to a CSV. Text cannot be exported as more than one column.
Post the SQL and I will show you why. It has to do with the usual way options are extracted. My post extracts the configuration table as a dataset.
Ok thanks i will test this tomorrow morning in the environment. Thanks for the tip.
I will keep you informed.
I have made the following script (see below), it gives me back database settings on multiple SQL servers. And it works fine. But the problem is the rows in the *.csv are using one column instead of multiple columns like the table format. Even though I use the export-csv format.
Does anybody know how to solve this?
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 foreach ($instance in Get-Content "D:\Scripts\MSSql\InputMSSqlServersProductionBorgy.txt" ){ $instance Invoke-Sqlcmd -ServerInstance $instance.ToString() -Database "MASTER" -inputfile "D:\Scripts\MSSql\DatabaseProperties.sql" | export-csv -path data.csv "D:\Scripts\MSSql\TestSQLCmd.csv" }
Hi jrv,
I have changed the query like this, but I cannot do an export-csv because I get the powershell format and when I do format-table It puts the table format in one column.
Any suggestions?
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 foreach ($Instance in Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt) { $Instance; Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT name AS 'Database_Name' , recovery_model_desc AS 'Recovery Model' , compatibility_level AS 'Compatibility Level' , collation_name AS 'Collation' , owner_sid AS 'Owner' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , page_verify_option_desc AS 'Checksum' FROM sys.databases ;" | Format-Table >> D:\Scripts\MSSql\test.csv }Kind regards, Andr
This is the export-csv format
Hi,
please use -NoTypeInformation parameter with Export-csv cmdlet.
Even with the notype information the output stays in one column, I changed the script to the following:
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 foreach ($Instance in Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt) { $Instance; Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT name AS 'Database_Name' , recovery_model_desc AS 'Recovery Model' , compatibility_level AS 'Compatibility Level' , collation_name AS 'Collation' , owner_sid AS 'Owner' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , page_verify_option_desc AS 'Checksum' FROM sys.databases ;" | Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation }
And the output is this:
Hi Aborgeld,
whether Excel automatically detects your columns is dependent on the Delimiter you use (which varies depending on which region you are in).
Try adding -Delimiter ";" to your export-Csv call.
Cheers,
Fred
#As fred suggested try the below code
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 foreach ($Instance in Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt) { $Instance; Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT name AS 'Database_Name' , recovery_model_desc AS 'Recovery Model' , compatibility_level AS 'Compatibility Level' , collation_name AS 'Collation' , owner_sid AS 'Owner' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , page_verify_option_desc AS 'Checksum' FROM sys.databases ;" | Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation -Delimeter ";" }
Pay attention.
Look closely at this code as posted:
Use this:
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 @query=@' SELECT name AS 'Database_Name' , recovery_model_desc AS 'Recovery Model' , compatibility_level AS 'Compatibility Level' , collation_name AS 'Collation' , owner_sid AS 'Owner' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , page_verify_option_desc AS 'Checksum' FROM sys.databases ; '@ Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt | ForEach-Object{Invoke-Sqlcmd -ServerInstance $_ -Database master -Query} | Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation
Your biggest issue was this:
FROM sys.databases ;" | Format-Table >> D:\Scripts\MSSql\test.csv
You cannot use Format-Table into an Export.
This works, but when I open excel I must use the text to column option - separate to get the result I want.
Is there any option to do this?
This, only I have to choose "Tekst to Colums" -> Separate change the delimiter.
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 foreach ($Instance in Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt) { $Instance; Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT name AS 'Database_Name' , recovery_model_desc AS 'Recovery Model' , compatibility_level AS 'Compatibility Level' , collation_name AS 'Collation' , owner_sid AS 'Owner' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , page_verify_option_desc AS 'Checksum' FROM sys.databases ;" | Export-CSV D:\Scripts\MSSql\test.txt -NoTypeInformation -Delimiter "," }
I tried to fix that for you but you are stubbornly inserting the same errors:
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 $query=@' SELECT name AS 'Database_Name' , recovery_model_desc AS 'Recovery Model' , compatibility_level AS 'Compatibility Level' , collation_name AS 'Collation' , owner_sid AS 'Owner' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , page_verify_option_desc AS 'Checksum' FROM sys.databases ; '@ Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt | ForEach-Object{Invoke-Sqlcmd -ServerInstance $_ -Database master -Query $query} | Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation
The next thing to look at if this still gives a single column what is you"kist separator" set to in Windows? Run the above first then check list char.
Do not add helpful looking items into the loop as it breaks the CSV.
sorry I typed -
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 $query=@' SELECT name AS 'Database_Name' , recovery_model_desc AS 'Recovery Model' , compatibility_level AS 'Compatibility Level' , collation_name AS 'Collation' , owner_sid AS 'Owner' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , page_verify_option_desc AS 'Checksum' FROM sys.databases ; '@ Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt | ForEach-Object{Invoke-Sqlcmd -ServerInstance $_ -Database master -Query $query} | Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation -UseCulture
We can also always add:
Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation -UseCulture
To the export to adjust for culture. This works in PowerShell v3 and later.