SQL Server Performance Forum – Threads Archive

Problem with cursor fetching the record

Hello I am writing a procedure which has a cursor. this cursor fetches records from one table and enter them into another. the source table has two fields with datatype "ntext" Currently in the procedure the the variable for those two fields r of datatype varchar(30) while executing the procedure gives error
"Cursor Fetch: Implicit conversion from data type ntext to varchar is not allowed."
If i decalre those two variable as ntext inside procedure .
It gives error while compiling the procedure as
"The text, ntext, and image data types are invalid for local variables." I have also tried convert and cast but it dosent complie. pls tell me some solution for this thanks in Advance Pallavi

---

>>this cursor fetches records from one table and enter them into another. Isntead of cursor why dont you use this? Insert into yourTable(columns) Select columns from OtherTable Madhivanan Failing to plan is Planning to fail

---

1. Avoid cursors if possible.
2. Why do you use ntext column if nvarchar(30) is sufficient? Do you actually need substring of ntext column value?
3. Have you tried substring function?

---

thanks for the replies but we cant avoid cursors. as the data from source table ia going into more than one tables the source tables are created by using a dts package, they r converted from paradox to sqlserver. changin the column datatype will be the last choice. but i dont want to go for it mmarovic : can u pls tell me how i can use substring funtion to solve my problem Pallavi

---

You still don’t need a cursor to achieve that.
Insert into table1(…) select … from source
insert into table2(…) select … from source
Why? Ntext makes processing much slower and more complex.
declare @var varchar(30)
select @var = substring(textColumn, 1,30) from source
Paste ntext data type, retrieving to BOL index.

---

]]>