?
Solved

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

Posted on 2008-10-31
8
Medium Priority
?
740 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

770 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