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 FTS results from varchar(MAX) or Text data type column?

I am unable to get FTS working where the column to be searched is type varchar(MAX) or Text. I can get this to work if my column to be indexed is some statically assigned array size such as varchar(1000).

For instance this works, and will return all applicable results.

CREATE TABLE [dbo].[TestHtml](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PageText] [varchar](1000) NOT NULL,

CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED

SELECT * FROM TestHTML WHERE Contains(PageText, @.searchterm);

And this does not. It returns zero results what so ever.

CREATE TABLE [dbo].[TestHtml](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PageText] [varchar](MAX) NOT NULL,

CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED

SELECT * FROM TestHTML WHERE Contains(PageText, @.searchterm);

Could someone please tell me what I need to do to enable FTS on varchar(MAX) or Text columns?

Did you create a fulltext index on the column and made it language specific / language neutral ?

Jens K. Suessmeyer

http://www.sqlserver2005.de