SqlCmd vs Invoke-SqlCmd issues with Export-CSV

PowerShell Help
1

Hi there, I’m new to this forum. I’m somewhat of a novice when it comes to Powershell but have been really getting into it lately. I’ve been wanting to execute some sql commands via powershell, however I’ve had some performance issues with Invoke-SqlCmd and getting the results saved to a file.

Here are my 2 lines:

Invoke-SqlCmd -ServerInstance "Server" -Database "Database" -Username "User" -Password "Password" -Query $Query | Export-Csv "Output.csv" -NoTypeInformation

SqlCmd -S "Server" -d "Database" -U "User" -P "Password" -Q $Query -o "Output.csv" -W -w 2048 -s","

Server info has been replaced with generic info for example purposes. The query and output location are exactly the same for both pieces of code.

The query returns about 12,000 rows. Here are the duration tests:

Type Duration
Invoke-SqlCmd select only 00:00:17.47
Invoke-SqlCmd + export 00:11:56:21
SqlCmd select only 00:00:15.89
SqlCmd + export 00:00:29.61
 

 

 

 

 

As you can see, exporting to csv takes a very long time for just 12,000 rows at about 25x longer then SqlCmd.Am I doing something wrong? Is it because of the pipe? Is there any way to get around this?

Kind Regards, Bailey.

2

I don’t have any experience with SQL but if you just wnat to avoid the pipeline you could try this:

$QueryResult = 
Invoke-SqlCmd -ServerInstance "Server" -Database "Database" -Username "User" -Password "Password" -Query $Query 
Export-Csv -Path "Output.csv" -NoTypeInformation -InputObject $QueryResult
3

Hi Olaf,

Unfortunately this does not work, the object that is stored is only the information about the query, not the query itself. The resulting csv looks like this:

Count Length LongLength Rank SyncRoot IsReadOnly IsFixedLength IsSynchronized
12810 12810 12810 1 System.Object[] FALSE TRUE FALSE
 

I’ve read that Export-Csv only works with pipes or single objects (like a query with 1 row for example). I’ve also tried using | Out-File but this is just as slow as piping into Export-Csv and is not in the correct format.

4

I would try it in powershell 7, it fixes several of the windows powershell speed issues.