Solved

SQL 2005 dbmail trouble with parameters

Posted on 2011-09-27
3
257 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL USE DATABASE VARIABLE 5 30
First Max value 3 32
Accessing variables in MySQL query 4 33
MS SQL Conditional WHERE clause 3 17
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 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