Solved

Select 12000 characters from sql text

Posted on 2013-01-17
3
482 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

12 Experts available now in Live!

Get 1:1 Help Now