When working with Lyris ListManager this week along with an existing account automation process, I ran into the issue of needing to automatically subscribing people to a list which has restrictions in place to require the use of confirmation messages generally. Well with a programmatic solution in mind, send & replying to e-mail confirmation messages just won’t cut it. Below is a simple sample of how to with PowerShell automatically enroll users into an existing ListManager list.
function ListEnrollment { param([array]$myuser) $myconn = New-Object System.Data.SqlClient.SqlConnection("Data Source=LYRIS-DBServer; Initial Catalog=Lyris; Integrated Security=SSPI") $myconn.Open() $mycmd = $myconn.CreateCommand() $mydate = Get-Date -Format "yyyy-mm-dd HH:mm:ss" $mydisplay = "$myuser[2] $myuser[3]" $mycmd.CommandText = "Insert Into members_ (Domain_,UserNameLC_,EmailAddr_,FullName_,List_,DateJoined_) VALUES ('MyDomain','$myuser[0]','$myuser[1]','$mydisplay','MyListName','$mydate')" $mylog += $mycmd.CommandText Write-Host $mycmd.CommandText $mycmd.ExecuteNonQuery() $myconn.Close() }
To call this function just use
ListEnrollment("username","email","FirstName","LastName")
This offers up a fairly simple solution to automate this potentially cumbersome manual process. As written this will use whatever credentials the PowerShell process is running to authenticate against the database.
If wanted, you can ignore the DateJoined_ and FullName_ columns as they are not required though useful. The Lyris database itself has triggers built-in upon insertion to do the heavy lifting for the rest of the required columns.
Thanks for the Powershell script for interacting with the database. That kind of function should make it easy to call shell scripts from the auto-reponders and command email addresses that interact with the db.
When doing direct insertions into the LM database (members_ table), it is necessary to make sure that Domain_ and UserNameLC_ fields are lowercased (certainly in a case sensitive database). There is an index that uses those lowercased values to prevent duplicate members from being inserted into a list.
Additionally, the listname must also be all lowercase letters, and must be able to be the valid local part of an email address (because it can be used as an email address). Periods are also not allowed in listnames, since it is used as a separator token to be able to send to subsets (segments).