Solved

SQL 2005 dbmail trouble with parameters

Posted on 2011-09-27
3
259 Views
Last Modified: 2012-05-12
I am using sp_send_dbmail within a cursor, but cannot set parameters based on query results unless the result returns only a single value.

(parameter declarations)


declare curs1 cursor for    

SELECT distinct [email] from DB.dbo.table88
where email is not null

   
open curs1  
   
fetch next from curs1 into @email
while @@fetch_status = 0    
begin    
                   
              
                     set @bodytext = (SELECT bod from DB.dbo.settings) + '
                    ' + (SELECT [name] + '        '+[dates] FROM DB.dbo.view2
                          WHERE [semail] = @email)
            
      set @subjectline = (SELECT sub from DB.dbo.settings)
      
      
      EXEC msdb.dbo.sp_send_dbmail @recipients= @email, @body=@bodytext,
@subject = @subjectline, @profile_name = 'profileA'        
       
   
   fetch next from curs1 into @supemail
end    
close curs1    
deallocate curs1




this works as desired but only if the 2nd query in the @bodytext parameter returns a single result (because subqueries can only have single results).

any ideas to make this work with multiple results?
0
Comment
Question by:RustyZ32
[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
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36710810
what do you want it to do with multiple results?

how do you want the body formatted in that case?

why are you using a cursor for this?

can you not send to all the users at once ....

format a comma separated to list or use the cc/bcc options?


normally i'd expect something like this

 set @bodytext=''
 Select @bodytext =  @bodytext+' '
                     + [name] + '        '+[dates]
   FROM DB.dbo.view2
 WHERE [semail] = @email
 order by [dates]

set @bodytext= (SELECT bod from DB.dbo.settings) + ' '+@bodytext
0
 
LVL 6

Author Comment

by:RustyZ32
ID: 36710926
I am using a cursor because there can be up to 50 or recipients, and each recipient has to recieve a different set of results (based on who works under them).

for the recipients with multiple people working under that have entries in view2 I need the body to list each:

usera     9/12/2011
userb     9/23/2011

I know the @query parameter in sp_send_dbmail would be nice for this but apparently you cannot pass parameters to it from outside the SP


I tried your suggestion, it works but I will have to tweak the spaces and returns to make it display right.  
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36711960
you need to add char(10)+char(13)   crlf to add line breaks at the appropiate places...

(maybe the other way around 13+10)

@query
no you have to form the sql string completly and pass it to dbmail... you cant concatenate the variables on the exec...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 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