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?
 
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
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
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
 
spiroseAuthor Commented:
Perfect solution!! Thanks a bunch :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.