Solved

Select 12000 characters from sql text

Posted on 2013-01-17
3
513 Views
Last Modified: 2013-01-23
The column datatype in the SQL table is 'text'. The application allows 12000 characters to be saved in that column of the SQL table. Now, I am trying to print a report with all the 12000 characters from the table. Below is a subquery which is part of my stored procedure that prints the report.

SELECT substring(txtnotes,1,5000) FROM company WHERE intcomp_id=256
This works.

SELECT substring(txtnotes,1,12000) FROM company WHERE intcomp_id=256
This throws the error:
Expression result length exceeds the maximum. 8000 max, 12000 found

If I simply read the text as I know the application would not save more than 12000 character in the table,
SELECT txtnotes FROM company WHERE intcomp_id=256
This throws error:
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

Is there a way to select first 12000 characters from a SQL column of datatype text?
0
Comment
Question by:Angel02
3 Comments
 
LVL 9

Assisted Solution

by:joaoalmeida
joaoalmeida earned 200 total points
ID: 38788327
Try

SELECT substring(txtnotes,1,8000) + substring(txtnotes,8001,12000) FROM company WHERE intcomp_id=256

Regards,

Joao
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 300 total points
ID: 38788359
the maximum number it can show is 8092 characters, beyongd that its not possible to see using the sql server client tools (eventhough it can return the entire string back to the application, so you best bet is to run that query from your application
0
 

Author Comment

by:Angel02
ID: 38789014
Looks like it.
When I run the query Joao mentioned or a query to show more than 7000 characters, I get the error:

Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.

(I am using SQL Server 2000). I am not sure why 7000 and not 8092

The subquery is part of a stored procedure that populates crystal reports. It is not feasible to run the query from the application.
So should I just confirm that I cannot print more than 7000 characters on the report?
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

830 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