Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: 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.



    Code:
    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)+@MessageText
    			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)+@MessageText
    			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 
    END
    Last edited by Spudhead; 07-18-02 at 11:29.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Have you tried substituing the xp_sendmail with SELECT just to see if you have formatted very thing correctly.
    MCDBA

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    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.

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    OK;

    In your script you have a line like:
    Code:
    exec master.dbo.xp_sendmail @em1,@bdy1,@sbj1
    Rewrite
    Code:
    SELECT 'master.dbo.xp_sendmail', @em1,@bdy1,@sbj1
    Do this for all xp_sendmail. This may shine some light.
    MCDBA

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    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 world’s 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?

  6. #6
    Join Date
    Jan 2002
    Posts
    189
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •