none
help, no success using any method of sending this complex string to file RRS feed

  • Question

  • I am using powershell to build sql to manipulate data in tables. it works but instead of copy/paste the output into a file from the console, I want to output to a file. The output line includes strings and variables containing datetime etc. the strings are the sql code and the variables are data that has to be quoted for sql.  example:

    If
     ( $J_start -eq "IS NULL" ) {
    Write-Host act_start $eekwulS $s_start AND act_end $eekwulE "'$s_end'" AND finish_status = "'$finish_status'" AND sla_id = "'$sla_id'"
    }
    Elseif
     ( $J_end -eq "IS NULL" ) {
    Write-Host act_start $eekwulS "'$s_start'" AND act_end $eekwulE $s_end AND finish_status = "'$finish_status'" AND sla_id = "'$sla_id'"
    }
    Else
     {
    Write-Host act_start $eekwulS "'$s_start'" AND act_end $eekwulE "'$s_end'" AND finish_status = "'$finish_status'" AND sla_id = "'$sla_id'"
    }
    If ( $LR -lt $load_data.Count) {
    Write-Host OR
    }
    }

    example of console output:

    select * from [ITO_SLA_QA].[dbo].[SLA_Actual] WHERE
    act_start  =  '11-06-2013 00:00:15.000' AND act_end  =  '11-06-2013 02:07:45.000' AND finish_status = 'G' AND sla_id = 'BSLA140000'
    OR
    act_start  =  '11-06-2013 03:00:17.000' AND act_end  IS NULL AND finish_status = 'R' AND sla_id = 'BSLA140001'
    OR
    act_start  =  '11-06-2013 02:30:21.000' AND act_end  =  '11-06-2013 03:50:07.000' AND finish_status = 'G' AND sla_id = 'BSLA140002'

    Thanks



    • Edited by MrkHzlwd Thursday, December 12, 2013 3:41 PM
    Thursday, December 12, 2013 3:28 PM

Answers

All replies

  • Do not use write-host. Write-host sends output to the console only. Use write-output instead.

    Bill

    Thursday, December 12, 2013 3:30 PM
    Moderator
  • You can use a double-quoted here string in conjunction with the $() sub-expression operator in order to avoid a lot of escaping- Additionally you can also easily add literal text and have variables expanded outside the sub-expression operator.:

    $sql=@"

    some static text $(If( $J_start -eq "IS NULL" ) { "act_start $eekwulS $s_start AND act_end $eekwulE '$s_end' AND finish_status = '$finish_status' AND sla_id = '$sla_id'" } Elseif( $J_end -eq "IS NULL" ) { "act_start $eekwulS '$s_start' AND act_end $eekwulE $s_end AND finish_status = '$finish_status' AND sla_id = '$sla_id'" } Else{ "act_start $eekwulS '$s_start' AND act_end $eekwulE '$s_end' AND finish_status = '$finish_status' AND sla_id = '$sla_id'" } If ( $LR -lt $load_data.Count) { "OR" }) "@ $sql




    • Edited by Dirk_74 Thursday, December 12, 2013 5:43 PM
    • Proposed as answer by Dirk_74 Friday, December 13, 2013 3:39 PM
    Thursday, December 12, 2013 5:16 PM
  • Thanks Dirk, I will try that.

    Friday, December 13, 2013 9:42 PM
  • I tried write-host but it wouldn't swallow the list of strings and variables without an error.  Perhaps using both suggestions here is the answer.  Thank you.
    Friday, December 13, 2013 9:43 PM
  • I tried write-host but it wouldn't swallow the list of strings and variables without an error.  Perhaps using both suggestions here is the answer.  Thank you.

    You cannot use Write-Host.  You need to just output the stings as Dirk demonstrated:
    This is what you want. There is no Write-Host.

    $sql=@"
    
            some static text
    	$(If( $J_start -eq "IS NULL" ) {
    		"act_start $eekwulS $s_start AND act_end $eekwulE '$s_end' AND finish_status = '$finish_status' AND sla_id = '$sla_id'"
    	}
    	Elseif( $J_end -eq "IS NULL" ) {
    		"act_start $eekwulS '$s_start' AND act_end $eekwulE $s_end AND finish_status = '$finish_status' AND sla_id = '$sla_id'"
    	}
    	Else{
    		"act_start $eekwulS '$s_start' AND act_end $eekwulE '$s_end' AND finish_status = '$finish_status' AND sla_id = '$sla_id'"
    	}
    	If ( $LR -lt $load_data.Count) {
    		"OR"
    	})
    "@
    $sql
    
    

    In the end the variable $sql wil contain the built string.


    ¯\_(ツ)_/¯

    Friday, December 13, 2013 10:59 PM
  • Are you trying to execute the SQL with Write-Host?

    You need to have a database connection nd use a database command to execute SQL.

    $cmd.CommandText=$sql

    You then need to execute the command with a table or a reader.

    The only thing you are doing is generating the text of a SQL statement.


    ¯\_(ツ)_/¯


    • Edited by jrv Friday, December 13, 2013 11:04 PM
    Friday, December 13, 2013 11:03 PM
  • jrv

    Yes I am only trying to build the sql. I already have worked out how to execute sql from powershell. I get flat txt files from an app server that I use to load the database by running a poweshell script from the task scheduler. I also want to input those files into powershell and generate code for specific purposes, for instance deleting all rows from a tainted load file.

    Thanks, Mark

    Tuesday, December 17, 2013 9:28 PM
  • jrv

    Yes I am only trying to build the sql. I already have worked out how to execute sql from powershell. I get flat txt files from an app server that I use to load the database by running a poweshell script from the task scheduler. I also want to input those files into powershell and generate code for specific purposes, for instance deleting all rows from a tainted load file.

    Thanks, Mark

    There is a program called BCP that loads and cleanses data.

    Your statement is confusing.  You want to input what files?  We isert bulk data into SQLServer using BCP or using the SQLServer Bolk data caoomands or objects.

    All-in-all it is very hard to understand what you are asking.  The script suggestions above give you the SQL from the Here-String.


    ¯\_(ツ)_/¯

    Tuesday, December 17, 2013 9:34 PM
  • Are you just asking how to output a string from PowerShell to a file?

    $sql | Out-File c:\file.sql

    That's all.


    ¯\_(ツ)_/¯

    Tuesday, December 17, 2013 9:35 PM