Solved

Generate/Email report from MS SQL database for multiple users

Posted on 2006-11-10
10
615 Views
Last Modified: 2013-12-25
I have a database in a SQL server that contains email information.

I want to setup a weekly script (using VBscript with DTS?) that reads through the database and sends an email to each user with a summary of the emails they have in the database.

The database is setup like so:
CREATE TABLE [dbo].[Messages] (
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [MsgGUID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DateSent] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Sender] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Subject] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Recipients] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

So the script should read through the DB and collect the date, sender, and subject of each item in the database, assemble it into an email, and send it to the appropriate recipient.
We have around 25 different recipients that will need a summary report of their messages in the database.

I found this MSDN article that shows how to do something similar, but its a bit over my head.
http://msdn.microsoft.com/msdnmag/issues/02/08/VBScriptandSQLServer2000/default.aspx?print=true&loc=null

Any help?


0
Comment
Question by:isol8
  • 5
  • 4
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17919667
You first need to decide what email protocol you are going to use.  If it is:

1. MAPI than you may be able to use SQL Mail, if you can and are willing to install an email client on the server such as Outlook.  You will then have to configure SQL Mail.
2. SMTP (This is the simpler choice) you can install a third party email extended stored procedure such as XPSMTP, see here for downloading and help:  http://www.sqldev.net/xp/xpsmtp.htm
0
 

Author Comment

by:isol8
ID: 17930598
I'd prefer not to install an email client on the server, but I can if it is absolutely necessary.

Any ideas on the actual vbscript to use?
0
 
LVL 3

Expert Comment

by:ia2189
ID: 17933742
I had to do something similar and was able to use xp_sendmail.  I set it up in a SQL job using T-SQL so I could schedule it to run whenever I needed.  I didn't setup the SQL Mail so I can't help you there, but below is what the script could look like.  The only downfall is I don't believe you can dynamically change the sender if that's what you have to do.

declare @vMsgGUID as varchar(255)
declare @vSubject as varchar(255)
declare @vRecipeients as varchar(255)

declare tmpCursor cursor for
      select MsgGUID, Subject, Recipients
      from Messages

open tmpCursor
fetch next from tmpCursor into @MsgGUID, @vSubject, @vRecipients

while @@fetch_status = 0 begin

      exec master..xp_sendmail
       @recipients = @vRecipients,
       @subject = @vSubject,
       @message = @MsgGUID

      fetch next from crTYB into @MsgGUID, @vSubject, @vRecipients

End

close tmpCursor
deallocate tmpCursor
0
 

Author Comment

by:isol8
ID: 17934095
Seems to be working up until this point:
fetch next from crTYB into @MsgGUID, @vSubject, @vRecipients

I get this error.
Executed as user: DOMAIN\sqladmin. A cursor with the name 'crTYB' does not exist. [SQLSTATE 34000] (Error 16916)  Associated statement is not prepared [SQLSTATE HY007] (Error 0).  The step failed.

Here is the code I'm using:

declare @vMsgGUID as varchar(255)
declare @vSubject as varchar(255)
declare @vRecipients as varchar(255)

declare tmpCursor cursor for
     select MsgGUID, Subject, Recipients
     from Messages

open tmpCursor
fetch next from tmpCursor into @vMsgGUID, @vSubject, @vRecipients

while @@fetch_status = 0 begin

     exec master..xp_smtp_sendmail
      @server = smtpserv,
      @FROM = test,
      @to = @vRecipients,
      @subject = @vSubject,
      @message = @vMsgGUID

     fetch next from crTYB into @vMsgGUID, @vSubject, @vRecipients

End

close tmpCursor
deallocate tmpCursor
0
 
LVL 3

Expert Comment

by:ia2189
ID: 17934106
Sorry, it's a typo.  Change this line...

 fetch next from crTYB into @vMsgGUID, @vSubject, @vRecipients

to...

 fetch next from tmpCursor into @vMsgGUID, @vSubject, @vRecipients
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:isol8
ID: 17934202
It works now, but sends an email for every message found in the DB.  is there a way to put all of that into a single email?

So each person would get a summary, not an additional email for each item.

@message should be a list of each vRecipients and vSubject.
0
 
LVL 3

Expert Comment

by:ia2189
ID: 17934357
Sorry, I misunderstood the scenario.  I'm still not clear what you're wanting exactly.  Do you want one message with everything in the table, or do you want one message for each recipient for their respective data in the table?  Below is the code for one message with everything in the table, but you're limited to 8000 characters unless you create some sort of parsing function to create a new variable if the 8000 maximum is reached.  You could also create a #temp table to load all the table data into one field and then do a select off that field for the message.


