Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    57

    Unanswered: Select returs truncated text data...

    Hello,

    I have a table with the following structure for which I execute a SELECT statment against to retrieve the value of a particular NoteText field. Every time I execute the select the Notes are truncated in the returned recordset and I cannot get it to return the full value. I have tried various options, Cast/Convert, Set TEXTSIZE, etc but to no avail. Advice?

    CREATE TABLE [dbo].[SOOrderNote_T] (
    [SOOrderNote_TSK] [int] NOT NULL ,
    [SOOrder_TSK] [int] NOT NULL ,
    [PXNoteTypeSK] [int] NOT NULL ,
    [NoteText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    --My Select which returns truncated notes
    Select NoteText From SOOrderNote_T where SOOrder_TSK = XYZ

    Thanks in advance!

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    AFAIK there is only two options for you: Either do it clientside, or move to SQL Server 2005 and use the new datatype varchar(max) instead. You cannot only select from a text/ntext/image column, they will be truncated.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    parityah,
    Are you doing this in Query Analyzer? If so, I'd bet you still have the output option set to limit text width to 256 characters. Go through the menu system and bump it up to 2000 or so.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2003
    Posts
    57
    Quote Originally Posted by blindman
    parityah,
    Are you doing this in Query Analyzer? If so, I'd bet you still have the output option set to limit text width to 256 characters. Go through the menu system and bump it up to 2000 or so.

    Hi,

    Yes this was the problem in QA but I have the problem on the client side as well.... In this case we are using Excel to connect to the query for analysis and it still truncates @256.

    Any idea?

    Thanks!

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by parityah
    Hi,

    Yes this was the problem in QA but I have the problem on the client side as well.... In this case we are using Excel to connect to the query for analysis and it still truncates @256.

    Any idea?

    Thanks!
    If I recall correctly, Excel has a limit of 256 characters per cell. Is this what you are referring to?

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Oct 2003
    Posts
    57
    Quote Originally Posted by hmscott
    If I recall correctly, Excel has a limit of 256 characters per cell. Is this what you are referring to?

    Regards,

    hmscott
    Well... I can type more than 256 characters in the Excel cell but it seems like the connection to SQL Server is limited to importing 256.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So, this looks like an Excel issue rather than an SQL issue. Your SQL statement and the server's response are not the problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2003
    Posts
    57
    Quote Originally Posted by blindman
    So, this looks like an Excel issue rather than an SQL issue. Your SQL statement and the server's response are not the problem.
    Hi BlindMan,

    Thanks for the help, up until you told me about the Max Characters setting in QA I thought it was a SQL problem.

    Thanks agian.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •