varchar & char variables max size
-
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...
-
Prove him wrong
.Select @@version
what's the compatibility level?
-
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: )
-
SP3a is 8.00.760 you're late
. -
This is the site that Bkelly use to check version number and SP/Hotfix Levels
Looks handy to keep around
* Noel -
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.
-
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
-
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.
-
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.
-
>> 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 -
Thanx for the confirmation.. i'll try to remember that next time I encounter this
.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply