Simple script help

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 6 months ago.

  • Author
    Posts
  • #6366

    by rdaleitdan at 2012-10-24 13:30:30

    I am looking at one table in a sql database. Inside here, I need to look at the email address, then copy just the username to an alias field on that same user.

    I would have to run this periodically, and the maximum amount of users will be about 2500. We have new users in daily, usually only a couple.

    Goal: I'd like to only run the script against rows that match the email criteria and that the alias is not null. The problem I have is how to put those values in. Any help?


    $SQLServer = "1.1.1.1"
    $SQLDBName = "mydatabase"
    #select only users with email matching criteria below
    $SqlQuery = "select * from users WHERE (EMAIL LIKE '%@domain.org')"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;User ID=sa;Password=#######"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)

    $DataSet.Tables[0]
    $results = $DataSet.Tables | format-table -autosize | out-string

    foreach ($row in $DataSet.Tables[0].Rows)

    {
    $emailField = $($Row[7])
    $alias = $emailField.Replace("@domain.org", "")
    }
    $SqlConnection.Close()

    by Klaas at 2012-10-25 00:25:17

    Do you mean you want to update the alias field in the table?
    That should be done with an 'UPDATE' T-SQL statement.You don't need Powershell to do that.

    [quote="rdaleitdan"]$SQLServer = "1.1.1.1"
    $SQLDBName = "mydatabase"
    #select only users with email matching criteria below
    $SqlQuery = "select * from users WHERE (EMAIL LIKE '%@domain.org')"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;User ID=sa;Password=#######"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)[/quote]This part looks OK, except that you shouldn't use 'SELECT *'. If you execute this from a machine with SQL Server installed, those 13 lines could be replaces with one 'Invoke-Sqlcmd' line.

    [quote="rdaleitdan"]$DataSet.Tables[0][/quote]Here you write the datatable to the shell, what's probably unnecessary if you're going to schedule this job to run unattended.

    [quote="rdaleitdan"]$results = $DataSet.Tables | format-table -autosize | out-string[/quote]This fills the variable $results which is not used anywhere, so why is this line here?

    [quote="rdaleitdan"]foreach ($row in $DataSet.Tables[0].Rows)

    {
    $emailField = $($Row[7])
    $alias = $emailField.Replace("@domain.org", "")
    }[/quote]In this loop you overwrite the $alias variable every iteration but nothing else changes. The $dataset is not updated, neither is the SQL table or anything else. What exactly do you want to happen with the $alias variable? Update the SQL table? Or do you want to update the alias property in an Active Directory or anywhere else?

You must be logged in to reply to this topic.