Solved

Select 12000 characters from sql text

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

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

630 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