• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

Converting text to a varchar from a text cursor

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.

Examples:
(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.
0
kalum
Asked:
kalum
1 Solution
 
Victor SpiridonovCommented:
> 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?

0
 
vvkCommented:
In T-SQL you can't get more than 255 of text field to variable.
0
 
kalumAuthor Commented:
Edited text of question
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
kalumAuthor Commented:
Adjusted points to 200
0
 
formulaCommented:
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.

0
 
kalumAuthor Commented:
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

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now