SQL Cursor to send email with text and query result as attachment.

I need to write a proc to send out emails - the email list is in a table. One user gets only one email regardless of how many times her/his name appears on the table (I used select distinct). I am able to write a cursor to do this successfully. But I need to modify this so that the user now not only gets an email with the general email text(@body) but also with an attachment which would be the output of a SQL query. This query is what I am having trouble with- in the email table, there are fields "Email Address" and "user id". An example of a few records in the table:
Email Address       user id
abc@123.com      1
abc@123.com      2
xyz@123.com      3
xyz@123.com      4
xyz@123.com      5
i have to query such that only one email is sent to each abc@123.com and xyz@123.com. For abc@123.com, she should get user id 1, 2 as an attachment. For xyz@123.com, she should get 3 4 5 as an attachment. How do I modify the existing cursor and/or the @query parameter to do this?
TIA for all the help..
spiroseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Brendt HessSenior DBACommented:
The simplest method to handle this would be to make the query that you need the output of reference the email list table, and filter by the email address you have in your cursor.  Modifying your query something like this:

... <your existing query here up to the WHERE statement> ...
INNER JOIN emailTable et
    ON x.[user id] = et.[user id]
WHERE et.[Email Address] = @email
    AND <rest of your existing WHERE clause if any>
0
spiroseAuthor Commented:
..........
..........

DECLARE EMAIL cursor for
       select distinct Email_Address
       from User_Email_Table
       where send_email = 1

      FOR READ ONLY
      OPEN Email

     FETCH NEXT FROM Emaill into @email
     WHILE @@FETCH_STATUS = 0
             BEGIN

                       EXEC @rc = msdb.dbo.sp_send_dbmail
                                    @profile_name = abc',
                                    @blind_copy_recipients = @email,
                                    @query =  'select userid from emailTable et INNER JOIN employeeTable x
    ON x.[user id] = et.[user id]', -------THIS WILL NOT WORK
                                    @attach_query_result_as_file = 1,
                                    @body_format = 'HTML',
                                    @body = @Message,
                                    @subject = @subject,
                                 

                FETCH NEXT FROM Email into @email
                END
         CLOSE Email
 DEALLOCATE Email
0
Brendt HessSenior DBACommented:
The basic format is close, but (1) the query output is not filtered by the @email value, and ofo course I see no output but the user_id values.  Obviously, you need to change field names to match your tables - is the value [user id] or user_id?


DECLARE EMAIL cursor for
       select distinct Email_Address
       from User_Email_Table
       where send_email = 1

      FOR READ ONLY
      OPEN Email

     FETCH NEXT FROM Emaill into @email
     WHILE @@FETCH_STATUS = 0
             BEGIN

                       EXEC @rc = msdb.dbo.sp_send_dbmail
                                    @profile_name = abc',
                                    @blind_copy_recipients = @email,
                                    @query =  'select userid from emailTable et INNER JOIN employeeTable x ON x.[user id] = et.[user id] WHERE @email = et.[Email_address]',
                                    @attach_query_result_as_file = 1,
                                    @body_format = 'HTML',
                                    @body = @Message,
                                    @subject = @subject,
                                 

                FETCH NEXT FROM Email into @email
                END
         CLOSE Email
 DEALLOCATE Email
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

spiroseAuthor Commented:
Sorry about unproper namings. Here is the correct field names, etc. Running this bit gives me error:
Error formatting query, probably invalid parameters.
Query execution failed. Must declatre the scalar variable "@email"

------
SET QUOTED_IDENTIFIER OFF

declare @email varchar(100)
    ......
.....
...


DECLARE EMAIL cursor for
       select distinct EmailAddress
       from emailTable
       where emailSent = 'False'

      FOR READ ONLY
      OPEN Email

     FETCH NEXT FROM Emaill into @email
     WHILE @@FETCH_STATUS = 0
             BEGIN

                       EXEC @rc = msdb.dbo.sp_send_dbmail
                                    @profile_name = abc',
                                    @blind_copy_recipients = @email,
                                    @query = "select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = 'False' and @email = et.[EmailAddress]",
                                    @attach_query_result_as_file = 1,
                                    @body_format = 'HTML',
                                    @body = @Message,
                                    @subject = @subject,
                                 

                FETCH NEXT FROM Email into @email
                END
         CLOSE Email
 DEALLOCATE Email
0
Brendt HessSenior DBACommented:
Sorry, that was my typo.  This would need to read:

@query = "select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = 'False' and '" + @email + "'= et.[EmailAddress]",
0
spiroseAuthor Commented:
I get a syntax error: Incorrect syntax near '+@email+'
0
spiroseAuthor Commented:
I modified this to say:
@query = "select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = 'False' and  et.[EmailAddress] = @email",.....


Now I get:
Error formatiing query, probably invalid parameters.

Must declare the scalar variable "@email"
0
spiroseAuthor Commented:
Fixed the error:

@query = "select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = 'False' and  et.[EmailAddress] = + '@email' ",
....

I get the email but it is not giving me the result of the @query ..it is blank. when I run the @query by itself it gives me 2 records...
0
spiroseAuthor Commented:
I do not think it is recognizing the value of @email since @query is actually outside the cursor..need some guidance
0
Brendt HessSenior DBACommented:
Hmmm..... let's try changing something else here.  What we are attempting to do is to create a varchar string containing the email to be filtered on embedded in the query.  To do so, we need to add the value of the @email variable into the string.

What may be causing the issue is the single-quote/double-quote problem.  In your configuration, both single and double quotes are allowed as string delimiters.  Single quotes are ANSI standard, so let us rewrite the query assignment using only single quotes:

@query = 'select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = ''False'' and ''' + @email + '''= et.[EmailAddress]', would seem to work....

Since it seems to be having an issue with a dynamically build query string, let's build the string first into another variable, and use that variable.  See below:

SET QUOTED_IDENTIFIER OFF

declare @email varchar(100)
declare @sql varchar(255)
......
.....
...


DECLARE EMAIL cursor for
      select distinct EmailAddress
      from emailTable
      where emailSent = 'False'

     FOR READ ONLY

OPEN Email

FETCH NEXT FROM Emaill into @email
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = ''False'' and ''' + @email + ''' = et.[EmailAddress]'

    EXEC @rc = msdb.dbo.sp_send_dbmail
        @profile_name = 'abc',
        @blind_copy_recipients = @email,
        @query = @sql,        
        @attach_query_result_as_file = 1,
        @body_format = 'HTML',
        @body = @Message,
        @subject = @subject

    FETCH NEXT FROM Email into @email
END
CLOSE Email
DEALLOCATE Email

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
spiroseAuthor Commented:
Perfect solution!! Thanks a bunch :)
0
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 2005

From novice to tech pro — start learning today.