Create A Stored Procedure that will send an email to ALL the people in a database with their contacts.

I am working with SQL Server 2000 and have the following Views created:

1. "vw_active_group_providers" - this view contains the following fields and data

PROVIDER_ID          FIRST_NAME          LAST_NAME          EMAIL1
128                           John                       Adams                 jadams@testemail1.com
544                           Abraham                Lincoln                 abelincoln@testemail2.com
and so on...(thousands of rows)

2. "vw_home_phone" - this view contains the following fields and data

PROVIDER_ID          FIRST_NAME           LAST_NAME           HOME         PHONE_NUMBER
128                           John                       Adams                  home          (000) 000-0000
544                           Abraham                Lincoln                  home          (111) 111-1111
and so on...(thousands of rows)

3. "vw_cell_phones" - - this view contains the following fields and data

PROVIDER_ID          FIRST_NAME           LAST_NAME           CELL         PHONE_NUMBER
128                           John                       Adams                  cell           (000) 000-0000
544                           Abraham                Lincoln                  cell           (111) 111-1111
and so on...(thousands of rows)

4. "vw_work/office" - - this view contains the following fields and data

PROVIDER_ID          FIRST_NAME           LAST_NAME           WORK/OFFICE         PHONE_NUMBER
128                           John                       Adams                  office                       (000) 000-0000
544                           Abraham                Lincoln                  office                       (111) 111-1111
and so on...(thousands of rows)

5. "vw_home_address" - - this view contains the following fields and data

PROVIDER_ID    ADDRESS_TYPE    ADDRESS_LINE1        ADDRESS_LINE2    ADDRESS_CITY     ADDRESS_STATE   ADDRESS_POSTAL_CODE
128                           home            101 Mountain Rd        NULL                       Cleveland             OH                        55432
544                           home            202 Crimson St           Apt # 343               Dallas                   TX                         75070  
and so on...(thousands of rows)

PLEASE NOTE:
 - "vw_home_address" does not have names
 - PROVIDER_ID is the only field that connects all five of the above views.


Here's what I want to achieve:

I want to create a Stored Procedure that looks at the above views and when I execute it, it should send an email to everyone in the "vw_active_group_providers" view.

The people getting the email should see the following in their email EXACTLY:

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

SUBJECT: Contact Information Verification

BODY:

As part of our ongoing effort to keep your contact information updated at all times, we request you to please verify your information below.

If your contact information has changed or been updated, please click "UPDATE CONTACT INFORMATION" link to email us with your updates.

The following contact information is registered with us:

LAST NAME, FIRST NAME
ADDRESS_LINE1
ADDRESS_LINE2 <----------//Should not display if NULL//
ADDRESS_CITY
ADDRESS_POSTAL_CODE

HOME PHONE: PHONE_NUMBER
CELL PHONE:   PHONE_NUMBER
OFFICE PHONE: PHONE_NUMBER  <----------//Should not display if NULL//

UPDATE CONTACT INFORMATION <-----------//This should be a mailto link that when clicked on sends an email to corp@corporation.com with the subject line "Please Update My Contact Information" allowing the person to send an email with any updates to his/her contacts//

Thank you,
CORP Notification System
--------------------------------------------------

So gurus, can you help me create a stored procedure to do this? And if so, how can I test it first to make sure it works?

I really appreciate it.

Thanks.
LVL 1
super786Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

super786Author Commented:
.
0
Anthony PerkinsCommented:
The following questions are now considered abandoned, please attend to them:

1 01/06/2008 500 Need a Program or Script to Search Keywords & Open Search Engine& & 
2 12/11/2007 250 Find Text within a text file, and use that to ren& Open VB Script & 
3 12/06/2007 125 Need to know how to use IF then statements Open General Offic& & 
4 08/21/2007 500 Auto-email notifications with updates and data& Open MS SQL DTS & 
5 04/09/2007 500 Import excel spreadsheet into Sql Server sche& Open Databases & 
0
super786Author Commented:
Done.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