declare @vSubject as varchar(255)
declare @vRecipients as varchar(255)
declare @vMessage as varchar(8000)

set @vMessage = ''

declare tmpCursor cursor for
      select Subject, Recipients
      from Messages

open tmpCursor
fetch next from tmpCursor into @vSubject, @vRecipients

while @@fetch_status = 0 begin

      set @vMessage = @vMessage + @vSubject + char(10) + @vRecipients + char(10) + char(10)

      fetch next from tmpCursor into @vSubject, @vRecipients

End

close tmpCursor
deallocate tmpCursor

exec master..xp_sendmail
 @recipients = '[enter email here]',
 @subject = '[enter subject here]',
 @message = @vMessage
0
 

Author Comment

by:isol8
ID: 17934433
That is close.  Each recipient in the database should get only one email with a list of all rows where they are listed as the recipient.

Here is an example of the data in the table:

MSGUID     SENDER              SUBJECT            RECIPIENT
1284          tim@hr.com          "Payroll"          john@marketing.com
1285          susan@1.com       "Meeting"         john@marketing.com
1286          george@wh.com   "Re: V1agr4"    tim@sales.com

In this case, john@marketing.com should get one email with body:

tim@hr.com          "Payroll"          john@marketing.com
susan@1.com       "Meeting"         john@marketing.com

And tim@sales.com should get an email with body:
george@wh.com   "Re: V1agr4"    tim@sales.com

0
 
LVL 3

Accepted Solution

by:
ia2189 earned 500 total points
ID: 17938095
The below code should work for you (hopefully I didn't mistype anything).  It will be it in the format of...

To john@marketing.com:

Sender: tim@hr.com
Subject: Payroll
Recipient: john@marketing.com

Sender: susan@1.com
Subject: Meeting
Recipient: john@marketing.com

To tim@sales.com:

Sender: george@wh.com
Subject: Re:V1agr4
Recipient: tim@sales.com

---------------------------------------------------

declare @vSender as varchar(255)
declare @vSubject as varchar(255)
declare @vRecipients as varchar(255)
declare @tmpRecipients as varchar(255)
declare @vMessage as varchar(8000)

declare tmpCursor cursor for
     select Sender, Subject, Recipients
     from Messages
     order by Recipients

open tmpCursor
fetch next from tmpCursor into @vSender, @vSubject, @vRecipients

set @vMessage = ''
set @tmpRecipients = @vRecipients

while @@fetch_status = 0 begin

     set @vMessage = @vMessage + 'Sender: ' + @vSender + char(10) + 'Subject: ' + @vSubject + char(10) + 'Recipient: ' + @vRecipients + char(10) + char(10)

     if @tmpRecipients <> @vRecipients begin

          exec master..xp_sendmail
           @recipients = @vRecipients,
           @subject = '[enter subject here]',
           @message = @vMessage

          set @vMessage = ''

     end

     set @tmpRecipients = @vRecipients

     fetch next from tmpCursor into @vSender, @vSubject, @vRecipients

End

close tmpCursor
deallocate tmpCursor
0
 

Author Comment

by:isol8
ID: 17940233
Works perfectly.  Thanks so much for your help.

Here is the final code that I used with xp_smtp_sendmail:

declare @vMsgGUID as varchar(255)
declare @vSubject as varchar(255)
declare @vRecipients as varchar(255)
declare @tmpRecipients as varchar(255)
declare @vMessage as varchar(8000)
declare @vSender as varchar (255)

declare tmpCursor cursor for
     select Sender, Subject, Recipients
          from Messages
     order by Recipients

open tmpCursor
fetch next from tmpCursor into @vSender, @vSubject, @vRecipients

set @vMessage = ''
set @tmpRecipients = @vRecipients

while @@fetch_status = 0 begin

    set @vMessage = @vMessage + 'Sender: ' + @vSender + char(10) + 'Subject: ' + @vSubject + char(10) + 'Recipient: ' + @vRecipients + char(10) + char(10)
   
        if @tmpRecipients <> @vRecipients begin
   
             exec master..xp_smtp_sendmail
              @server = SMTPSERV,
              @FROM = "Notify@domain.com",
              @to = @vRecipients,
              @subject = "Notification",
              @message = @vMessage
   
             set @vMessage = ''
   
        end
   
        set @tmpRecipients = @vRecipients
   
     fetch next from tmpCursor into @vSender, @vSubject, @vRecipients
End

close tmpCursor
deallocate tmpCursor
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now