SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 varchar truncated to 255 in SELECT in SP, SQL 2000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

17 Posts

Posted - 06/02/2010 :  10:33:42  Show Profile  Reply with Quote
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

Flowing Fount of Yak Knowledge

1836 Posts

Posted - 06/02/2010 :  10:43:26  Show Profile  Reply with Quote
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


United Kingdom
22712 Posts

Posted - 06/02/2010 :  10:54:26  Show Profile  Reply with Quote
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

Starting Member

17 Posts

Posted - 06/02/2010 :  19:49:39  Show Profile  Reply with Quote
Thanks heaps guys, that did the trick. :) :) :)

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

Edited by - shell_l_d on 06/02/2010 19:50:13
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000