Solved

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

Posted on 2008-10-31
8
732 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
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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article shows how to convert a multi-page PDF file into multiple image files, with one image file created for each page of the PDF. It does this by utilizing an excellent, free software package called GraphicsMagick. The solution is amazingly s…
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.
The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.

708 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

16 Experts available now in Live!

Get 1:1 Help Now