Varchar(8000) limit - SQL Server Forums

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

SQL Server Development (2000)

Varchar(8000) limit

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-25 : 00:08:28

Sorry i posted to the wrong category in Transact SQL. It should be here the Devloper category.

-----------------------------------------------------------

hi

I can't get this to work.

Drop Table #Mytable

create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))
insert #Mytable
select '01', 2006001, 1000, 'Acutal' union all
select 'AB001', 2006001, 1100, 'Forecast' union all
select 'AB002', 2006001, 1020, 'Forecast' union all
select '02', 2006001, 1150, 'Acutal' union all
select 'AB001', 2006002, 1200, 'Forecast' union all
select 'AB001', 2006002, 1210, 'Forecast' union all
select 'AB003', 2006003, 1110, 'Forecast' union all
select 'con', 2006002, 2000, 'Acutal'

----------------------------------------------------------------------------------------

declare @s varchar(300)
declare @s1 varchar(300)

Set @s = ''
Set @s1 = ''

If Len(@s + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']') > 300

Begin
Select @s1 = isnull(@s1 + ',' + char(13), '') + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from #Mytable where TCode like 'AB%' group by TCode, TransDa
end
else
Begin
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from #Mytable where TCode like 'AB%' group by TCode, TransDa

end

exec('select *,' + @s + ' ' + @s1 + ' from #Mytable order by TCode, TransDa')

------------------------------------------------------------

I have gone thru the link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274

Thanks

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 01:41:55

If Len(@s + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']') > 300

won't work because it does not reference any table to provide a value for TCode, TransDa etc.

Better to start with a description of the problem you are trying to solve please

Kristen

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-25 : 02:17:40

basically is to find out whether it exceed the varchar length and if does it will continue to the next declare variable:
----------------------------------------------------------

create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))
insert #Mytable
select '01', 2006001, 1000, 'Acutal' union all
select 'AB001', 2006001, 1100, 'Forecast' union all
select 'AB002', 2006001, 1020, 'Forecast' union all
select '02', 2006001, 1150, 'Acutal' union all
select 'AB001', 2006002, 1200, 'Forecast' union all
select 'AB001', 2006002, 1210, 'Forecast' union all
select 'AB003', 2006003, 1110, 'Forecast' union all
select 'con', 2006002, 2000, 'Acutal'

----------------------------------------------------------------------------------------

declare @s varchar(300)
declare @s1 varchar(300)

Set @s = ''
Set @s1 = ''

Select @s = isnull(@s + ',' + char(13), '') + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'from #Mytable where TCode like 'AB%' group by TCode, TransDa

If Len(@S) > 290

Begin
Select @s = isnull(@s + ',' + char(13), '') + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from #Mytable where TCode like 'AB%' group by TCode, TransDa
end
else
Begin
select @s1 = isnull(@s1 + ',' + char(13), '') + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from #Mytable where TCode like 'AB%' group by TCode, TransDa

end
Print Len(@s)
Print Len(@s1)
exec('select *,' + @s + ' ' + @s1 + ' from #Mytable order by TCode, TransDa')

------------------------------------------------------------------

Thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-25 : 03:52:16

I know basic syntax but not the deeper things. Any help please. Thanks

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 04:41:06

"basically is to find out whether it exceed the varchar length and if does it will continue to the next declare variable"

Sorry, but that isn't the problem you are trying to solve, that is the brick wall you have hit with your proposed solution!

Its the overall problem I need to understand please.

Kristen

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-25 : 05:09:27

hi

Ok. I have a large table with 600,000 rows of record and when i run this demo code: presuming @s is delare as varchar(8000). This code below is a simulation of the situation.

create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))
insert #Mytable
select '01', 2006001, 1000, 'Acutal' union all
select 'AB001', 2006001, 1100, 'Forecast' union all
select 'AB002', 2006001, 1020, 'Forecast' union all
select '02', 2006001, 1150, 'Acutal' union all
select 'AB001', 2006002, 1200, 'Forecast' union all
select 'AB001', 2006002, 1210, 'Forecast' union all
select 'AB003', 2006003, 1110, 'Forecast' union all
select 'con', 2006002, 2000, 'Acutal'

declare @s varchar(300)

Select @s = isnull(@s + ',' + char(13), '') + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'from #Mytable where TCode like 'AB%' group by TCode, TransDa

Print Len(@s)
exec('select *,' + @s + ' ' + @s1 + ' from #Mytable order by TCode, TransDa')

------------------------------------------------------------

The length of @s is 7423 in my real table. So, right now i am trying to solve the limit of varchar(8000) if not as the number of rows keep increasing, i would definitely hit the 8000 limit.

I went thru the post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274 but after trying many times on my PC. I still get error. I guess i am not that creative in solving script problem.

I can still further explain if you need to.Thanks.

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 05:59:13

OK, now I can see what you are trying to do

You are trying to make a cross tab

I suggest you have a look at

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Dynamic+Cross,Tabs,Pivot+Table,,Pivot+Tables

The solution to the wall you have hit is:


create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))
insert #Mytable
select '01', 2006001, 1000, 'Acutal' union all
select 'AB001', 2006001, 1100, 'Forecast' union all
select 'AB002', 2006001, 1020, 'Forecast' union all
select '02', 2006001, 1150, 'Acutal' union all
select 'AB001', 2006002, 1200, 'Forecast' union all
select 'AB001', 2006002, 1210, 'Forecast' union all
select 'AB003', 2006003, 1110, 'Forecast' union all
select 'con', 2006002, 2000, 'Acutal'

DECLARE @SQLDynamic1 varchar(8000),
	@SQLDynamic2 varchar(8000),
	@SQLDynamic3 varchar(8000)

SELECT @SQLDynamic3 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN @SQLDynamic2 ELSE @SQLDynamic3 END,
       @SQLDynamic2 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN @SQLDynamic1 ELSE @SQLDynamic2 END,
       @SQLDynamic1 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN '' ELSE @SQLDynamic1 END,
       @SQLDynamic1 =  isnull(@SQLDynamic1 + ',' + char(13), '') + ' case when TCode Like ''' + TCode + 
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' + 
TCode + '-' + cast(TransDa as varchar(15)) + ']'from #Mytable where TCode like 'AB%' group by TCode, TransDa

exec('select *,' + @SQLDynamic3 + ' ' + @SQLDynamic2 + ' ' + @SQLDynamic1 + ' from #Mytable order by TCode, TransDa')

DROP TABLE #Mytable 
GO

Add more @SQLDynamic9999 vairables as necessary to contain the overall size. The assumption is that the string generated for each iteration will NOT exceed 1,000 characters (the difference between the 8,000 character declaration and the 7,000 size test). Adjust if required ....

Kristen

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-25 : 09:34:25

Thank you so much.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-25 : 10:19:58

Or you could build the string up in a text column of a single row temp table. Then use dynamic sql to split up the string into variables and execute it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources