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

How to return a varchar from sproc on remote Linked Server?

The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.

DECLARE@.OutParamVARCHAR(5)

EXEC('{Call getnextpin(1)}')ATALTTEST

GetNextPin takes a single input parameter.

I need to modify the above code snippet to return the next PIN number into @.OutParam.

Any ideas?

Any help appreciated.

Regards,

Paul.

Hi wadep,

Try the following code and see if it solves your problem ( i suppose your table has a primary key named "number_index" and your table is ordered well. If not, you need to use cursor which will be a little complicated)

create procedure getnextpin@.indexint,@.numberint outputasselect top 1 @.number=pin_number from your_table_namewhere number_index>@.indexreturn

-------- call proceduredeclare @.indexint, @.numberintset @.index=whatever_you_wantexecute getnextpin @.index,@.number outputprint @.number -- see if your number can be printed here

Hope my suggestion helps

How to return a range of rows?

How can a SQL statement be written to return a specified range of
rows? For example:

-- tblContact
-- (
-- SSN char(9),
-- FirstName varchar(50),
-- LastName varchar(50)
-- )
-- This table contains 500 rows.

Select * from tblContact -- Return only rows 5 through 10

Thanks"Briniken" <briniken@.yahoo.com> wrote in message
news:68dae6d3.0311012131.6f9faf26@.posting.google.c om...
> How can a SQL statement be written to return a specified range of
> rows? For example:
> -- tblContact
> -- (
> -- SSN char(9),
> -- FirstName varchar(50),
> -- LastName varchar(50)
> -- )
> -- This table contains 500 rows.
> Select * from tblContact -- Return only rows 5 through 10
> Thanks

Data in tables doesn't have any order, so you have to decide how to say
which are the 'first' 10 rows. Assuming that you want rows 5 to 10 when
ordered by LastName, then this is one possible solution:

select top 5 * from
(
select top 10 *
from tblContact
order by LastName asc) dt
order by LastName desc

Alternatively, you can look at the first example in this KB article:

http://support.microsoft.com/defaul...kb;en-us;186133

If you add "having count(*) between 5 and 10" to the query, you should also
get the results you want.

Simon|||Hi

There is not equivalent of a row number in SQL Server, therefore you need to
be able to order the values, but something like

SELECT TOP 5 SSN. FirstName, LastName FROM
( SELECT TOP 10 SSN. FirstName, LastName FROM tblContact ORDER BY SSN ASC )
A
ORDER BY SSN DESC

Will give you the rows, but not in order!

Also check out the solution in the following thread
http://groups.google.com/groups?hl=...Newsposts%2BTOP

John

"Briniken" <briniken@.yahoo.com> wrote in message
news:68dae6d3.0311012131.6f9faf26@.posting.google.c om...
> How can a SQL statement be written to return a specified range of
> rows? For example:
> -- tblContact
> -- (
> -- SSN char(9),
> -- FirstName varchar(50),
> -- LastName varchar(50)
> -- )
> -- This table contains 500 rows.
> Select * from tblContact -- Return only rows 5 through 10
> Thanks

How to return a partial string based on a particular character?

Hi,
I am looking through books on-line but an not finding what I am looking for.
In my stored proc, I am being passed a varchar field, 20 long. It looks
something like, '103098-1'
I need to split the characters on the left side of the '-' into one field,
and the characters on the right side of the '-' into another field.
How do I do this?
Thanks,
Steve
This is how I did it, does this make sense, or is there an easier way?
Declare @.strOrder varchar(20)
set @.strOrder = '38372-1'
set @.charIndex = CHARINDEX('-', @.strOrder)
set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
@.charIndex))
Thanks again.
"SteveInBeloit" wrote:

> Hi,
> I am looking through books on-line but an not finding what I am looking for.
> In my stored proc, I am being passed a varchar field, 20 long. It looks
> something like, '103098-1'
> I need to split the characters on the left side of the '-' into one field,
> and the characters on the right side of the '-' into another field.
> How do I do this?
> Thanks,
> Steve
|||yes, it could be done in a single line though.
Declare @.strOrder varchar(20)
declare @.left varchar(10)
declare @.right varchar(10)
set @.strOrder = '38372-1'
select @.left = left(@.strOrder, CHARINDEX('-', @.strOrder)-1),
@.right=substring(@.strOrder,
charindex('-',@.strOrder)+1,len(@.strOrder)-charindex('-',@.strOrder)+1)
print @.left
print @.right
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:A9247AFD-686A-46BE-AE7B-225E813FBCA1@.microsoft.com...[vbcol=seagreen]
> This is how I did it, does this make sense, or is there an easier way?
> Declare @.strOrder varchar(20)
> set @.strOrder = '38372-1'
> set @.charIndex = CHARINDEX('-', @.strOrder)
> set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
> Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
> @.charIndex))
> Thanks again.
> "SteveInBeloit" wrote:
for.[vbcol=seagreen]
looks[vbcol=seagreen]
field,[vbcol=seagreen]

How to retrieve value from Exec stmt?

hi, all..
the following is part of my sp
I want to know how to assign result of stmt.1 to @.tp

declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
Exec ('SELECT MAX(UpdateTime) FROM ' + @.tableName) -- stmt.1

thank you..Try putting everything in an EXEC statement:

EXEC('
declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = ''tblState''
SELECT MAX(UpdateTime) FROM @.tableName
')

I can't say this is the optimal way to do it, but it should work. Note: Those are two single quotes around tblState, not double quotes.|||declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
EXEC sp_executesql 'SELECT @.tp=MAX(UpdateTime) FROM ' + @.tableName, N'@.tp datetime OUTPUT',@.tp OUTPUT

select @.tp

it should give u the exact result.|||Thank you for reply,
however it gives following err...
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.|||Modifying the previous example, I'd use:declare @.sql VARCHAR(250)declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
SET @.sql = 'SELECT @.tp=MAX(UpdateTime) FROM ' + @.tableName

EXEC sp_executesql @.sql, N'@.tp datetime OUTPUT',@.tp OUTPUT

select @.tp-PatP|||USE Northwind
GO

SET NOCOUNT ON

DECLARE @.tp datetime, @.TABLE_NAME sysname, @.COLUMN_NAME sysname, @.SQL varchar(8000)

SELECT @.TABLE_NAME = 'Orders', @.COLUMN_NAME = 'OrderDate'

CREATE TABLE #x(tp datetime)

SET @.SQL = 'INSERT INTO #x(tp) SELECT MAX('+@.COLUMN_NAME+') FROM '+@.TABLE_NAME

EXEC(@.SQL)

SELECT @.tp = tp FROM #x

SELECT @.tp

DROP TABLE #x

SET NOCOUNT OFF
GO|||Thank you all..
All are good hint..
Now it works..

my version is following..

DECLARE @.SQLString NVARCHAR(500), @.tbl nvarchar(100)
DECLARE @.ParmDefinition NVARCHAR(500)
declare @.tp datetime
SET @.tbl = 'tblState'
SET @.SQLString = N'SELECT @.tp = MAX(UpdateTime) FROM ' + @.tbl
SET @.ParmDefinition = N'@.tp datetime OUTPUT'
EXECUTE sp_executesql @.SQLString, @.ParmDefinition
,@.tp OUTPUT
Select @.tp as UpdateTime