Solved

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

Posted on 2008-10-31
8
736 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
This video demonstrates basic masking and how to edit the mask to reveal the desired image.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

839 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