none
Running sql script using PS with delimiter

    Question

  • Hi im currently running an sql script using powershell across different server. And would like the result to have a delimiter "|". How can i add it on my current script below. Apologies but im a begginer in using ps scripting.

    thanks for your help in advance.

    $QueryPath= "C:\Users\\Desktop\access\SQLQuery.sql"

    $OutputFile = "C:\Users\\Desktop\access\MercuryAccess4.csv"

    $ExecuteQuery= Get-Content -path $QueryPath | out-string

    "Results -- > `r`n`r`n" > $OutputFile

    FOREACH($server in GC "C:\Users\\Desktop\access\SQLInstance4.txt")

    {

    $server

    "---------------------------------------------------------------------------------------------------------" >> $OutputFile

    $server >> $OutputFile

    "---------------------------------------------------------------------------------------------------------" >> $OutputFile

    invoke-sqlcmd -ServerInstance $server -query $ExecuteQuery -querytimeout 65534 | ft -autosize | out-string -width 4096 >> $OutputFile

    }

    Thursday, June 14, 2018 12:33 AM

All replies

  • First I recommend that you learn basic PowerShell.   Copying code without any understanding of the code will only waste time.

    $query= Get-Content -path C:\Users\Desktop\access\SQLQuery.sql
    Get-Content C:\Users\Desktop\access\SQLInstance4.txt |
        ForEach-Object{
            invoke-sqlcmd -ServerInstance $_ -query $query
        } |
        Export-Csv C:\Users\\Desktop\access\MercuryAccess4.csv -NoTypeInformation -Delimiter '|'
    

    You cannot create a CSV from text output.  You must export objects and not text.


    \_(ツ)_/

    Thursday, June 14, 2018 12:48 AM
    Moderator
  • Thank you for your advice. I'm planning to get some training with powershell. Nyway i tried running the script but im having error with.

    Cannot convert system.object[] to the type system.string required by parameter query. Specified method is nit supported.

    Invoke-sqlcmd -serverinstance $_ -query  $query.


    Thursday, June 14, 2018 5:56 AM
  • If you have a multiline query then just do this:

    $query= Get-Content -path C:\Users\Desktop\access\SQLQuery.sql -Raw

    No more array.


    \_(ツ)_/

    Thursday, June 14, 2018 6:03 AM
    Moderator
  • Got it. Thank you very much.

    One last favor to ask how can we separate it or group by per server results. Eg

    Server1 

    Result1 result 2....

    Server 2

    Result1 result 2...

    Thursday, June 14, 2018 8:19 AM
  • You cannot group data in a CSV file.  It will break the file.  Use a new file for each server or add the server name to the query.  All servers have a meta column with the sever name, he instance name or the db name.


    \_(ツ)_/

    Thursday, June 14, 2018 8:25 AM
    Moderator