CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2000
Andrey Baulin
Andrey Baulin

Reputation: 679

SQL Server 2000: returned string truncated after 256th symbol

I have a query like this:

declare @tbl varchar(1024)
declare @clmn varchar(1024) 
declare @sql nvarchar(1024)

set @tbl = 'table1'
set @clmn = 'column1'

select top 1
'select (case when exists (select column_name from ' + name + '.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = ''' + @tbl + ''') then 1 else 0 end),
(case when exists (select column_name from ' + name + '.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = ''' + @tbl + ''' and COLUMN_NAME=''' + @clmn + ''') then 1 else 0 end)'
From master.dbo.sysdatabases

On SQL Server 2008 it returns full text of generated query, but in Query Analyzer on SQL Server 2000 resulting string is truncated after 256th symbol. Is there a way to enlarge this resulting string?

Upvotes: 0

Views: 97

Answers (2)

ewahner
ewahner

Reputation: 1189

Just going to go out on a limb and state that maybe your settings are set to stop @ 256 characters?

enter image description here

Upvotes: 2

Philip Kelley
Philip Kelley

Reputation: 40309

Assuming you are referring to the returned text being displayed in the results window--with output being sent to TEXT, not GRID, then this is probably a feature of Query Analyzer.

First check, revise yourquery to

SELECT @sql = ' <etc>

Without top 1, this should drop one returned string into the variable. Follow that with

PRINT len(@sql)

to see how many characters are actually being returned. (Or maybe datalength, for number of bytes, since it's nvarchar.)

It's been a long time since I got to work with SQL 2000, but if my memory is playing straight, it has the same or a very similar feature to what's in SSMS 2005 and up. In these system, got to the Tools menu, select Options, then "drill down" to Query Results / SQL Server / Results to Text. One of the controls on this panel is "Maximum number of characters displayed in each column", with a spinner that defaults to [I forget] and allows you to set it up to 8192 (which I set it to and left at years ago). I am reasonably confidant that a similar if not identical setting was in Query Analyzer as well.

Upvotes: 2

Related Questions