troubleshooting Question

Using SQL 2005 Database Mail

Avatar of grantballantyne
grantballantyneFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server 2005SQL
3 Comments1 Solution1441 ViewsLast Modified:
Hi Experts

We have recently upgraded to SQL 2005 from 2000.  We previously had a SQL job that run once a day to email the results of a table (please see code attached.  Subsequent to upgrading to SQL 2005 (which now uses database mail) we get the following error when trying to execute the job.  - "Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid"

Can anyone advise how to amend this to execute the job successfully.  We have a mail profile set up called 'BMCSQL' which works when testing sending mail.

Thanks for any help.

Grant
Declare @MyRecipients nvarchar (255)
Declare @MyMessage nvarchar (4000)
Declare @Subject nvarchar (50)
Declare @firstname char (15)
Declare @ref int
Declare @minprice money
Declare @maxprice money
Declare @area char (20)
Declare @minbedrooms char
 
Declare MyCursor Cursor For 
Select username, firstname, pRef, minprice, maxprice, area, minbedrooms
from todays_property_matches
 
 
Open MyCursor
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@minprice,@maxprice,@area,@minbedrooms
While @@Fetch_Status = 0
BEGIN
 
 
Set @MyMessage = 
'Dear ' +(convert(nvarchar(50), rtrim( @firstname) )) + '
 
A property which matches your criteria has been added to the BSPC Property Guide website. Please use the link below to view the property
 
http://www.bspc.co.uk/detailsnew.asp?pRef='  + convert(nvarchar(4),@ref) + '
 
' +
 
'Your Criteria:
Your minimum price:  '  + convert(nvarchar(15),@minprice) + '
Your maximum price:  '  + convert(nvarchar(15),@maxprice) + '
Your preferred area: '  + convert(nvarchar(50),@area)  + '
Minimum bedrooms:    '  + convert(nvarchar(50),@minbedrooms) + '
 
 
Regards
The BSPC Property Guide'
 
 
Print @MyRecipients 
EXEC msdb.dbo.sp_send_dbmail
@MyRecipients,@MyMessage,@Subject = 'BSPC Property Match'
 
 
 
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@minprice,@maxprice,@area,@minbedrooms
End
Close MyCursor
Deallocate MyCursor
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros