No announcement yet.

PowerShell - SQL scripting

  • Filter
  • Time
  • Show
Clear All
new posts

  • PowerShell - SQL scripting

    Guys, I'm still learning powershell and I have the following script, just wondering if this is the best way to do it.

    My basic query is about the best way to actually run the query against the db server.

    $date = (get-date -f MMM)
    #Setting file locations
    $bwfile = "D:\tfn$date.csv"
    $tempfile = "d:\tempfile$date.csv"
    $processedfile = "D:\completed$date.csv"
    #temp file to create headings 
    $header = "acc1,acc2,names,joint,held,clcode"
    $header | out-file -filepath $tempfile -encoding ascii
    start-sleep -s 2
    $changecsv = get-content $bwfile | out-file -filepath $tempfile -encoding ascii -append
    #Creates email settings
    $SmtpClient = new-object 
    $MailMessage = New-Object 
    $SmtpClient.Host = "smtpserver" 
    $mailmessage.from = "fromemail" 
    $mailmessage.Subject = “subject” 
    #Sets database connections
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    #Imports the csv file and turns it into a formatted string
    $clcode = import-csv $bwfile | foreach {"'" + $_.clcode + "'"}
    $joined = [string]::join(",", $clcode)
    #The query to run against the database
    $SqlCmd.CommandText = "select a.clcode, b.taxnumber , b.given, b.surname, a.account
    from w_extbal as a
    join w_tfn as b
    on a.clcode=b.clcode
    where a.instcode='bwa'
    and a.clcode in (" + $joined + ")
    order by a.clcode asc" 
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $DataSet.Tables[0] | format-table -auto
    #$content = $DataSet.Tables[0] | format-table -auto | out-string
    $csv = $DataSet.Tables[0] | export-csv $processedfile
    #Email message settings
    $mailmessage.Body = "Message "
    Last edited by Lemons; 31st December 2007, 02:36.