Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: SPROC cannot pass text or ntext

    Hi there! I've read through several of these forums and am impressed by the talent out there. I'm hoping someone could share some insight on a little complication I've got.

    I have a sproc that essentially is pulling all mail items from a profile, reading them in and storing them in a table. It is all working fine MINUS the fact that I cannot pass the message because it won't allow me to pass text or ntext.

    If anyone has any ideas I would truly appreciate it. In the meantime I'll be searching through the forum looking for a way to make it happen.
    Thx, Camey

    Here's my code:

    CREATE PROCEDURE USP_News_GETmail
    AS

    declare @status int
    declare @msg_id varchar(64)
    declare @originator varchar(255)
    declare @cc_list varchar(255)
    declare @msgsubject varchar(255)
    declare @msgtext varchar(255)
    declare @messages int
    declare @resultmsg varchar(80)
    declare @filename varchar(12)
    declare @current_msg varchar(64)

    declare @post_id int
    declare @topic_id int
    declare @forum_id int
    declare @poster_id int
    declare @post_time int
    declare @poster_ip int
    declare @post_username char(25)
    declare @enable_bbcode smallint
    declare @enable_html smallint
    declare @enable_smilies smallint
    declare @enable_sig smallint
    declare @post_edit_time int
    declare @post_edit_count smallint
    declare @bbcode_uid char(10)
    declare @post_subject char(60)
    declare @post_text text

    Set @msg_id = NULL
    while (1=1)
    begin
    exec master..xp_findnextmsg @msg_id = @msg_id output
    if @msg_id is null break

    exec @status = master.dbo.xp_readmail
    @msg_id,
    @originator=@originator output,
    @cc_list=@cc_list output,
    @subject=@msgsubject output,
    @message=@msgtext output

    select @topic_id = 2000,
    @forum_id = 1,
    @poster_id = 3,
    @post_time = Convert(int,getdate()),
    @poster_ip = 1000196,
    @post_username = 'yahoo-alibre',
    @enable_bbcode = 1,
    @enable_html = 0,
    @enable_smilies = 0,
    @enable_sig = 0,
    @post_edit_time = NULL,
    @post_edit_count = 0,
    @bbcode_uid = NULL,
    @post_subject = @msgsubject,
    @post_text = @msgtext




    SET IDENTITY_INSERT phpbb_test_posts ON

    DECLARE @num int
    SELECT @num=max(post_id)
    FROM phpbb_test_posts
    SELECT @num = @num + 1

    INSERT INTO phpbb_test_posts (post_id,topic_id,forum_id,poster_id,post_time,pos ter_ip,post_username,enable_bbcode,enable_html,ena ble_smilies,enable_sig,post_edit_time,post_edit_co unt)
    VALUES (@num,@topic_id,@forum_id,@poster_id,@post_time,@p oster_ip,@post_username,@enable_bbcode,@enable_htm l,@enable_smilies,@enable_sig,@post_edit_time,@pos t_edit_count)


    INSERT INTO phpbb_test_posts_text (post_id,bbcode_uid,post_subject,post_text)
    VALUES (@num,@bbcode_uid,@post_subject,@post_text)

    exec master..xp_deletemail @msg_id


    end
    GO

  2. #2
    Join Date
    Mar 2004
    Posts
    25

    Re: SPROC cannot pass text or ntext

    Originally posted by CameyP
    Hi there! I've read through several of these forums and am impressed by the talent out there. I'm hoping someone could share some insight on a little complication I've got.

    I have a sproc that essentially is pulling all mail items from a profile, reading them in and storing them in a table. It is all working fine MINUS the fact that I cannot pass the message because it won't allow me to pass text or ntext.

    If anyone has any ideas I would truly appreciate it. In the meantime I'll be searching through the forum looking for a way to make it happen.
    Thx, Camey

    I've had similar problems using text fields in Sybase. What is the error message that you are getting?

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    Acutally, the error was when checking the syntax for the sproc. I was trying to declare text fields and pass the result into a column w/ data type text. Apparently, you can't do that w/ a sproc - so instead. I left the column as data type text, but i declared my variable in the sproc as varchar(8000) and it worked out for me.

    The error that comes up when check the syntax basically states that you cannot declare text, ntext in a stored procedure. Quite a pain in the rear actually. But fortunately, I found a workaround.

    Thanks for your reply.
    Camey

  4. #4
    Join Date
    Mar 2004
    Posts
    25
    Originally posted by CameyP
    Acutally, the error was when checking the syntax for the sproc. I was trying to declare text fields and pass the result into a column w/ data type text. Apparently, you can't do that w/ a sproc - so instead. I left the column as data type text, but i declared my variable in the sproc as varchar(8000) and it worked out for me.

    The error that comes up when check the syntax basically states that you cannot declare text, ntext in a stored procedure. Quite a pain in the rear actually. But fortunately, I found a workaround.

    Thanks for your reply.
    Camey

    yeah - that's what we had to do too! (Thank heaven that it was a database which allowed varchars of longer than 255 characters! ) - glad you got sorted.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There are actually several ways how to do this, but the simplest would be to pass your @post_text as a parameter. Of course I'd question if you're actually storing the entire content of a message by doing everything in one INSERT as opposed to handling TEXT datatype using WRITETEXT/UPDATETEXT. And what about attachments? Are they being lost or you're saving them to a disk and then storing paths?

Posting Permissions

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