Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Problem changing the default subject line in xp_sendmail

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-27 : 11:10:12
Tracey writes "I get the following error error [ODBC error 170 (42000) Line 1: Incorrect syntax near 'Dingleberry'.] when adding the subject parameter to xp_sendmail. If I just leave the default subject line, it works fine.

I would appreciate any help with this frustrating problem.

Here is my code:
*****************************************************************
Fetch Next From EmailCursor Into @employeeid, @email, @Classname, @Classdate, @Location, @ClassId, @SessionId 
While @@Fetch_Status = 0
Begin
set @apostrophe = '''
set @position = CHARINDEX(@apostrophe,@className,0)

IF @position > 1
BEGIN
set @position = @position -1
set @classname = LEFT(@classname, @position) + ''' ' + Right(@classname, @position+6)

END
print @employeeID
print @classname
Select @message = 'Reminder: ' + @Classname + ' will start on ' + convert(varchar(11), @Classdate) + ' at ' + @Location + '.'

If Not Exists (Select * from tblMail_Log Where EmployeeId=@EmployeeId and ClassId=@ClassId and SessionId=@Sessionid)
Begin
set @subject = 'Class Notification'
--print @subject
--print @message
Select @CurrentDate = getdate()
exec master.dbo.xp_sendmail @email, @subject, @message
if @@Error = 0
exec usp_Mail_Insert_Log @EmployeeId, @ClassId, @Sessionid, @CurrentDate
End

Fetch Next From EmailCursor Into @employeeid, @email, @Classname, @Classdate, @Location, @ClassId, @SessionId
End

*****************************************************************"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 14:06:55
Hmmm.... there are several things you could do to get this to work better... First, I would encourage you to read some of the articles on this site that relate to email... [url]http://www.sqlteam.com/SearchResults.asp?SearchTerms=email[/url]

Next, I encourage you to avoid using cursors as much as possible. One of the articles referenced above shows a good solution ("Building a Mail Queue System").

And while I'm looking this over, it appears that you are using CHARINDEX, LEFT, and RIGHT to just get rid of apostrophes in the @classname. You might want to lookup the REPLACE function in SQL Server Books Online. It will do this easier and faster for you.

By the way, this is probably mentioned in the articles, but xp_sendmail (or SQL Server for that matter) is not really designed to do bulk mailing. If you expect the volume of email to really be large, you might have to look for another solution that can query your SQL data.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...
Go to Top of Page
   

- Advertisement -