shadowsaintCommented:
this should get you on the right track...
I would create a cursor to go line by line through the views.

Make a join that combines the data to a person level

Do all the formatting of the data within the while to get the message formatted correctly.

then at the very end execute the sp_send_dbmail

you can populate the table with an email to yourself to test, or comment out the sendmail and select from all your vars that you're passing into it with text view instead of grid view.

The code below is VERY generic lol so let me know if you need anything else

also remember the isnull() function is a very handy function :)
DECLARE @msg VARCHAR(8000),
        @sub VARCHAR(500),
        @sql VARCHAR(8000),
        @name VARCHAR(500),
        @city VARCHAR(500),
        @email VARCHAR(500)
 
DECLARE cursor_x CURSOR FOR
   SELECT name, city, email
   FROM view1 v1
   JOIN view2 v2
   ON v1.id = v2.id
   [any more joins you need]
 
OPEN cursor_x
FETCH NEXT FROM cursor_x INTO @name, @city, @email -- these are populated in the order of the select
WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @msg = '[Place your message that you want here] CHAR(13) is a   carriage return btw :)'
 
   SELECT @sub = 'subject here'
 
   EXECUTE	msdb.dbo.sp_send_dbmail @recipients = @email,								@body 	    = @msg,								@subject    = @sub
 
FETCH NEXT FROM cursor_x INTO @name, @city, @email -- Gets next person to email
END
 
        

Open in new window

0
super786Author Commented:
Ok this is what I did....I joined all the views into ONE view, and customized the stored procedure in the code snippet below.

But after I EXECUTE it, I get the following error:

Server: Msg 2812, Level 16, State 62, Line 48
Could not find stored procedure 'msdb.dbo.sp_send_dbmail1'.
Server: Msg 2812, Level 16, State 62, Line 48
Could not find stored procedure 'msdb.dbo.sp_send_dbmail1'.
Stored Procedure: CON_TEST_BLAST
      Return Code = 0

What am I missing?
DECLARE @msg VARCHAR(8000),
        @sub VARCHAR(500),
        @sql VARCHAR(8000),
        @name VARCHAR(500),
        @email VARCHAR(500),
        @address_line1 VARCHAR(500),
        @address_line2 VARCHAR(500),
        @address_city VARCHAR(500),
        @address_state VARCHAR(500),
        @address_postal_code VARCHAR(500),
        @home VARCHAR(500),
        @cell VARCHAR(500)
   
 
DECLARE cursor_x CURSOR FOR
   SELECT Last_Name+', '+first_name, email1, address_line1, address_line2, address_city, address_state, address_postal_code, home, cell
   FROM vw_CON_TEST
 
OPEN cursor_x
FETCH NEXT FROM cursor_x INTO @name, @email, @address_line1, @address_line2, @address_city, @address_state, @address_postal_code, @home, @cell -- these are populated in the order of the select
WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @msg = 'As part of our ongoing effort to keep your contact information updated at all times, we request you to please verify your information below.<br>
 
If your contact information has changed or been updated, please click the "UPDATE CONTACT INFORMATION" link to email us with your updates.<br>
 
The following contact information is registered with us:<br><br>'
 
SELECT @name = last_name+', '+first_name from vw_CON_TEST
SELECT @address_line1 = address_line1 from vw_CON_TEST
SELECT @address_line2 = address_line2 from vw_CON_TEST
SELECT @address_city = address_city from vw_CON_TEST
SELECT @address_postal_code = address_postal_code from vw_CON_TEST
SELECT @home = home from vw_CON_TEST
SELECT @cell = cell from vw_CON_TEST
  
    SELECT @msg = @msg + char(13) + char(10) + '<b>NAME:</b> ' + @name + '<br><br><b>ADDRESS:<b><br>' + @address_line1 + isnull('<br>' + @address_line2,'') + '<br>' + @address_city + ', ' + @address_state + ' ' + @address_postal_code
    SELECT @msg = @msg + char(13) + char(10) + '<br><br><b>HOME PHONE:</b> ' + @home + '<br><b>CELL:</b> ' + @cell + '<br><br>'
 
    SELECT @msg = @msg + char(13) + char(10) + '<a href="mailto:corp@corporate.com?subject=Please Update My Contact Information"><b>UPDATE CONTACT INFORMATION</b></a><br><br>Thank you,<br>CORP Notification System' 
 
 
   SELECT @sub = 'Contact Information Verification'
 
   EXECUTE	msdb.dbo.sp_send_dbmail1 @recipients = @email, @body = @msg, @subject = @sub
 
