Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Select 12000 characters from sql text

Posted on 2013-01-17
3
Medium Priority
?
549 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 800 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 1200 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

721 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