Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL Server - truncated text

Options
  • 16-08-2006 2:30pm
    #1
    Drexl Spivey
    Registered Users Posts: 528 ✭✭✭


    Hi,

    I am not sure why but when I run a SQL Server query, I get all the requiered column, the last one is text (rather long) and it is truncated.

    Do I need to change my sql statement or is it due to the type of data stored in the table?

    Your help much appreciated.


Comments

  • Options
    #2
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    Are you using query analyzer to run the sql? If so your Maximum characters per column setting may be too short. It's in Tools -> Options -> Results tab.


  • Options
    #3
    Drexl Spivey
    Registered Users Posts: 528 ✭✭✭Drexl Spivey


    Hey,

    Thanks for the tip.

    I modified the value in query analyser to 8192 (the max. allowed)

    I still get truncated text values.

    Do you think there is a way to increase the above value even more?

    Cheers,


  • Options
    #4
    Smoggy
    Registered Users Posts: 1,466 ✭✭✭Smoggy


    Does it matter that your text is truncated in sql query analyser ? its only the interface thats truncating it, your text is all there , its just only displaying 8xxx chars.


  • Options
    #5
    Drexl Spivey
    Registered Users Posts: 528 ✭✭✭Drexl Spivey


    It is not just a display issue in Query Analyser, the text is truncated.

    Hence, when I copy it into Excel, it is still truncated.

    :confused:


  • Options
    #6
    Smoggy
    Registered Users Posts: 1,466 ✭✭✭Smoggy


    Again - thats because the interface can't display more than 8xxx. if you could export it then the data will be there , copying frpom an interface that is limited to 8xxx will always give you 8xxx chars.


  • Advertisement
  • Options
    #7
    bert_man
    Closed Accounts Posts: 17 ✭bert_man


    the reason it's truncating is because it's a column of data type text which means that the data isn't contained on the same data pages for the table but a pointer to the data is stored there. If you want to get around this and check to see if the data is correct - try DTS-ing it out to a text file or excel and you'll see the full range of characters gets outputted


  • Options
    #8
    Drexl Spivey
    Registered Users Posts: 528 ✭✭✭Drexl Spivey


    I exported the reult to a flat file and xls file.

    I still get the same truncated text (it is truncated sooner in the text file )

    :(


  • Options
    #9
    Smoggy
    Registered Users Posts: 1,466 ✭✭✭Smoggy


    so what your saying is that you think that the text is truncated in sql server itself ?


  • Options
    #10
    evilhomer
    Registered Users Posts: 1,464 ✭✭✭evilhomer


    Sounds like you are using the wrong datatype for the amount of data you wish to store.

    http://www.databasejournal.com/features/mssql/article.phpr/2212141

    Is the complete list of SQL Server 2000 datatypes.

    Type text is the longest as far as I know and it's 2147483648 characters long. If thats not long enough you will need BLOBS http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=true

    If you are using type text already then you can see if your text has been truncated by doing

    SELECT length(text_field) FROM table_with_text

    This will return the length of the text in the text_field for all rows in that table. Just see if the number is what you expect to see.


Advertisement