Specifying SqlString length for a CLR stored procedure

Question

I'm writing stored procedures in C# at the moment and I've run into a problem with the size of a parameter.

So I've created a project in VS 2008 and created several stored procedures which all look a bit like this:

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SaveProgress(... SqlString logpart, ...)
    {
        ...stuff...
    }
}

Now because I've not specified anything else, when I deploy this to a database, the CREATE DATABASE statement (apparently) gets created with a nvarchar(4000) as the definition for the input parameter.

However, I regularly have to flush log parts larger than 4000 chars, so I'd like that to be nvarchar(MAX).

Now I think I can do some jiggery-pokery and use Management Studio to re-define the CREATE DATABASE statment, but I'd actually like to define the fact that I want it to be MAX in the project/solution, so the deployment gets done correctly and I don't have to start adding large wads of comments and/or documentation for anyone who needs to maintain this code after me.

Is there any way to specify this in the code or maybe in the AssemblyInfo or something like that?

score 3 · Answer 1 · answered Dec 10 '13 at 16:42

Revisiting this years later, I tried to use SqlChars in a function that read data from the database and returned a formatted string with data in it. Using SqlChars actually made the function bomb, stating that it could not find linked server System - an error message that seems to have nothing to do with the problem, as I was never referencing a linked server in the first place.

Changing the return type and parameters back to SqlString, adding [return:SqlFacet(MaxSize = -1)] attribute to the function, and adding [SqlFacet(MaxSize = -1)] to each parameter made my function work properly.

Magnus · Accepted Answer · 2013-03-03T09:04:56.390

2

Try using SqlChars. SqlChars automatically maps to NVARCHAR(MAX)

edited Mar 03 '13 at 09:04

answered Mar 30 '11 at 11:50

Magnus

45,362
8
80
118

That's an option. But - sorry for being difficult here - what if I want nvarchar(MAX)? I'd like to keep things flexible i18n-wise. – Alex Crouzen Mar 30 '11 at 12:35
Actually, `SqlChars` maps to nvarchar(MAX)! That's great! – Alex Crouzen Mar 30 '11 at 12:43
Confirmed it maps to NVARCHAR(MAX). Thanks for this! – Derreck Dean Mar 02 '13 at 16:59

Specifying SqlString length for a CLR stored procedure

2 Answers2