Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2005 dbmail trouble with parameters

Posted on 2011-09-27
3
Medium Priority
?
262 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
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

783 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