TSQL - HowTo? CURSOR & TEXT datatype? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TSQL - HowTo? CURSOR & TEXT datatype?

Author

Topic

Frederick Volking
Starting Member

9 Posts

Posted - 2003-05-28 : 14:22:15

My first time using TEXT datatype. And I need to use it inside a cursor ...
what am I doing wrong?

CREATE PROCEDURE LoadNaratives AS
    DECLARE @EventNarrative varbinary(16)
    DECLARE EventCursor CURSOR FOR SELECT EventNarrative FROM tblEvent
    OPEN EventCursor
    FETCH NEXT FROM EventCursor INTO @EventNarrative
    WHILE (@@fetch_status <> -1)
         BEGIN
              IF (@@fetch_status <> -2)
                   BEGIN
                        INSERT myTestTable(MyTextCol) VALUES(@EventNarrative)
                   END
              FETCH NEXT FROM EventCursor INTO @EventNarrative
         END
    CLOSE EventCursor
    DEALLOCATE EventCursor
RETURN

Execute Error = "Operand type clash: varbinary is incompatible with text"

... and attempt to do this ....
DECLARE @EventNarrative text

generates the error
The text, ntext, and image data types are invalid for local variables.

Any help would be appreciated.

P.S. Please don't critique my use of a cursor for so trivial a task

I've removed about 100 lines of TSQL from the above. The "real" process is very complex

and involves several transactions.

1fred
Posting Yak Master

158 Posts

Posted - 2003-05-28 : 15:55:46

This may help you... it is from BOL

Using text in row
In Microsoft SQL Server 2000, users can enable a text in row option on a table so it could store text, ntext, or image data in its data row.

To enable the option, execute the sp_tableoption stored procedure, specifying text in row as the option name and on as the option value. The default maximum size that can be stored in a row for a BLOB (binary large object: text, ntext, or image data) is 256 bytes, but values may range from 24 through 7000. To specify a maximum size that is not the default, specify an integer within the range as the option value.

text, ntext, or image strings are stored in the data row if the following conditions apply:

text in row is enabled.

The length of the string is shorter than the limit specified in @OptionValue

There is enough space available in the data row.
When BLOB strings are stored in the data row, reading and writing the text, ntext, or image strings can be as fast as reading or writing character and binary strings. SQL Server does not have to access separate pages to read or write the BLOB string.

If a text, ntext, or image string is larger than the specified limit or the available space in the row, pointers are stored in the row instead. The conditions for storing the BLOB strings in the row still apply though: There must be enough space in the data row to hold the pointers.

For more information, see sp_tableoption.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-28 : 21:33:47

The problem is that a text column appears in the table definition with a length of 16 bytes. These 16 bytes are NOT data, they are a pointer to the data. You cannot fetch that pointer into a variable and address it as you would a C type pointer. The only method you can use to fetch text data is to use the READTEXT command. Additionally, you cannot declare a variable as a text datatype, nor can you manipulate a text variable, and therefore you cannot read all of the data from a text column in one operation unless it is less than 8000 characters. You need to look in Books Online under READTEXT and WRITETEXT for more information.

Additionally, while you wish no criticism of your cursor, neither should you disbelieve us when we say it's possible that you don't need a cursor at all. You should post the entire code of the "real" process and let us take a look at it. If you've spent any time on SQL Team you'll know that we've taken a lot of crappy cursors and turned them into golden, set-based SQL. You're unlikely to improve your code if you choose to believe otherwise.

Frederick Volking
Starting Member

9 Posts

Posted - 2003-05-29 : 07:28:48

Well ... I spent one of my "Technical Support Incidents" and called Microsoft. Here's what Microsoft 2nd tier help said. He re-wrote my above sample.
-------------------------------
Hello Fred,
I have attached a script that will demonstrate how to insert (via UPDATETEXT) rows of text into a column that is of data type ‘text’. Note that it inserts data from a source table that contains a data type of ‘text as well.

