Solved

excedding limit varchar(8000)

Posted on 2003-11-10
10
2,278 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
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
 
LVL 12

Accepted Solution

by:
ill earned 20 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now