SQLCLR: SQLChars or SqlString

Recently, i came across a post on the forums which focused on a strange error coming from a CLR function. Essentially, the OP was attempting to execute a dynamic sql string in a function (which isn't possible in a regular UDF) so he went down the CLR route. All sounds simple enough, but when calling his CLR UDF, he kept receiving the following error:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "DynamicSQL":
System.Data.SqlClient.SqlException: Could not find server 'System' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at UserDefinedFunctions.DynamicSQL(SqlChars Query, SqlInt32 OperationId, SqlSingle ConstantValue)

Here is a sample of the source code of the CLR function:

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static string DynamicSQL(SqlChars Query)
{
string value;
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Sandbox;Integrated Security=True;");
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(Query.ToString(), conn);
value = cmd.ExecuteScalar().ToString();
conn.Close();
}

return value;
}

SELECT dbo.DynamicSql('SELECT 1 AS t', 1, 1)

Nothing in this looked particularly terrible to start but the error wasn't that cryptic - it was parsing the query with 4 part naming. Hardcoding the Query in the CLR resolved the issue indicating there was obviously something amiss with the Query input parameter.

A bit of quick debugging allowed me to establish what the problem was. When casting Query.ToString, the value that was being returned was System.Data.SqlTypes.SqlChars. And this value happens to be in the same format a 4 part object name in SQL Server. Voila!! There is the issue. A bit of digging online, and i found that casting SqlChars.ToString returns a String that represents the current Object. (Inherited from Object.).

A method of resolving this would either be to change the input parameter to be of type SqlString or to change the casting to:

string value = Query.ToSqlString().ToString();