Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Generate/Email report from MS SQL database for multiple users

Posted on 2006-11-10
10
Medium Priority
?
641 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

721 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