Solved

Converting text to a varchar from a text cursor

Posted on 1998-09-19
6
451 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
[X]
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
6 Comments
 
LVL 7

Expert Comment

by:spiridonov
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?

0
 
LVL 4

Expert Comment

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

Author Comment

by:kalum
ID: 1090200
Edited text of question
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:kalum
ID: 1090201
Adjusted points to 200
0
 
LVL 2

Accepted Solution

by:
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.

0
 

Author Comment

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

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parsing this XML works but the other one doesn't 9 28
IF SQL Query 12 29
Need help separating values from a column and creating a new record 6 45
SQL- GROUP BY 4 23
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

749 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