May 19, 2006 at 9:39 am
I know I am probably missing something here, but string paramaters I pass to a C# CLR stored procedure get truncated at 4K.
Here is the T-SQL code:
Declare @Message nvarchar (max);
declare @res int;
SET @Message = 'Giant Test Message for test ';
Set @message = @message + replicate (cast ('A-Z' as varchar (MAX)), 10000);
select len (@Message);
exec @res = LenTest @message;
select @res;
Here is the C# procedure
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static int LenTest(SqlString MessageToSend)
{
return (MessageToSend.ToString().Length);
}
}
The select len statement returns 30028.
But the select @Res statement only returns 4000
Thank you for your assistance.
Scott Eichman
May 19, 2006 at 12:02 pm
Max length of NVARCHAR is 4000.
Max length of VARCHAR is 8000.
@Message is of NVARCHAR data type.
-SQLBill
May 19, 2006 at 12:05 pm
Also, I don't believe VARCHAR(MAX) is valid TSQL syntax.
MAX in TSQL requires an expression that if finds the MAX of. MAX(MyDate).
-SQLBill
May 19, 2006 at 12:46 pm
I wanted to clarify that I am running MSSQL 2005 so nvarchar (max) is a valid construct. It produces a string of upto about 2 gig.
Scott
May 20, 2006 at 2:15 pm
I have found the solution to this problem.
June 17, 2006 at 1:46 pm
As someone who had the same problem and was excited to find this thread and was then disappointed to not see the answer, I felt I would share the answer now that I found it. For some reason this is not is not as conveniently documented as one would think it should be.
The following did work 12 years ago (when I posted this answer) with what are now older versions of Visual Studio / SSDT (or "Data Dude" as it was originally named). However, there is, and was, a better answer that I learned about after posting this, but then I forgot that I had posted this so did not know to come back to improve it. So, now I'm posting the better info in the "UPDATED AND IMPROVED ANSWER" section. Besides, the following method no longer works with more recent versions of Visual Studio / SSDT (starting in 2012, I think) as the default now is to also map SqlString to NVARCHAR(MAX).
The following code both accepts AND returns a NVARCHAR(MAX) datatype. The key is to use the "SqlChars" type instead of "SqlString".
Some notes on the following:
[Microsoft.SqlServer.Server.SqlFunction(Name = "TestFunction", DataAccess = DataAccessKind.Read)]
public static SqlChars TestFunction(SqlChars InputString)
{
string __TempString = new string(InputString.Value);
/* whatever code here */
return new SqlChars(__TempString.ToCharArray());
}[h2]UPDATED AND IMPROVED ANSWER:[/h2]
Using SqlChars to get the "Deploy" operation (which is now the "Publish" operation in newer versions of Visual Studio / SSDT, while the "Deploy Solution" and "Deploy { project_name }" operations don't do anything) to use NVARCHAR(MAX) in the generated T-SQL was an effect of SSDT mapping "SqlString" to NVARCHAR(4000) and "SqlChars" to NVARCHAR(MAX). However, starting with Visual Studio 2012 (I believe), the default mapping for "SqlString" change to NVARCHAR(MAX). The issue was never that "SqlString" and "SqlChars" necessarily mapped to those T-SQL datatypes, it was just a default chosen by someone, and then later changed. But, both of those .NET types can be used with either of those T-SQL datatypes. At this point, if you want to use NVARCHAR(1 - 4000), then you need to do one of the following:
[SqlFacet(MaxSize = 200)] SqlString InputParamName
For return types of scalar Functions (and User-Defined Aggregates, and methods / properties of User-Defined Types), the attribute is placed just above the [SqlFunction] attribute, prefixed with "return:". For example:
[return: SqlFacet(MaxSize = 2000)]
[SqlFunction(name="functionName")]
public static SqlChars myFunction(....
This option works when publishing via Visual Studio / SSDT or even SSDT by itself.
and, my suggestion to allow NVARCHAR(MAX), VARBINARY(MAX), and XML parameters of SQLCLR objects to have defaults in the first place (this is not specific to SSDT; you currently cannot even manually supply a default for parameters of these 3 datatypes):
Support default parameter values for NVARCHAR(MAX), VARBINARY(MAX), and XML types in SQLCLR objects
Take care,
Solomon....
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply