Author Posts

January 1, 2012 at 12:00 am

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?