How to return varchar(MAX) from a CLR function?

Hi,

I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters.

How can I solve this problem and return varchar(MAX)?

Thanks

? The Visual Studio deployment tool maps SqlString to VARCHAR(8000). Use SqlChars instead -- it gets mapped to VARCHAR(MAX). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bill YU@.discussions.microsoft.com> wrote in message news:2a21db0a-f54b-42e2-88b9-fb4cab26d356@.discussions.microsoft.com... Hi, I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters. How can I solve this problem and return varchar(MAX)? Thanks|||Thanks Adam, it works.|||Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!
|||? Visual Studio deployment unfortunately has a lot of quirks. In case you're interested, I discuss another one in the following blog post: http://www.amazon.com/gp/plog/post.html/ref=cm_blog_pl/104-6385614-0075127?%5Fencoding=UTF8&pt=personalBlog&aid=PlogMyCustomersAgent&ot=customer&pd=1147803274.225&pid=PMCAALIINL2LPJELat1147801908&iid=AALIINL2LPJEL -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bo416@.discussions.microsoft.com> wrote in message news:2343e9db-ce1c-4f2a-9b47-2d471a0ddb44@.discussions.microsoft.com...Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!

how to return truncated field

Hi,
I'd like to select a field of my table and also a truncated version of
that field:
SELECT Field1, Field1_truncated FROM myTable
This field is of type nvarchar. Field1 should contain the entire string
valule whereas Field1_truncated should only contain the first 10
characters of the original field, followed by 3 dots (...)
So I would get this kind of result:
Field1 Field1_truncated
some long string some long ...
Can you help?SELECT Field1, left(Field1,10) + '...' as Field1_truncated FROM myTable
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi, Samuel
Try something like this:
SELECT Col1,
CASE
WHEN Len(Col1)>13
THEN LEFT(Col1,10)+'...'
ELSE Col1
END as Col1_truncated
FROM YourTable
Razvan|||Brilliant.
Thanks to you guys !