Solved

excedding limit varchar(8000)

Posted on 2003-11-10
10
2,284 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Upgrading my SSIS package in VS 2012 6 59
Are triggers slow? 7 12
MS SQL Server select from Sub Table 14 23
Use column to search string column 2 9
I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

856 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