Text field argument in stored procedure - char limit?

[sodakotahusker]

Is there a limitation on the number of characters that a text field argument can handle? I mean in the same range as varchar (4000 or something). I don't need an exact number of bytes - just a answer like - 4, 8k or A huge number if this indeed allows mega bytes. I seem to be suffering from truncation and it could be a limitation. I'll have to ditch the store procedure if that is the case and that activity is frowned on.

 

[SQLDenis]

A text field can go up to 2 Gigabyte



“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[sodakotahusker]

Cool. That is what I figured. I must be truncating somewhere else. Thanks!!!

 

[Hameedullah]

I don't think you can pass text to a stored procedure. Use varchar.YOu can send up to 8000 chars through it.

Have a read of this:

http://www.sqlxml.org/faqs.aspx?faq=42

You are either the slave of what made men or what men made.

 

[hmckillop]

Hameedullah
You can pass text type through to a stored procedure, if you define a parameter with type as text.
You can also use binary, varbinary, ntext, image etc.

You example uses xml, how would you pass an XML string larger than 8000 chars to a proc if you couldnt use text data type?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[sodakotahusker]

I finally pinpointed the cause of my truncation. The query pulling this data is dynamic sql (which shouldn't matter). However this query is a union query. It appers that the type is automatically cast as a varchar(4000) and that is where I get cut off. I tried to specify the field as cast to text (in the union sections which really were varchar values). That threw an erros saying that text could not be used with distinct (I am not even using a distinct qualifier!).
I have a filter which actually allows me to return only those sections of the union query which have the text field value. That works fine. So I figured - perhaps the first value used is the model for the others that follow so I moved my text field secion to the top of the query. No luck. Still cutting off at 4000. If I can't use the cast statement how do I get more than 4000???

 

[gmmastros]

when you use union, it performs a distinct operation. If you don't want distinct, then you should use UNION ALL instead.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hmckillop]

Varchar allows up to 8000, change the variable to be 8000 chars.
Its nvarchar which only allows 4000.

"I'm living so far beyond my income that we may almost be said to be living apart