SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 TSQL - HowTo? CURSOR & TEXT datatype?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Frederick Volking
Starting Member

9 Posts

Posted - 05/28/2003 :  14:22:15  Show Profile  Reply with Quote
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

Canada
158 Posts

Posted - 05/28/2003 :  15:55:46  Show Profile  Reply with Quote
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.



Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 05/28/2003 :  21:33:47  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

Frederick Volking
Starting Member

9 Posts

Posted - 05/29/2003 :  07:28:48  Show Profile  Reply with Quote
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




Go to Top of Page

Frederick Volking
Starting Member

9 Posts

Posted - 05/29/2003 :  07:29:59  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000