How to take big raw text in SP as a paremeter!...............

Hi all,
I have raw text like these:
'test1111111111111111111111111111111111
test2222222222222222222222222222222222
test3333333333333333333333333333333333
test4444444444444444444444444444444444
test5555555555555555555555555555555555'
I want to create a SP that take these raw text as a paremeter and insert it
into a temp table as a different row. In this case, my temp table should
store:
test1...
test2...
test3...
test4...
I created a SP for this and defined a paremeter as text. However, for some
reason, the 'text' paremeter ONLY took limited text and truncated whatever
after that.
Thanks in advance!
Tom dHow did you pass the value to the parameter and from where?
If you are testing your sp in QA, and you pass the text as a value (a string
between apostrophes), then sql server consider it as varchar and the max
length for varchar is 8000.
AMB
"tom d" wrote:

> Hi all,
> I have raw text like these:
> 'test1111111111111111111111111111111111
> test2222222222222222222222222222222222
> test3333333333333333333333333333333333
> test4444444444444444444444444444444444
> test5555555555555555555555555555555555'
> I want to create a SP that take these raw text as a paremeter and insert i
t
> into a temp table as a different row. In this case, my temp table should
> store:
> test1...
> test2...
> test3...
> test4...
> I created a SP for this and defined a paremeter as text. However, for some
> reason, the 'text' paremeter ONLY took limited text and truncated whatever
> after that.
> Thanks in advance!
> Tom d
>|||You have to be wary about the functions you use to parse the string. Most
functions like CharIndex only work on VarChar data types and not on Text dat
a
types. You need to process the string in 8000 character chunks using somethi
ng
like Substring (which does work on Text data types) and split the values bas
ed
on the delimiter (in this case) of a line feed or carriage return and line f
eed.
Thomas
"tom d" <tomd@.discussions.microsoft.com> wrote in message
news:FD307709-71D4-42F5-9A29-CC94003922A3@.microsoft.com...
> Hi all,
> I have raw text like these:
> 'test1111111111111111111111111111111111
> test2222222222222222222222222222222222
> test3333333333333333333333333333333333
> test4444444444444444444444444444444444
> test5555555555555555555555555555555555'
> I want to create a SP that take these raw text as a paremeter and insert i
t
> into a temp table as a different row. In this case, my temp table should
> store:
> test1...
> test2...
> test3...
> test4...
> I created a SP for this and defined a paremeter as text. However, for some
> reason, the 'text' paremeter ONLY took limited text and truncated whatever
> after that.
> Thanks in advance!
> Tom d
>