Solved

Generate/Email report from MS SQL database for multiple users

Posted on 2006-11-10
10
618 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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

773 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