[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2309
  • Last Modified:

excedding limit varchar(8000)

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
Dharmeshgandhi
Asked:
Dharmeshgandhi
1 Solution
 
CJ_SCommented:
You can use a text field instead.
0
 
DharmeshgandhiAuthor Commented:
no.
its not valid for  local variables.

thnx,
dharmesh
0
 
CJ_SCommented:
You can however use a text field as a paramter of the stored procedure and use that variable instead.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
illCommented:
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
 
LowfatspreadCommented:
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
 
MauriceBicheCommented:
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
 
illCommented:
-- 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
 
JaccoCommented:
Thanx! This solves a problem.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now