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, Sep 28 '11 at 08: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, Sep 28 '11 at 08: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, Sep 28 '11 at 09:41
@marc_s - as it turns out, a `varchar(max)` variable can [contain more than 2GB](http://stackoverflow.com/q/7611394/15498). 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, Sep 30 '11 at 16:26

score 13 · Answer 1 · answered Apr 12 '14 at 14:41

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 '14 at 14:41

Kolya Evdokimov

331
4
5

Correct! This will cause VS to create correct script automatically! – Mike Keskinov Sep 24 '14 at 18:01

score 4 · Answer 2 · edited May 09 '12 at 12:32

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 09 '12 at 12:32

Michał Powaga

22,561
8
51
62

answered Sep 29 '11 at 14:07

Chaki_Black

882
3
12
30

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](http://www.sqlservercentral.com/stairway/105855/), on SQL Server Central (free registration is required for that site). – Solomon Rutzky Dec 30 '15 at 03:53

score 0 · Answer 3 · edited May 09 '12 at 12:30

0

Try using length -1. Something like this:

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

edited May 09 '12 at 12:30

Michał Powaga

22,561
8
51
62

answered Sep 28 '11 at 08:14

Tom Chantler

14,753
4
48
53

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 – Chaki_Black Sep 28 '11 at 08:18
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. – Solomon Rutzky Dec 30 '15 at 03:56

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

3 Answers3

Linked