Solved

What to use instead of varchar(8000) in SQL Server 2000

Posted on 2008-10-31
8
737 Views
Last Modified: 2013-12-17
Hi,

I am trying to write a stored procedure and in that I am using exec function, but the query length is greater than varchar(8000) so what should I use instead so that I can include character sql string of a greater length than varchar(8000) ?

Attached alongwith is code snippet.

Please give me a solution to this.

Regards,
Pranjal
select @sql1 = 'insert into #temp8' +
	' SELECT CLT_SITE,CLT_CD,COUNT(BILL_STATUS) AS TOTAL, ''B1'' as BILL_STATUS' +
	' FROM IMG_INFO WHERE (IMG_DT < LEFT(CONVERT(VARCHAR(20),GETDATE()-1),11)' + 
	' AND CRT_DATE = ''1/1/1900'' AND IMG_DT >= ''10/01/2008'' AND BILL_STATUS = ''E'')' +
	' GROUP BY CLT_SITE, CLT_CD' 
 
exec @sql1

Open in new window

0
Comment
Question by:pranjal_ds
[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
8 Comments
 
LVL 3

Expert Comment

by:Blackninja2007
ID: 22849828
did sql 2000 have the TEXT or NTEXT type which take over 2,000,000,000 characters ?

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22914632
Certainly. Unfortunately you could not declare local variables of type text or ntext so the author is SOL.
0
 

Expert Comment

by:hamishd
ID: 22936201
If you are using MS SQL 2005 or above, use datatype varchar(max)

http://teratrax.com/articles/varchar_max.html
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22936224
>>If you are using MS SQL 2005 or above<<
The title of this question is a dead giveaway. :)
0
 

Author Comment

by:pranjal_ds
ID: 22971082
Hi,
I am using SQL Server 2000 only so please provide me a solution to varchar(8000) or any other parallel solution so that I can execute the above mentioned code.

Regards,
Pranjal
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22972424
The best you can do is break it down into multiple varchar(8000) variables and then concatenate them when you do Exec as in:

Exec @SQL1 + @SQL2 + @SQL3 + ... + @SQLN
0
 

Author Comment

by:pranjal_ds
ID: 22975439
I am getting error "Incorrect syntax near +" when using the above suggestion.
0
 

Accepted Solution

by:
pranjal_ds earned 0 total points
ID: 22976144
I have found a solution to this by using a cursor with union all for multiple queries output combination.

Regards,
Pranjal
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

696 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