Converting text to a varchar from a text cursor

Posted on 1998-09-19
Last Modified: 2010-03-19
We are trying to use string functions on information we retreive with a cursor. The problem is that the cursor is pulling information from a text column. We need to convert the information we pull from the text cursor into a varchar so that we may us the string functions on it. We can easily convert a single text entry into a varchar using the select and convert function but it seems to be a totally different case when trying to do this with a cursor. Hopefully we are just missing the obvious, but if anyone has a solution and if you could provide a detailed example we would be very appreciative. Thank you.

(This is the cursor call, incidents.description is a text column)
declare incident_cursor cursor for
select incidents.description from incidents
where incidents.accountid = 185
open incident_cursor
declare @testing varchar(255)

(This fetch statement yields the following error)
fetch next from incident_cursor into @testing

Msg 16922, Level 16, State 1
Cursorfetch: Variable type of '@testing' does not match that of selected column in the cursor

I have tried many other ways of running this query and they all yield similiar results. If someone was able to do a cursor query on their own system and convert the results into a varchar and submit their method I would be eternally grateful.
Question by:kalum
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 1090198
> but it seems to be a totally different case when trying to do > this with  a cursor.

Why? can you post an example of the problem?


Expert Comment

ID: 1090199
In T-SQL you can't get more than 255 of text field to variable.

Author Comment

ID: 1090200
Edited text of question
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 1090201
Adjusted points to 200

Accepted Solution

formula earned 200 total points
ID: 1090202
I successfully did the following on my system:

declare test_cursor cursor for
select convert(varchar(255),#test_comp.Comp_Name) from #test_comp
where #test_comp.Comp_Code = "A1"
open test_cursor
declare @test_comp_name varchar(255)
fetch next from test_cursor into @test_comp_name

Looks like you need to convert the field to varchar(255) in the cursor definition "select" as follows:

select convert(varchar(255),incidents.description) from incidents where incidents.accountid = 185

Please let me know if this works on your system.


Author Comment

ID: 1090203
Dear Formula, that worked excellent.
Thank you very much for taking the time to solve our dilemna.
Here is the final revision that worked perfectly:

declare test_cursor cursor for
select convert(varchar(255),incidents.description) from incidents
where incidents.incidentid = 1
open test_cursor
declare @test_comp_name varchar(255)
fetch next from test_cursor into @test_comp_name
select @test_comp_name
close test_cursor
deallocate test_cursor


Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question