CLR function will not return more than 4000 chars

Question

first time posting, please be kind! :-) New to VB/C#, Trying to fix up a CLR function that calls a web service. I've cobbled together the following:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Globalization;

// For the SQL Server integration
using Microsoft.SqlServer.Server;

// Other things we need for WebRequest
using System.Net;
using System.Text;
using System.IO;

public partial class UserDefinedFunctions
{

// Function to return a web URL as a string value.
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

public static SqlChars GET(SqlString uri, SqlString username, SqlString passwd)
{
    // The SqlPipe is how we send data back to the caller
    SqlPipe pipe = SqlContext.Pipe;
    SqlChars document;

    // Set up the request, including authentication
    WebRequest req = WebRequest.Create(Convert.ToString(uri));
    if (Convert.ToString(username) != null & Convert.ToString(username) != "")
    {
        req.Credentials = new NetworkCredential(
            Convert.ToString(username),
            Convert.ToString(passwd));
    }
    ((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";

    // Fire off the request and retrieve the response.
    // We'll put the response in the string variable "document".
    WebResponse resp = req.GetResponse();
    Stream dataStream = resp.GetResponseStream();
    StreamReader rdr = new StreamReader(dataStream);
    document = new SqlChars(rdr.ReadToEnd());
    if (document.IsNull)
    {
        return SqlChars.Null;
    }

    // Close up everything...
    rdr.Close();
    dataStream.Close();
    resp.Close();

    // .. and return the output to the caller.
    return (document);
}

This will only return 4000 characters even though it is coded for sqlChars. The API call could potentially return upwards of 14MB during a full inventory pull. If I understand correctly sqlChars goes to varchar(max) (or nvarchar(max)) ... What am I missing here? compiled for .Net 3.0 on sql server 2005 if that helps... Andy

Answer 1

Neither SqlChars nor SqlString are necessarily tied to either NVARCHAR(MAX) or NVARCHAR(4000). In .NET, a string is NVARCHAR(MAX) and so both SqlChars and SqlString can map to both NVARCHAR(MAX) and NVARCHAR(4000). The only thing that determines whether it is MAX or 4000 (or technically 1 through 4000) is the datatype declared for the input parameter, return type, or result set column in the CREATE [PROCEDURE | FUNCTION | AGGREGATE] statement. So, you first need to check there. It is clearly defined as NVARCHAR(4000). If you simply change it (i.e. ALTER FUNCTION) to be NVARCHAR(MAX) it will return everything.

Some other notes:

1. The SQLCLR API only allows for NVARCHAR, not VARCHAR.

2. If you are using Visual Studio / SSDT, you can decorate your method to provide a hint for the SSDT DDL generation process that will tell it to use NVARCHAR(MAX). You do that by adding the following above the [SqlFunction()] decorator:

   [return: SqlFacet(MaxSize = -1)]
   

   Had you wanted the return type to be NVARCHAR(250), you would use MaxSize = 250.

3. You need to be very careful when using external resources in this manner within SQLCLR. The AppDomain can stay loaded for a long time and is shared by all Sessions. So if you have an exception and there is no using() construct and no try ... finally in which you call Dispose() on the opened resource(s), then you can be orphaning those handles for a long time. That can result in locking of files (if it's a file system operation) or can use up networking sockets. You need to be very thorough in your error handling and resource cleanup here! VERY! I have some other answers here with additional notes, such as: SQL CLR Web Service Call: Limiting Overhead.

   This means that, in its current form, the code shown in the question is very risky due to not using either the using() construct or try...catch...finally. AND IN FACT, you are already doing a VERY BAD thing by returning return SqlChars.Null; before the 3 .Close() statements. If document.IsNull ever returns true, then this code will orphan that network connection and those external resources!!!!!

4. If this UDF will only ever be called by one session / process at a time, and never multiple, then you are ok. Otherwise, you are going to run into the default number of external connections being 2 problem that causes additional connections to wait until one of the two allowed connections closes. In that case, you will need to set the default connection count in the ServicePointManager class.
5. There is no reason to call Convert.ToString({input_parameter}) as you are doing. All Sql* types have a Value property that returns the passed in value in the expected .NET type. So you would instead simply use uri.Value and username.Value, etc.

6. There is no reason to have this method attribute property specified:

   DataAccess = DataAccessKind.Read
   

   You only specify Read if you are making a connection to the DB and accessing data. There is no SqlConnection in this code so you don't need this property which is a performance hit. Along those same lines, you also do not need using System.Data.SqlClient;.

7. For more info on working with SQLCLR in general, please see the list of resources I have posted on SQLCLR Info
8. For anyone interested in a fully functional, pre-done implementation of a WebRequest SQLCLR UDF, such a function already exists in the SQL# library (that I wrote). The function is named INET_GetWebPages and supports passing in all (or at least most) HTTP headers including custom headers, sending POST data, getting back binary data as VARBINARY, overriding the default 2 connection per URI maximum, etc. Please note that while most functions are available in the Free version, this particular function is only available in the Full (i.e. paid) version.