I have developed a .NET Stored Procedure in C# (SQL 2005 sept CTP, VS 2005 RC1).
1. I'm passing an T-SQL output variable into the .NET function. For example @TSQL varchar(max) 2. Inside the .NET procedure the variable is of type string (for example: ref string SQLText). 3. The .NET procedure populates my local variable SQLText with a quite long SQL Statement which I want to return to my T-SQL Code into the @TSQL variable. Everything is OK if my SQLText variable is less than 8000 chars long. If > 8000 I get an error stating the variable is too long.
Is there a limitation returning long variables OUT of .NET SQL Stored Procedures? Passing more than 8000 chars IN is perfectly ok...
I believe you are using Visual Studio to deploy this code. By default Visual Studio uses nvarchar(4000) for string, SqlString, char and nvarchar(MAX) for SqlChars. So using SqlChars would solve your problem. If you want to use string, you can deploy manually using Create Function statement in SQL Server. Alternatively if you want to automate in Visual Studio, you can use post-deploy script to issue an ALTER FUNCTION statement.