[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

Generate/Email report from MS SQL database for multiple users

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
isol8
Asked:
isol8
  • 5
  • 4
1 Solution
 
Anthony PerkinsCommented:
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
 
isol8Author Commented:
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
 
ia2189Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
isol8Author Commented:
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
 
ia2189Commented:
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
 
isol8Author Commented:
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
 
ia2189Commented:
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
 
isol8Author Commented:
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
 
ia2189Commented:
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
 
isol8Author Commented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now