Solved

Select 12000 characters from sql text

Posted on 2013-01-17
3
503 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

777 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