IBM Computer, Laptops and Servers

Laptop Battery This article demonstrates an email queue system using SQL Server and VB Script. This overcomes the limitations of the Sending SMTP Mail using a Stored Procedure method.

1. ZXTM GLB beats competition to make Network Computing ‘Product of the year’ short list, 30 January 2008 ... DNS (Domain Name System)-based Global Server Load Balancing system and is ... unique in that, unlike most Global Server Load Balancer products available ... built Zeus appliance, standard server s, blades, or even virtualized ... class companies such as AMD, Dell, Egenera, HP, IBM, Intel, Sun Microsystems ...

Thinkpad In a previous article I demonstrated a way to send email from a stored procedure using a COM object. This article prompted a lot of comments and discussion. From this discussion a few things became evident.

• This method was not terribly efficient. Calling COM objects like this is not particulaly fast.
• The 255 character restiction. Unfortunately in SQL Server, when calling a COM object using sp_OACreate, we are limited to 255 characters when passing in parameters to properties.
• No way to send many emails without using a cursor. This procedure had to be called for every email. This means that if you needed to send 10 emails, you would need to call it 10 times. To send an email to everyone in a table, you would need a cursor. A cursor, plus the speed of the sp_OACreate call would not make for a very efficient procedure.

Computer memory is the quickest, cheapest, and easiest way to improve the performance of your system. Find RAM memory upgrades for desktops, laptops, servers, and printers all backed by a lifetime warranty and guaranteed compatible with your computer. Shipping is an everyday low price of $1.99! Computer Memory Outlet sells memory compatible with all leading computer manufacturers like Dell, Apple, Compaq, HP, Sony, IBM, Lenovo, and many more.”

Microsoft In short, although this method is fine for say, sending alert emails to an administrator, for a large scale emailing solution it does not really work. Another way

According to the indictment, Jones would steal various IBM and Penguin computer servers from Verisign's warehouse in Virginia and sell them to Johnson. Johnson would then sell the servers to several individuals, who would sometimes place them for sale on eBay. As a result of this scheme, the indictment alleges that Jones and Johnson caused Verisign to lose more than $120, 000 worth of computer equipment. In the indictment, Jones and Johnson are charged in three counts with causing the interstate transportation of stolen property, namely IBM 330 and 335 servers, in violation of 18 U.S.C.

Laptop Computers A different approach to this problem is to put all emails into a "Queue" table. Then use a scheduled job (running every minute or so) to poll this table and send any mail in the queue.

Hypertec has been supplying businesses with memory for PCs, servers and laptops for many years. The company has built on its success in this area of PC enhancement by extending its own product portfolio to include processor and system upgrades, batteries and adapters for laptops. Quality products, supported by quality services, enable users of PC technology to maximize the functionality, performance, value and lifespan of their computers with Hypertec quality solutions!

Laptop Computer For the purpose of this article, I am going to demonstrate a simple solution for this, then outline some ways you could make it more robust. The Queue Table

Desktop Computer Firstly, create a "Mail Queue" table.

 Create Table MailQueue( MailDate DateTime Default(GetDate()), FromName VarChar(200) NOT NULL, FromAddress VarChar(200) NOT NULL, ToName VarChar(200) NOT NULL, ToAddress VarChar(200) NOT NULL, Subject VarChar(200) NOT NULL, Body VarChar(6000) NOT NULL ) 

Notebooks This is fairly straightforward. We have a timestamp column, the details of the sender and recipient of the email, the subject and the body. Getting Data In

Lenovo The next step is getting data into the table. For this we can use two methods, firstly is a single email method, second is inserting a batch of emails.

Hard Drive For a single email, this procedure will work.

 Create Procedure SendMail @FromName VarChar(200), @FromAddress VarChar(200), @ToName VarChar(200), @ToAddress VarChar(200), @Subject VarChar(200), @Body VarChar(6000) AS --Inserts mail into queue table INSERT Into MailQueue (FromName, FromAddress, ToName, ToAddress, Subject, Body) Values (@FromName, @FromAddress, @ToName, @ToAddress, @Subject, @Body) 

Travelstar Calling this procedure is easy.

 Exec SendMail 'Me', 'me@mydomain.com', 'you', 'you@yourdomain.com', 'Hi', 'The body of the message' 

