Simple script help

Welcome Forums General PowerShell Q&A Simple script help

Viewing 0 reply threads
  • Author
    Posts
    • #6366
      Participant
      Topics: 1562
      Replies: 0
      Points: 1
      Rank: Member

      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?

      <br />$SQLServer = &quot;1&#46;1&#46;1&#46;1&quot; <br />$SQLDBName = &quot;mydatabase&quot;<br />#select only users with email matching criteria below<br />$SqlQuery = &quot;select * from users WHERE (EMAIL LIKE '%@domain&#46;org')&quot;</p><p>$SqlConnection = New-Object System&#46;Data&#46;SqlClient&#46;SqlConnection<br />$SqlConnection&#46;ConnectionString = &quot;Server = $SQLServer; Database = $SQLDBName;User ID=sa;Password=#######&quot;<br />$SqlCmd = New-Object System&#46;Data&#46;SqlClient&#46;SqlCommand<br />$SqlCmd&#46;CommandText = $SqlQuery<br />$SqlCmd&#46;Connection = $SqlConnection</p><p>$SqlAdapter = New-Object System&#46;Data&#46;SqlClient&#46;SqlDataAdapter<br />$SqlAdapter&#46;SelectCommand = $SqlCmd</p><p>$DataSet = New-Object System&#46;Data&#46;DataSet<br />$SqlAdapter&#46;Fill($DataSet)</p><p>$DataSet&#46;Tables&#091;0&#093;<br />$results = $DataSet&#46;Tables | format-table -autosize | out-string</p><p>foreach ($row in $DataSet&#46;Tables&#091;0&#093;&#46;Rows)</p><p>{<br />    $emailField = $($Row&#091;7&#093;)<br />    $alias = $emailField&#46;Replace(&quot;@domain&#46;org&quot;, &quot;&quot;)<br />}<br />$SqlConnection&#46;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.

      $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)

      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.

      $DataSet.Tables[0]

      Here you write the datatable to the shell, what’s probably unnecessary if you’re going to schedule this job to run unattended.

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

      This fills the variable $results which is not used anywhere, so why is this line here?

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

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

      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?

Viewing 0 reply threads
  • The topic ‘Simple script help’ is closed to new replies.