| Author |
Topic |
|
Marioi
Posting Yak Master
132 Posts |
 Posted - 2005-11-14 : 17:05:53
|
| In SS 2000 it seems that there is no variable data type that can hold more than 8000 characters (varchar) or 4000 unicode characters (nvarchar). I've seen posts where multiple variables are spliced together to extend this limit. I am looking at performing string manipulations in an sproc and I need to be able to deal with the full 2GB/1GB limit of text and ntext field types. Is this possible? How do you deal with that? |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-14 : 17:12:45
|
| The "text" datatype will hold more than 8000 characters, but is more limited in functionality than varchar. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-14 : 17:13:48
|
| Text will give you 4GB and nText will give you 2GB. String manipulations is going to be hard with either data type. I'd recommend the multiple variable approach. I believe there is an article here discussing it.Tara Kizeraka tduggan |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-14 : 17:35:16
|
quote:
Originally posted by tkizer
Text will give you 4GB and nText will give you 2GB. String manipulations is going to be hard with either data type.
But I can't declare @variable as text or ntext, therefore I am limited to 8000 chars, right?I saw somewhere that SS 2005 allows going to full 2GB with varchar(MAX). Coming from development tools that made a jump from the 32k (or 64k) limit of string variables to 2GB a long time ago, this seems like a long overdue improvement.How do you deal with long memo strings in sProcs? 8000 chars at a time? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-14 : 17:47:08
|
| I gave my recommendation already. I've never had to deal with this type of situation, but I've done a lot of reading about it in the forums.Tara Kizeraka tduggan |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-14 : 18:00:47
|
| Thanks Tara. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-15 : 08:33:36
|
quote:
How do you deal with long memo strings in sProcs? 8000 chars at a time?
....hmmmm...the fact that you want to assign this value to a variable makes me think that you are implementing cursors or loops, perhaps unnecessarily. If you use only set-based operations there is no need to load your verylongstring into a variable.What are you trying to accomplish? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-15 : 08:42:09
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274MadhivananFailing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-15 : 09:30:58
|
| The only place you can have a TEXT or NTEXT variables declared is as an input parameter to a stored procedure.If you need to hold TEXT on NTEXT data to manipulate within a stored procedure, create temp tables with a TEXT or NTEXT columns to hold it.create table #temp_text ( MyText TEXT )insert into #temp_text (TEXT)select @sp_input_text_paramCODO ERGO SUM |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-15 : 15:21:37
|
| Thanks to all of you. I am trying to work with memos that contain > 8000 chars like I can in VB or .net: parse and insert values, add sections of new text, etc. This has nothing to do with large query strings. I simply wanted to encapsulate text and binary manipulation functionality in sProcs so that they can be accessed from other sProcs with minimal overhead. I guess most people do this kind of work in the client or in a DLL in the middle tier? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-15 : 15:29:16
|
| Yes this should be done outside of SQL Server.Tara Kizeraka tduggan |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-15 : 15:38:21
|
quote:
Originally posted by tkizer
Yes this should be done outside of SQL Server.
Any preference? ActiveX DLL OK? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-15 : 15:44:19
|
| I'm not a developer, so I wouldn't be able to provide a preference for client-side code.Tara Kizeraka tduggan |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-11-15 : 16:04:30
|
quote:
Originally posted by tkizer
I'm not a developer, so I wouldn't be able to provide a preference for client-side code.Tara Kizeraka tduggan
I always like that answer....Hey, why not just use SQL Server 2005...You'll have varchar(max)Which will be 2^31-1 bytes.That's a lot of bytes....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-15 : 16:46:53
|
quote:
Originally posted by X002548Hey, why not just use SQL Server 2005...You'll have varchar(max)
And the ability to use .net assemblies directly and a bunch of other stuff. Do you remember what the lowest level OS requirements are? We want to support reasonably old hardware and OSs for replicated clients. |
|
|
|
|
Dealing with more than 8000 characters