Gateway Another way to get mail into the queue would be to do an insert using a select. Here is an example :

 Declare @FromName VarChar(200), @FromAddress VarChar(200), @Subject VarChar(200), @Body VarChar(200) Select @FromName = 'Me', Select @FromAddress = 'me@mydomain.com' Select @Subject = 'Hi there' Select @Body = 'The body of the message' --Insert mail into queue table INSERT Into MailQueue (FromName, FromAddress, ToName, ToAddress, Subject, Body) Select @FromName, @FromAddress, UserFirstName + ' ' + UserSurname, UserEmail, @Subject, @Body FROM Users 

Laptop Parts As you can see, this will insert a row in the Queue table for every user in the fictional Users table. Sending mail

Software O.K. we have rows in our queue table, now what ?

Hard Drives What we want to do is write a program that will get all the rows in our queue table and send an email for each one, we would typically want to run continuously. In this case, I am going to write some VBScript and run it as a SQL Server scheduled job.

Electronics I am going to write a stored procedure to get the rows out of the table. Once I have retrieved them, I want to delete them from the queue. To avoid any rows being inserted between my select and my delete, I am going to use a temp table.

 Create Procedure GetMailQueue AS Set Nocount on --Select from queue into a temp table. Then delete from Queue. --This gets around any issues with mail being inserted between select and delete Create Table #tempmail( MailDate DateTime Default(GetDate()), FromName VarChar(200) NOT NULL, FromAddress VarChar(200) NOT NULL, ToName VarChar(200) NOT NULL, ToAddress VarChar(200) NOT NULL, Subject VarChar(200) NOT NULL, Body VarChar(6000) NOT NULL ) Insert Into #tempmail Select * From MailQueue Delete From MailQueue From MailQueue M INNER JOIN #tempmail t ON t.MailDate = M.MailDate Select * From #tempmail drop table #tempmail Set Nocount Off 

Canon This procedure will return a set containing all the mails from the queue. Here is the VBScript code to retrieve the set and send it. Once again, I have used the ASPMail component from ServerObjects , but you could modify it to use CDONTS or JMail or whatever other component you use.

Desktop Pc For a complete VBScript reference go to The Microsoft Scripting site.

 '-------------------- 'ADO Constants Const adCmdStoredProc = &H0004 '-------------------- 'System config Const ConnString = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=testing; User ID=test; Password=test" Const MailServer = "mail.mydomain.com" Call Main Sub Main() dim sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody dim objCmd, objRs 'ADO Command Object Set objCmd = CreateObject("ADODB.Command") objCmd.ActiveConnection = ConnString objCmd.CommandType = adCmdStoredProc objCmd.CommandText = "GetMailQueue" 'Our Stored procedure Set objRS = objCmd.execute 'Gets an ADO recordset of all the emails do until objRs.EOF 'Loop through the emails sFromName = objRs("FromName") sFromAddress = objRs("FromAddress") sToName = objRs("ToName") sToAddress = objRs("ToAddress") sSubject = objRs("Subject") sBody = objRs("Body") 'Call our mail subroutine Call SendMail(sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody) objRS.movenext loop 'Clean up objRS.close Set objRS = nothing Set objCmd = nothing End Sub Sub SendMail(sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody) dim objMail, errCode 'Create the mail object Set objMail = CreateObject("SMTPsvg.Mailer") 'Set all the properties for this email objMail.RemoteHost = MailServer objMail.FromName = sFromName objMail.FromAddress = sFromAddress objMail.AddRecipient sToName, sToAddress objMail.Subject = sSubject objMail.BodyText = sBody 'Send it errCode = objMail.SendMail 'Clean up Set objMail = nothing End Sub 

Running It

Desktop Computers The next step is to set this script up as a job. You can do this in Enterprise Manager, schedule a script task to fire every minute. Conclusion

Think Pad There you have it, a simple mail queue system. As I said before there are ways of making it more robust but I wanted to make the example simple to understand. Some ways of making it better:

• Write an NT service in VB or C++ rather than using script.
• Rather than getting all the rows and deleting them, open up a recordset in ADO and try to send each one. If the mail doesn't succeed, mark the message as failed. Perhaps have a number of attempts until marking it as failed.
• Attachments. Add a field in the table with the path to a file to attach.

Repair I hope this example has got you thinking about other features you could incorporate. The main point of this aricle as well as the last one on mail is to remind you that there is always another way to do things, and with a little bit of lateral thinking you can come up with a solution to fit your needs.

Data Recovery The source code to these examples is here . Have fun with it.

Cisco Happy coding!

[ Comment, Edit or Article Submission ]