SqlCmd vs Invoke-SqlCmd issues with Export-CSV

Welcome Forums General PowerShell Q&A SqlCmd vs Invoke-SqlCmd issues with Export-CSV

Viewing 3 reply threads
  • Author
    Posts
    • #238775
      Participant
      Topics: 1
      Replies: 1
      Points: 17
      Rank: Member

      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.

      • This topic was modified 2 weeks, 3 days ago by bailey125. Reason: Table was in a weird format
      • This topic was modified 2 weeks, 3 days ago by bailey125. Reason: Table was in a weird format again
    • #238880
      Participant
      Topics: 5
      Replies: 2384
      Points: 6,066
      Helping Hand
      Rank: Community MVP

      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
      
    • #238919
      Participant
      Topics: 1
      Replies: 1
      Points: 17
      Rank: Member

      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.

    • #239129
      Participant
      Topics: 3
      Replies: 431
      Points: 1,533
      Helping Hand
      Rank: Community Hero

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

Viewing 3 reply threads
  • You must be logged in to reply to this topic.