CODEWITHSUNDEEP
c#visual-studio-2008sql-server-2008stored-proceduresclr
Alex Crouzen
Alex Crouzen

Reputation: 75

Specifying SqlString length for a CLR stored procedure

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?

Upvotes: 2

Views: 3856

Answers (2)

Derreck Dean
Derreck Dean

Reputation: 3766

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.

Upvotes: 3

Magnus
Magnus

Reputation: 46937

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

Upvotes: 2

Related Questions