ToString Conversion Truncating SqlString Value in SQL CLR

Question

I'm using C# to build a SQL Server CLR procedure. In that I'm trying to convert a SqlString parameter to string, but the result is being truncated.

Here's simplified version of the code. The parameter "MyParam" contains a string of over 200 characters, but for some reason the ToString() truncates it to 100 characters. Any ideas? Or better way to convert the SqlString parameter.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void MySqlClrProcedure(SqlString MyParam)
{
    SqlContext.Pipe.Send("Test: " + MyParam.ToString());
}

It would be awesome if you could share a minimal reproducible example. — mjwills
– mjwills, Commented Sep 12, 2019 at 2:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Try to always use the Value property of the Sql* types. In your case it would be: MyParam.Value. The Value property is available in all of the Sql* types and returns the expected .NET type.
There is no inherent truncation in SqlString as it can support NVARCHAR(MAX). And SqlContext.Pipe.Send() should be limited to 4000 UTF-16 characters (8000 bytes) as it is a PRINT statement.

Most likely your T-SQL wrapper object has its input parameter defined as NVARCHAR(100). Change it to be NVARCHAR(4000) or even NVARCHAR(MAX). If you are using Visual Studio / SSDT to build this, you can added a SqlFacet decorator to your input parameter to have it use that max length: MySqlClrProcedure([SqlFacet(MaxLength=4000)] SqlString MyParam)

To learn more about working with SQLCLR in general, please check out the series I am writing on this topic:

Stairway to SQLCLR

Also: SQLCLR Info

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 12, 2019 at 2:50

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

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

Collectives™ on Stack Overflow

ToString Conversion Truncating SqlString Value in SQL CLR

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related