Post reply

TEXT DATA TYPE

  • August 27, 2002 at 9:55 am

    #173327

    Hi

    (SQL SERVER 7)

    I am trying to put together data that needs to be written out to a text file. I've got a stored procedure (SP_GENERATE_FILE_DATA) for this purpose. Obviously the data may exceed 8000 characters, so I reverted to using the TEXT data type. Problem is retreiving the TEXT from the stored procedure.

    Here's an example of my procedure:

    CREATE PROCEDURE SP_GENERATE_FILE_DATA

    @FILE_DATA TEXT OUTPUT

    AS

    CREATE TABLE #TEMP (FILE_DATA TEXT)

    INSERT INTO #TEMP

    VALUES('TEST DATA FOR FILE')

    SELECT

    @FILE_DATA = FILE_DATA

    FROM #TEMP

    DROP TABLE #TEMP

    (NOTE: This is very cryptic and a lot more updates will be done on the FILE_DATA field to generate the text file data)

    When I run: EXEC SP_GENERATE_FILE_DATA ''

    I get the following error:

    "The assignment operator operation cannot take a text data type as an argument."

    Any suggestions will be appreciated.

    Thanks

    JOE

  • August 27, 2002 at 10:45 am

    #435449

    Can't use a text datatype as an output parameter. Return it as a result set.

    Steve Jones

    sjones@sqlservercentral.com

    http://qa.sqlservercentral.com/columnists/sjones

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 27, 2002 at 3:35 pm

    #435484

    Actually, Steve based on BOL documentation you can, however I never figured out how to get it to work as you cannot use it in an assigment of value. MS also has never to my knowledge provided an example that actually works with it either.

    Now as for doing this however, in yuor situation you could output SELECT FILE_DATA FROM #TEMP and it technically returns the data like a variable, you just required a #TEMP table to do it. If you need to piece together several items from sources then take a look at the example I did here.

    http://qa.sqlservercentral.com/forum/topic.asp?TOPIC_ID=3829&FORUM_ID=65&CAT_ID=1&Topic_Title=Parameter%20value%20longer%20than%208000%20characters&Forum_Title=Performance%20Tuning

    This is the best solution I have been able to come up with.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • August 28, 2002 at 2:37 pm

    #435556

    Thanks guys, I managed to find a workaround.

    It is difficult to understand though that MS have provided the TEXT datatype but no clear indication of how to use it as output from a stored procedure.

    Surely that would be one of the most valuable characteristics of this datatype?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply