Passing a text field From Function to SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing a text field From Function to SP

Author

Topic

william_lee78
Yak Posting Veteran

53 Posts

Posted - 2004-09-03 : 06:51:05

Hi All

,

I write a function to return a table to my store procedure. The table contains only one fielw which is Text field. This text field store a large number of data, approximately 30000 characters. When I call the function from store procedure, it return me the data in the column but some of the data have been truncated. Is there any way I can pass my complete data to my store procedure.

Is there any better way to solve this problem?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-03 : 06:54:47

"some of the data have been truncated"....does this happen in QA??
if so....the 'maximum column length to be displayed in QA' option is kicking in.

change it to a more suitable value.

william_lee78
Yak Posting Veteran

53 Posts

Posted - 2004-09-05 : 21:11:32

Hi,

Thanks for your replied. To let you have a better understanding of my situation, here are my code:

CREATE FUNCTION [fc_GetInvoiceInstructions] (@InvNo AS varchar(10))
RETURNS
@InvInstr TABLE
(
InvoiceInstructions text

)
BEGIN
................
................ ---> Some system logic here
................

INSERT INTO @InvInstr VALUES(
@COA + CHAR(13) + CHAR(10) + @COA_Type + CHAR(13) + CHAR(10) + @COO + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
@COA_Recipient + @Surveyor + @Collection + @Instructions + CHAR(13) + CHAR(10) +
@InvDetail1 +
@InvDetail2 +
@InvDetail3 +
@InvDetail4 +
@InvDetail5

)

RETURN
END

The truncation happen when I try to contenate the results together and return it as a text fields.

Anyone can help?

Thanks for the helps..........

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-06 : 00:42:53

read:
Managing ntext, text, and image Data in BOL.
----------------------------
Using text in row
In Microsoft SQL Server 2000, users can enable a text in row option on a table so it could store text, ntext, or image data in its data row.

To enable the option, execute the sp_tableoption stored procedure, specifying text in row as the option name and on as the option value. The default maximum size that can be stored in a row for a BLOB (binary large object: text, ntext, or image data) is 256 bytes, but values may range from 24 through 7000. To specify a maximum size that is not the default, specify an integer within the range as the option value.

text, ntext, or image strings are stored in the data row if the following conditions apply:

text in row is enabled.

The length of the string is shorter than the limit specified in @OptionValue

There is enough space available in the data row.
When BLOB strings are stored in the data row, reading and writing the text, ntext, or image strings can be as fast as reading or writing character and binary strings. SQL Server does not have to access separate pages to read or write the BLOB string.

If a text, ntext, or image string is larger than the specified limit or the available space in the row, pointers are stored in the row instead. The conditions for storing the BLOB strings in the row still apply though: There must be enough space in the data row to hold the pointers.
--------------------------

Directly making an insert into the table with text data type will raise an error.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-06 : 01:02:06

quote:
Originally posted by AndrewMurphy

"some of the data have been truncated"....does this happen in QA??
if so....the 'maximum column length to be displayed in QA' option is kicking in.

change it to a more suitable value.

What is the maximum value we can set.
Thanks

mk_garg

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-06 : 03:02:36

quote from BOL
text

Variable-length non-Unicode data in the code page of the server and with a maximum length of 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

quote:
Originally posted by mk_garg20

quote:
Originally posted by AndrewMurphy

"some of the data have been truncated"....does this happen in QA??
if so....the 'maximum column length to be displayed in QA' option is kicking in.

change it to a more suitable value.

What is the maximum value we can set.
Thanks

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources