Alternate for Varchar(8000)

Question

Post reply

Alternate for Varchar(8000)

RJ-476420

SSCarpal Tunnel

Points: 4806
More actions
June 17, 2008 at 9:29 am

#93323

I have a stored procedure written in SQL Server 2000 which has a parameter passed as XML document as varchar(4000). Now, I'm trying to expand the XML document size to varchar(8000) but it is not a possible solution since my document size may exceed 8000 characters. Moreover, I'm not comfortable using "TEXT" datatype as this datatype may not be supported by future SQL Server versions.
Also, this stored procedure could be written in other database systems like MySQL, PostGres, Oracle but the front end application remains the same. So, please provide some suggestions how I can increase the XML Document size in this stored procedure.
I know SQL Server 2005 supports 2GB varchar but we are not moving anytime sooner to this system & also I have to support other database features.
Any help is appreciated.
~Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]Have you tried nText datatype, I am not sure but it might help you.

Mahesh[/font]

MH-09-AM-8694

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 2

Thanks, Mahesh. But, ntext wont be supported as well in future SQL Server versions.

~Thanks

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

You're stuck. You either need to move to 2005 & use VARCHAR(MAX) or stay in 2000 and use TEXT. There's no in between.

DB2 supports (MAX), but I'm not sure about the other databases mentioned.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

You could always store the document(s) in a file and store the file path/name in the database. Kind of a universal solution for all RDBMS's...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 5

Jeff- This is a desktop application & I use the XML tag to carry the search items to be searched in the database. Like, a person's first, last name, phone, etc., Based on the inputs from user the stored procedure will do the search in the database & return the resultset.

Hence, I'm not using the path to store them in external hard drive. Moreover, all these things takes place on the fly from front end application to database so the XML tags are always in memory & destroyed.

~Thanks