T-SQL (varchar(max) vs CLR (string, SqlString, SqlChars)?

Question

I have a problem, that I can't solve. I'm using SQL Server 2005, C# CLR for using outer dll. The problem is at length of parameter. I need to use as function parameter type varchar(max). If at C# code I use string, SqlSring, I can't use T-SQL type varchar(max), just varchar(4000) of nvarchar(4000). I need to say, that can be situations, when I need to use more then 4000 symbols, so I need know, what C# type I need to use for varchar(max).

I have read a lot of articles, and several of them say, that for this I can use SqlChars. But! I have manipulations with strings. How can I have actions with string or SqlString and then convert to SqlChars? (It is possible SqlChars.ToString() or SqlChars.ToSqlString()).

I didn't find any C# code for this.

A varchar(max) string that's using its maximum capacity will not be loadable into a CLR string, since it will exhaust the address space. Can you be more explicit about what actions you want to perform against these strings? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 28, 2011 at 8:27
I convert any SQL type to varchar and send this value to the outer dll. Than, do manipulations with it and return to SQL Server. Now I use SQL/vchar(4000) and C#/string. I need more than 4000 symbols. — Chaki_Black
– Chaki_Black, Commented Sep 28, 2011 at 8:56
@Damien_The_Unbeliever: I was under the impression both SQL Server's varchar(max) as well as a .NET string instance could be up to 2 GB in size.... — marc_s
– marc_s, Commented Sep 28, 2011 at 9:41
@marc_s - as it turns out, a varchar(max) variable can contain more than 2GB. But I was mostly working from the other side. Certainly under 32 bit CLR, you can't actually allocate a 2GB object, and I think you can struggle even under 64 bit. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 30, 2011 at 16:26

Kolya Evdokimov · Accepted Answer · 2014-04-12 14:41:29Z

13

What you need to do is add SqlFacet(MaxSize = -1) attribute to parameter:

[return: SqlFacet(MaxSize = -1)]
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
public static SqlString YourSqlFunction([SqlFacet(MaxSize = -1)]SqlString sourceSS)
{ return new }

answered Apr 12, 2014 at 14:41

Kolya Evdokimov

3314 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Keskinov Over a year ago

Correct! This will cause VS to create correct script automatically!

Michał Powaga · Accepted Answer · 2012-05-09 12:32:34Z

4

I found the answer. Not just only me asked about this question. I have to read posts more attentively...

Can read here http://social.msdn.microsoft.com/Forums/is/sqlnetfx/thread/391ebb98-e1b5-47fc-a72d-9f1e39829e3a The problem of CLR compatibility solves not very difficult.

All time I wanted use varchar(max) and tried to use C# types string, SqlString, SqlChars. Need to use T-SQL nvarchar(max), and you can use any of C# types string, SqlString, SqlChars!

Of course nvarchar taking up more space than varchar at two times.

edited May 9, 2012 at 12:32

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

answered Sep 29, 2011 at 14:07

Chaki_Black

9324 gold badges13 silver badges31 bronze badges

1 Comment

Solomon Rutzky Over a year ago

SQLCLR does not allow for VARCHAR, whether it is 1 - 4000 or MAX. NVARCHAR is the only string type that the SQLCLR API passes through (well, I guess object / SQL_VARIANT can also pass a string of up to 4000). If you pass in VARCHAR values, they are implicitly converted to NVARCHAR. Please do not use, or suggest using, string as a SQLCLR input or output param type. For more info, please see my series, Stairway to SQLCLR, on SQL Server Central (free registration is required for that site).

Michał Powaga · Accepted Answer · 2012-05-09 12:30:51Z

0

Try using length -1. Something like this:

cmd.Parameters.Add("@param", SqlDbType.VarChar, -1).Value = "hdfiophdopigherog";

edited May 9, 2012 at 12:30

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

answered Sep 28, 2011 at 8:14

Tom Chantler

15k4 gold badges50 silver badges53 bronze badges

2 Comments

Chaki_Black Over a year ago

I can't try this, because I don't have SqlCommand... Just input parameter - manipulation with it - out parameter. And I need IN/OUT parameters will be SqlChars

Solomon Rutzky Over a year ago

While -1 is technically correct, this answer is still irrelevant to the question. This answer is dealing with a SqlConnection API and not the SQLCLR API.

Collectives™ on Stack Overflow

T-SQL (varchar(max) vs CLR (string, SqlString, SqlChars)?

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related