Link to home
Create AccountLog in
Avatar of grantballantyne
grantballantyneFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

Avatar of chapmandew
chapmandew
Flag of United States of America image

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
Avatar of grantballantyne

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
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer