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.

Go to Top of Page

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
Go to Top of Page

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). :)
Go to Top of Page
   

- Advertisement -