-- Ensure that the database involved has the 'select into/bulkcopy' option turned on.
EXEC sp_dboption 'test', 'select into/bulkcopy', 'true'
go
--Create the tblEvent table contains the text data from an access memo field.
create table tblEvent
(EventID int NOT NULL,EventNarrative TEXT)
go
-- Place some dummy data in it.
-- The key is to retrieve a pointer (placed into @ptrval) to use a a reference to insert that row of
-- data that contains a text column.
INSERT INTO tblEvent VALUES (1,'')
BEGIN TRAN
DECLARE @ptrval VARBINARY(16)
SELECT @ptrval = TEXTPTR(EventNarrative)
FROM tblEvent
WHERE EventID = 1
WRITETEXT tblEvent.EventNarrative @ptrval 'this data could be residing in an access table'
COMMIT
go
INSERT INTO tblEvent VALUES (2, '')
BEGIN TRAN
DECLARE @ptrval VARBINARY(16)
SELECT @ptrval = TEXTPTR(EventNarrative)
FROM tblEvent
WHERE EventID = 2
WRITETEXT tblEvent.EventNarrative @ptrval 'this could be more data residing in an access table'
COMMIT
go

-- Create myTestTable as a SQL Server table that has one TEXT column (it could have 2 or more).
create table myTestTable
(idcol int, MyTextCol text)
go

-- Create a procedure that will load the data from the Access table.
CREATE PROCEDURE LoadNaratives AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Declare a variable to use to trace the current id to use for determining
-- the current text pointer.
DECLARE @idctr int

DECLARE EventCursor CURSOR FOR SELECT EventID FROM tblEvent
OPEN EventCursor
FETCH NEXT FROM EventCursor INTO @idctr
WHILE @@FETCH_STATUS = 0
BEGIN
-- The key is to retrieve a pointer to use a a reference to insert that row of
-- data that contains a text column.
INSERT INTO myTestTable VALUES (@idctr, '')
BEGIN TRAN
-- Get the pointer from the source table (tblEvent)
DECLARE @srcptrval VARBINARY(16)
SELECT @srcptrval = TEXTPTR(EventNarrative)
FROM tblEvent
WHERE EventID = @idctr
-- Get the pointer from the destination table (myTestTable)
DECLARE @ptrval VARBINARY(16)
SELECT @ptrval = TEXTPTR(MyTextCol)
FROM myTestTable
WHERE idcol = @idctr
UPDATETEXT myTestTable.MyTextCol @ptrval NULL NULL tblEvent.EventNarrative @srcptrval
COMMIT
FETCH NEXT FROM EventCursor INTO @idctr
END
CLOSE EventCursor
DEALLOCATE EventCursor
RETURN
GO
-- Call the stored procedure to test it.
exec LoadNaratives

Frederick Volking
Starting Member

9 Posts

Posted - 2003-05-29 : 07:29:59

I responded to Microsoft Tech Support with .....
---------------------------------------------------

The sample code actually does three reads and two writes? (1) a read for FETCH NEXT FROM (2) an INSERT to pre-populate the new record (3) a SELECT to place the new record into buffer (4) another SELECT to put the incoming text into buffer and finally (5) an UPDATETEXT to re-write the new record with the new text value. Scooters! Not very elegant and definitely not speedy.

Any special reason you wrap in a transaction and force SET TRANSACTION ISOLATION LEVEL READ COMMITTED? Just habit to prevent dirty reads? Or some special reason connected to TEXT datatypes? Or maybe UPDATETEXT?

The sample UPDATETEXT command uses NULL for insert_offset. This is only possible because the destination was pre-populated with an empty ‘’ string. Right? If I wanted to replace any existing data, I would specify an insert_offset of 0(zero).

The sample UPDATETEXT command uses NULL for delete_length. Which will replace everything from the insert_offset point to the end of any existing data. Right?

This could get ugly for my application because a few of those ignorant Access tables actually have 4 and 5 memo fields … sigh …

If there’s not a more elegant solution (until Yukon) I’ll work with this approach (smile). Give me a half-day to ferret out any problems then you can close the case.

---------------------------------------------------

I will post his answer to my above questions when I get it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources