Solved

Converting text to a varchar from a text cursor

Posted on 1998-09-19
6
430 Views
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.

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
Comment
Question by:kalum
6 Comments
 
LVL 7

Expert Comment

by:spiridonov
Comment Utility
> 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
 
LVL 4

Expert Comment

by:vvk
Comment Utility
In T-SQL you can't get more than 255 of text field to variable.
0
 

Author Comment

by:kalum
Comment Utility
Edited text of question
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:kalum
Comment Utility
Adjusted points to 200
0
 
LVL 2

Accepted Solution

by:
formula earned 200 total points
Comment Utility
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
 

Author Comment

by:kalum
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now