Error converting datatypes

Hi,

This script gets run by a job every 3 mins, and it's falling over with an "Error converting varchar value... to column of datatype int", and I think it's on this line:

select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'

...where I'm trying to build up a string by dropping an ID number (datatype int) into it.

So I tried:

select @.sbj1='New ICNA Forum Post (ThreadID='+CAST(@.existingID AS varchar(100))+')'

and:

select @.sbj1='New ICNA Forum Post (ThreadID='+CONVERT(varchar(100), @.existingID)+')'

Both of these result in the job running successfully, but no emails get sent and the job history shows the error "Incorrect syntax near 'Forum'." On the good side, it's supposed to be looping through the email-sending bit 4 times (there are currently 4 users) and sure enough, it repeats that error message 4 times.

The full script follows below. I'd be hugely grateful if anyone could point out what I'm doing wrong, and how to do it right.

Cheers.

Declare @.hMessage varchar(255),@.msg_id varchar(255)
Declare @.MessageText varchar(8000),@.message varchar(8000)
Declare @.MessageSubject varchar(8000),@.subject varchar(8000)
Declare @.Origin varchar (8000), @.originator_address varchar(8000)

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN

exec master.dbo.xp_readmail
@.msg_id=@.hMessage,
@.message=@.MessageText OUT,
@.subject=@.MessageSubject OUT,
@.originator_address=@.Origin OUT

IF ((SELECT COUNT(*) FROM forum_users WHERE email = @.Origin) = 1) -- IF email from forum-recognised address
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF email has a thread ID
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.existingID)

-- Do mailing

declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1

while @.@.FETCH_STATUS=0
begin
select @.bdy1='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1

END
ELSE -- IF email has no thread ID
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500) -- Create a new thread record and use the resulting ID to add a thread_post record
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.newID)

-- Do mailing

declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2

while @.@.FETCH_STATUS=0
begin
select @.bdy2='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj2='New ICNA Forum Post (ThreadID='+@.newID+')'
exec master.dbo.xp_sendmail @.em2,@.bdy2,@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2

END
END

SET @.hMessage = NULL

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
ENDHave you tried substituing the xp_sendmail with SELECT just to see if you have formatted very thing correctly.|||Ah. Did I mention that my grasp of SQL and its debugging techniques was a little sparse?

Thanks very much for your help, but could you possibly explain how do do that?

Cheers.|||OK;

In your script you have a line like:
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
RewriteSELECT 'master.dbo.xp_sendmail', @.em1,@.bdy1,@.sbj1
Do this for all xp_sendmail. This may shine some light.|||Aha! Well, at least I've learnt how to get some debugging output. Unfortunately I'm none the wiser as to why sendmail isn't working.

It loops 4 times, once for each email address in my forum_users table. I'm sending the "trigger" email from a hotmail address (needs to be external to our network) and thus for each email it tries to send the subject and body look like:

@.sbj1:
New ICNA Forum Post (ThreadID=24)

@.bdy1:
New ICNA Forum Post: Friday morning test 1 __________________________________________________ _______________ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com

Which are pretty well exactly what I was expecting - so why on earth is it falling over? :(|||At last, got it working! It didn't like this line:

exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1

when I replaced it with:

exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1

it worked fine. I don't know why, and I don't care :) It works...