Using SQL 2005 Database Mail

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

Open in new window

Microsoft SQL Server 2005SQL

Last Comment

chapmandew

8/22/2022 - Mon

chapmandew

4/15/2008

You need to look at these articles...you have to make sure DBMail is setup properly before you can change your code successfully:

http://articles.techrepublic.com.com/5100-9592_11-6161839.html
http://articles.techrepublic.com.com/5100-10878_11-6164310.html

grantballantyne

4/15/2008

ASKER

Hi

Thanks for the links. Im sure the DBMail has been set up correctly and the tests are working. I have amended my code slightly (see below) and I am now receiving the error message -

Msg 119, Level 15, State 1, Line 43
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Thanks again

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
@profile_name = 'BMC',
@MyRecipients,
@MyMessage,
@Subject = 'BSPC Property Match'
 
 
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@minprice,@maxprice,@area,@minbedrooms
End
Close MyCursor
Deallocate MyCursor

Open in new window

ASKER CERTIFIED SOLUTION

chapmandew

4/15/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes