PowerShell – SQL scripting

Viewing 1 post (of 1 total)
  • Author
    Posts

  • Lemons
    Member
    #129507

    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 system.net.mail.smtpClient
    $MailMessage = New-Object system.net.mail.mailmessage
    $SmtpClient.Host = “smtpserver”
    $mailmessage.from = “fromemail”
    $mailmessage.To.add(“toemail”)
    $mailmessage.To.add(“secondaryemail”)
    $mailmessage.Subject = “subject”

    #Sets database connections
    $SQLSERVER=”sqlserver”
    $Database=”database”
    $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
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()

    $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 ”
    $smtpclient.Send($mailmessage)[/CODE][CODE]$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 system.net.mail.smtpClient
    $MailMessage = New-Object system.net.mail.mailmessage
    $SmtpClient.Host = “smtpserver”
    $mailmessage.from = “fromemail”
    $mailmessage.To.add(“toemail”)
    $mailmessage.To.add(“secondaryemail”)
    $mailmessage.Subject = “subject”

    #Sets database connections
    $SQLSERVER=”sqlserver”
    $Database=”database”
    $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
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()

    $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 “
    $smtpclient.Send($mailmessage)[/CODE]

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.

External Sharing and Guest User Access in Microsoft 365 and Teams

This eBook will dive into policy considerations you need to make when creating and managing guest user access to your Teams network, as well as the different layers of guest access and the common challenges that accompany a more complicated Microsoft 365 infrastructure.

You will learn:

  • Who should be allowed to be invited as a guest?
  • What type of guests should be able to access files in SharePoint and OneDrive?
  • How should guests be offboarded?
  • How should you determine who has access to sensitive information in your environment?

Sponsored by: