SQL Server Help

Any one know how to copy data from a text col to an image col?

I've tried using WRITETEXT but am unable to create local variables big enough to hold what may be in a text col.

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16), @txtcol varchar(8000)
SELECT @ptrval = TEXTPTR(imagecol) , @txtcol = proseDesc
FROM aTable where aTable_link = 2
WRITETEXT aTable.imagecol @ptrval @narr
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO

Yo
Tuesday, May 18, 2004

that would be

WRITETEXT aTable.imagecol @ptrval @txtcol

Yo
Tuesday, May 18, 2004

My guess is that you will have to instatiate some type of stream.  If you Google for Sql Server Stream it should have a few articles to get you started, though those seem more targetted at getting data out of, or into, Sql Server.

CF
Tuesday, May 18, 2004

try something like this(modified from http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=u1nfE5jeDHA.1820%40TK2MSFTNGP10.phx.gbl )



--create table BlobTable (BlobID int, ImageData image, TextData text primary key (BlobID))
DECLARE
    @BlobID int,
    @TextDataSize int,
    @CurrentTextDataOffset int,
    @ImageDataPointer binary(16),
    @CurrentImageData varbinary(8000)

DECLARE BlobData CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT
        BlobID,
        DATALENGTH(TextData)
    FROM BlobTable
OPEN BlobData
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM BlobData INTO @BlobID, @TextDataSize
    IF @@FETCH_STATUS = -1 BREAK

    -- use regular UPDATE for initial chunk
    UPDATE BlobTable
    SET ImageData = CAST(SUBSTRING(TextData, 1, 8000) AS varbinary(8000))
    WHERE BlobID = @BlobID
    IF @TextDataSize > 8000
    BEGIN
        -- append remaining data using UPDATETEXT
        SELECT @ImageDataPointer = TEXTPTR(ImageData)
        FROM BlobTable
        WHERE BlobID = @BlobID

        SET @CurrentTextDataOffset = 8000

        WHILE @CurrentTextDataOffset < @TextDataSize
        BEGIN
            SELECT
                @CurrentImageData = CAST(SUBSTRING(TextData, @CurrentTextDataOffset + 1,8000) AS varbinary(8000)),
                @CurrentTextDataOffset = @CurrentTextDataOffset + 8000
            FROM BlobTable
            WHERE BlobID = @BlobID
            UPDATETEXT BlobTable.ImageData @ImageDataPointer NULL 0 @CurrentImageData
        END
    END
END
CLOSE BlobData
DEALLOCATE BlobData

Tuesday, May 18, 2004

Try asking at www.sqlteam.com

Phibian
Tuesday, May 18, 2004

  Recent Topics

  Fog Creek Home