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();



SQLCLR: Publish SSRS reports from CLR stored procedure

Over the past few years, an task i've come across on a frequent basis is how to produce reports from within an application. The scenario is typically something like: SSIS Package imports data, transforms it and then produces output into a table. The user then wants the data to be rendered into excel and made available to them.

I'd really like to see a system T-SQL stored procedure to be able to do this- something along the lines of sys.sp_renderreport @reportname, @format, @location etc

However, for now this doesn't exist so its down to you to make it happen with the tools at your disposal. SSRS provides the reportexecution2005 webservice which you can use to render reports and its fairly straightforward to call this from a CLR stored procedure.

Here is the source code for the CLR stored procedure:

using System;
using System.Data;
using System.Net;
using System.IO;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Collections.Generic;
using System.Xml.Serialization;
using RenderReport.ReportExecution2005;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RenderReport(string reportPath, string filePath, string format, SqlXml reportParams)
{
ReportExecutionService res = new ReportExecutionService();
res.Url = "http://DEVSERVER/reportserver_DEV/reportexecution2005.asmx?wsdl";
res.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Prepare Render arguments
string historyID = null;
string deviceInfo = null;
string extension;
string encoding;
string mimeType;
Warning[] warnings;
string[] streamIDs;

int i = 0;

XmlDocument paramXML = new XmlDocument();
paramXML.LoadXml(reportParams.Value);
var paramNodes = paramXML.SelectNodes("//reportparams/param");

ParameterValue[] prms = new ParameterValue[paramNodes.Count];

foreach (XmlNode param in paramNodes)
{
prms[i] = new ParameterValue();
prms[i].Label = param.Attributes["label"].Value;
prms[i].Name = param.Attributes["name"].Value;
prms[i].Value = param.Attributes["value"].Value;
i++;
}

res.LoadReport(reportPath, historyID);

// set params if we have any
if (i > 0)
{
res.SetExecutionParameters(prms, "en-us");
}

byte[] results = res.Render(format, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);

// Open a file stream and write out the report
FileStream stream = File.OpenWrite(filePath);
stream.Write(results, 0, results.Length);
stream.Close();
}
};


A couple of things to note,
1) ReportParams need to be set in this format:
<reportparams><param name="MyName"label="My Name"value="Richard"</reportparams>

2) You will need to follow the steps in this KB article.
3) You will find that you need to deploy this using the EXTERNAL ACCESS permission set.
4) EXTERNAL_ACCESS CLR routines will be executed as the SQL Service Account so it will need appropriate permissions if you want to save reports to a remote location.