varchar truncated to 255 in SELECT in SP, SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

varchar truncated to 255 in SELECT in SP, SQL 2000

Author

Topic

shell_l_d
Starting Member

17 Posts

Posted - 2010-06-02 : 10:33:42

Hi... in a stored procedure, I can create a varchar > 255 in length & use print to get the required result, but if use select, truncates to 255 chars... any help/ideas/workarounds please?

---------------------------------------
PRINT @myvarchar -- this shows > 255chars
SELECT @myvarchar -- this TRUNCATES to 255chars
---------------------------------------

DECLARE @strWorkFlow varchar(255)
DECLARE @strWorkFlow2 varchar(255)
DECLARE @strWorkFlow3 varchar(500)

-- ALL select statements are truncating to 255 chars
SELECT @strWorkFlow
SELECT @strWorkFlow2
SELECT CAST(@strWorkFlow + @strWorkFlow2 AS TEXT)
SELECT @strWorkFlow + @strWorkFlow2
SELECT @strWorkFlow3
SELECT CONVERT(text, @strWorkFlow3)

-- these show what is expected
PRINT LEN( @strWorkFlow )
PRINT LEN( @strWorkFlow2 )
PRINT LEN( @strWorkFlow3 )
PRINT @strWorkFlow3

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 10:43:26

If you are using SSMS, check the settings here. It is defaulted to 256 characters.

Tools » Options » Query Results » Results to Text » Maximum number of characters displayed in each column

Also check the "Maximum Characters Retrieved" under "Results to Grid" section too.

Kristen
Test

22859 Posts

Posted - 2010-06-02 : 10:54:26

If this is SQL 2000 then its probably Query Analyser, rather than SSMS, but the same restriction, and Tools : Option setting applies

shell_l_d
Starting Member

17 Posts

Posted - 2010-06-02 : 19:49:39

Thanks heaps guys, that did the trick. :) :) :)

I am using SQL Query Analyser (SQL 2000). :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources