Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 746
  • Last Modified:

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

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
pranjal_ds
Asked:
pranjal_ds
1 Solution
 
Blackninja2007Commented:
did sql 2000 have the TEXT or NTEXT type which take over 2,000,000,000 characters ?

0
 
Anthony PerkinsCommented:
Certainly. Unfortunately you could not declare local variables of type text or ntext so the author is SOL.
0
 
hamishdCommented:
If you are using MS SQL 2005 or above, use datatype varchar(max)

http://teratrax.com/articles/varchar_max.html
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Anthony PerkinsCommented:
>>If you are using MS SQL 2005 or above<<
The title of this question is a dead giveaway. :)
0
 
pranjal_dsAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
pranjal_dsAuthor Commented:
I am getting error "Incorrect syntax near +" when using the above suggestion.
0
 
pranjal_dsAuthor Commented:
I have found a solution to this by using a cursor with union all for multiple queries output combination.

Regards,
Pranjal
0

Featured Post

Independent Software Vendors: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now