?
Solved

excedding limit varchar(8000)

Posted on 2003-11-10
10
Medium Priority
?
2,300 Views
Last Modified: 2007-11-27
Hi,
I am having diffuclty in preparing dynamic select statemet where length exceed 8000 characters because too many columns in tables.

How can i overcome this situation..Is there anyway to retain if exceeds 8000 characters..

thnx,
Dharmesh
0
Comment
Question by:Dharmeshgandhi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 9720674
You can use a text field instead.
0
 

Author Comment

by:Dharmeshgandhi
ID: 9720764
no.
its not valid for  local variables.

thnx,
dharmesh
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 9720845
You can however use a text field as a paramter of the stored procedure and use that variable instead.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 12

Accepted Solution

by:
ill earned 80 total points
ID: 9721745
create table ##a (  id int , value varchar(7900) )
declare @text varchar(8000), @text1 varchar(8000), @col varchar(64), @tab varchar(64), @htab varchar(64), @lasttab varchar(64), @i int, @trig varchar(4000), @historyLength varchar(64)

-- just take what is needed from declaration

-- check your string while preparing and insert after every 7900 chars into ##a. id must be ascendant. then:



/****************************  EXEC LONG STRING *************/

set @text='declare @id int,@inText varchar(8000) '
set @i=0
set @count= ( select count(*) from ##a )
while @i< @count begin
      set @text= @text +', @text'+ convert( varchar(10), @i)+ ' varchar(8000)'
      set @i=@i+1
end
set @text= @text+ 'declare tmpcur insensitive cursor for select id, value from ##a order by id open tmpcur'
set @text= @text+ ' fetch next from tmpcur into @id, @inText'
set @text= @text+ ' while @@fetch_status=0 begin '
set @i=0
while @i< @count begin
      set @text= @text+ 'if @id= '+ convert( varchar(10),@i+1)+ ' begin set @text' +convert ( varchar(10), @i)+ '= @inText end '-- print @text' +convert ( varchar(10), @i)+ ' end '
      set @i= @i+1
end
set @text= @text+ ' fetch next from tmpcur into @id, @inText end close tmpcur deallocate tmpcur '
set @text= @text+' exec ('''''
set @i=0
while @i< @count begin
      set @text= @text+ '+  @text'+ convert ( varchar(10), @i)
      set @i= @i+1
end
set @text= @text+')'
exec ( @text)


i hope i  pasted it correctly
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9722236
use the
sp_executesql stored procedure to execute your dynamic sql statement...

you need to pass the statement in a set of NVARCHAR(4000)  variables

to the procedure

exec sp_executesql @Nvar1 + @nvar2 + @Nvar3 etc


...

you will eventually meet the hard limit of 64K for an sql statement however...

0
 

Expert Comment

by:MauriceBiche
ID: 12409033
This is found to be a interesting one and the one that we are currently facing while converting our database into non-unicode supported datatype (char/varchar/text) into unicode supported datatypes. (nvarchar/nchar/ntext).
This article is quite useful.
0
 
LVL 12

Expert Comment

by:ill
ID: 12409087
-- static user table & SP code i'm using currently. for nvarchar it has to be modified to smaller sizes. doesn't support multiple 8000+ at once.
create table sLongString (  
      id int ,
      value varchar(8000)
)
GO

create procedure sExecLong
AS
declare @i int, @count int,  @text varchar(8000)
set @text='declare @id int,@inText varchar(8000) '
set @i=0
set @count= ( select count(*) from sLongString )
while @i< @count begin
      set @text= @text +', @text'+ convert( varchar(10), @i)+ ' varchar(8000)'
      set @i=@i+1
end
set @text= @text+ 'declare longTextcur insensitive cursor for select id, value from sLongString order by id open longTextcur '
set @text= @text+ ' fetch next from longTextcur into @id, @inText'
set @text= @text+ ' while @@fetch_status=0 begin '
set @i=0
while @i< @count begin
      set @text= @text+ 'if @id= '+ convert( varchar(10),@i+1)+ ' begin set @text' +convert ( varchar(10), @i)+ '= @inText end '-- print @text' +convert ( varchar(10), @i)+ ' end '
      set @i= @i+1
end
set @text= @text+ ' fetch next from longTextcur into @id, @inText end close longTextcur deallocate longTextcur '
set @text= @text+' exec ('''''
set @i=0
while @i< @count begin
      set @text= @text+ '+  @text'+ convert ( varchar(10), @i)
      set @i= @i+1
end
set @text= @text+')'
exec ( @text)
truncate table sLongString
GO
0
 
LVL 10

Expert Comment

by:Jacco
ID: 13517809
Thanx! This solves a problem.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question