FETCH NEXT FROM cursor_x INTO @name, @email, @address_line1, @address_line2, @address_city, @address_state, @address_postal_code, @home, @cell -- Gets next person to email
END
GO

Open in new window

0
super786Author Commented:
OK - Got it working.

I created a table that stores all messages, and then created a trigger to go off if the message_status is set to 'QUEUED' - This trigger then sends emails out to any message_id's with message_status as 'QUEUED'.

The result of which becomes the following:


DECLARE @msg VARCHAR(8000),
        @sub VARCHAR(500),
        @sql VARCHAR(8000),
        @name VARCHAR(500),
        @email VARCHAR(500),
        @address_line1 VARCHAR(500),
        @address_line2 VARCHAR(500),
        @address_city VARCHAR(500),
        @address_state VARCHAR(500),
        @address_postal_code VARCHAR(500),
        @home VARCHAR(500),
        @cell VARCHAR(500),
        @intMessageID INTEGER
   
 
DECLARE cursor_x CURSOR FOR
   SELECT Last_Name+', '+first_name, email1, address_line1, address_line2, address_city, address_state, address_postal_code, home, cell
   FROM vw_CON_TEST
 
OPEN cursor_x
 
FETCH NEXT FROM cursor_x INTO @name, @email, @address_line1, @address_line2, @address_city, @address_state, @address_postal_code, @home, @cell -- these are populated in the order of the select
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @msg = 'As part of our ongoing effort to keep your contact information updated at all times, we request you to please verify your information below.<br>
 
If your contact information has changed or been updated, please click the "UPDATE CONTACT INFORMATION" link to email us with your updates.<br>
 
The following contact information is registered with us:<br><br>'
 
  
    SELECT @msg = @msg + char(13) + char(10) + '<b>NAME:</b> ' + @name + '<br><br><b>ADDRESS:</b><br>' + @address_line1 + isnull('<br>' + @address_line2,'') + '<br>' + @address_city + ', ' + @address_state + ' ' + @address_postal_code
    SELECT @msg = @msg + char(13) + char(10) + '<br><br><b>HOME PHONE:</b> ' + @home + '<br><b>CELL:</b> ' + @cell + '<br><br>'
 
    SELECT @msg = @msg + char(13) + char(10) + '<a href="mailto:corpuser@corporation.com?subject=Please Update My Contact Information"><b>UPDATE CONTACT INFORMATION</b></a><br><br>Thank you,<br>CORP Notification System' 
 
 
   SELECT @sub = 'Contact Information Verification'
 
     insert into master.owner.messages            
   (message_type, message_status, priority, sender, sender_email, send_method, subject, body)
   values
   ('EMAIL','HOLD','NORMAL','Corp User', 'corpuser@corporation.com','EMAIL', @sub, @msg)
 
   select @intMessageID = message_id from sysctrl.sysctrl.messages
   where message_id = @@IDENTITY
 
   insert into master.owner.messages  
   (message_id, recipient_type, recipient_address)
   select
    @intMessageID, 'TO', @email
 
   update sysctrl.sysctrl.messages
   set message_status = 'QUEUED'
   where message_id = @intMessageID
 
   Fetch Next from cursor_x
   into @name, @email, @address_line1, @address_line2, @address_city, @address_state, @address_postal_code, @home, @cell
 
End
 
Close cursor_x
Deallocate cursor_x
 
Set NoCount On
 
 
Return 0
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.