varchar & char variables max size

Question

varchar & char variables max size

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 1

Well, my SA's tell me we're on SP3a. Then again, they're not the brightest bulbs in the bunch.

Besides, Noeld, this is from the cursor to dynamic SQL conversion thread you helped with a couple of days ago, remember? You even warned me to watch out for string lengths...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Prove him wrong .

Select @@version

what's the compatibility level?

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 3

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Ninja's_RGR'us

SSC Guru

Points: 294069

July 14, 2005 at 9:13 am

#574443

SP3a is 8.00.760 you're late .

noeld SSC Guru Points: 96590 More actions · Answer 5

This is the site that Bkelly use to check version number and SP/Hotfix Levels

Looks handy to keep around

* Noel

fcallawa Mr or Mrs. 500 Points: 531 More actions · Answer 6

Actually Remi,

There were 70 tables that qualified for the query, and the total bytes was about 7200. None of which helps to explain why The variable is being limited to 4000 bytes.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Bingo :

SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS ColName, dbo.systypes.name AS DateType, dbo.systypes.xtype

FROM dbo.systypes INNER JOIN

dbo.syscolumns ON dbo.systypes.xtype = dbo.syscolumns.xtype INNER JOIN

dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id

WHERE (dbo.sysobjects.id = 1) AND (dbo.syscolumns.name = N'name')

sysobjectsnamenvarchar231

sysobjectsnamesysname231

The name column of SysObjects is SysName (NVarchar)

Declare @sql as varchar(8000)

select @sql = ''

-- select len(@SQL)

-- select datalength(@SQL)

select @sql = @sql + 'Create View vw_' + CAST(name as varchar(128)) + char(10) +

'AS' + char(10) +

'select * ' + char(10) +

'from dbo.' + CAST(name as varchar(128)) + char(10)

from dbo.sysobjects

where xtype = 'U'

order by name

print @sql

print len(@SQL)

--8000

fcallawa Mr or Mrs. 500 Points: 531 More actions · Answer 8

Thanks Remi, that works. Has sysname always been an Nvarchar datatype? I don't remember running into this problem before and I have been doing this for a number of years now.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

It is on my machine. Maybe it has something to do with the default collation of the server (but wouldn't make sens in my case). I think that the most likely explaination is that it's the first time you go over 4000K characthers when you do that.

noeld SSC Guru Points: 96590 More actions · Answer 10

>> Has sysname always been an Nvarchar datatype? <<

YES it always have been nvarchar(128). SQL Server 7 and up are designed to support unicode named objects

Cheers!

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Thanx for the confirmation.. i'll try to remember that next time I encounter this .