Wednesday, June 27, 2012

SQL Server 2012 Reporting Services Error

We were upgrading one of our SQL Server 2008 R2 server to SQL Server 2012. Everything went smooth expect for one report in which was using reporting services. This report was calling the reporting service webservice and was passing in a huge xml parameter. This XML parameter would work fine for smaller string lengths but failed for larger strings. The error was specifically:

library!ReportServer_0-2!4ac!05/11/2012-14:26:30:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: Internal error, Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.UriFormatException: Invalid URI: The Uri string is too long.
   at System.Uri.EscapeString(String input, Int32 start, Int32 end, Char[] dest, Int32& destPos, Boolean isUriString, Char force1, Char force2, Char rsvd)
   at System.Uri.EscapeDataString(String stringToEscape)
   at Microsoft.ReportingServices.Common.UrlUtil.UrlEncode(String input)
   at Microsoft.ReportingServices.ReportProcessing.ParameterInfoCollection.UrlEncodeSingleParam(StringBuilder url, String name, Object val, Func`2 cs)
   at Microsoft.ReportingServices.ReportProcessing.ParameterInfoCollection.ToUrl(Boolean skipInternalParameters, Func`2 cs)
   at Microsoft.ReportingServices.Library.ReportExecutionBase.WriteParametersToJobContext(RunningJobContext jobContext)
   at Microsoft.ReportingServices.Library.ReportExecutionBase.Execute()
   at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
   --- End of inner exception stack trace ---;

As you can see, the error message is “Invalid URI: The Uri string is too long.”. We knew the problem was with the huge XML parameter and wanted to find out what got changed from SQL Server 2008 R2 to SQL Server 2012. So, I opened up reflector and started comparing the code in 2008 R2 with the one from SQL Server 2012.



SQL Server 2008 R2 execution path
private void WriteParametersToJobContext(RunningJobContext jobContext)
{
if (this.EffectiveParameters != null)
{
if (this.EffectiveParameters.Count > 0)
{
jobContext.ExecutionInfo.Parameters = this.EffectiveParameters.ToUrl();
}
}
else if ((this.RequestInfo.ReportContext.ReportParameters != null) && (this.RequestInfo.ReportContext.ReportParameters.Count > 0))
{
jobContext.ExecutionInfo.Parameters = ParameterInfoCollection.ToUrl(this.RequestInfo.ReportContext.ReportParameters);
}
}

public string ToUrl()
{
StringBuilder url = new StringBuilder();
foreach (ParameterInfo info in this)
{
if (info.Values != null)
{
foreach (object obj2 in info.Values)
{
EncodeSingleParamForUrl(url, info.Name, obj2);
}
}
}
return url.ToString();
}


private static void EncodeSingleParamForUrl(StringBuilder url, string name, object val)
{
if (url.Length > 0)
{
url.Append('&');
}
EncodeForUrlAndAppend(url, name);
if (val == null)
{
EncodeForUrlAndAppend(url, ":isnull");
}
url.Append('=');
if (val == null)
{
url.Append("true");
}
else
{
EncodeForUrlAndAppend(url, val.ToString());
}
}


private static void EncodeForUrlAndAppend(StringBuilder builder, string unencodedStr)
{
int length = builder.Length;
builder.Append(unencodedStr);
builder.Replace("=", "%3D", length, builder.Length - length);
builder.Replace("&", "%26", length, builder.Length - length);
}












SQL Server 2012 execution path


private void WriteParametersToJobContext(RunningJobContext jobContext)
{
if (this.EffectiveParameters != null)
{
if (this.EffectiveParameters.Count > 0)
{
jobContext.ExecutionInfo.Parameters = this.EffectiveParameters.ToUrl(false);
}
}
else if ((this.RequestInfo.ReportContext.RSRequestParameters.ReportParameters != null) && (this.RequestInfo.ReportContext.RSRequestParameters.ReportParameters.Count > 0))
{
jobContext.ExecutionInfo.Parameters = ParameterInfoCollection.ToUrl(this.RequestInfo.ReportContext.RSRequestParameters.ReportParameters);
}
}


public string ToUrl(bool skipInternalParameters)
{
return this.ToUrl(skipInternalParameters, null);
}

public string ToUrl(bool skipInternalParameters, Func<object, string> cs)
{
StringBuilder url = new StringBuilder();
foreach (ParameterInfo info in this)
{
if (skipInternalParameters && !info.PromptUser)
{
continue;
}
if (info.Values != null)
{
foreach (object obj2 in info.Values)
{
UrlEncodeSingleParam(url, info.Name, obj2, cs);
}
continue;
}
UrlEncodeSingleParam(url, info.Name, null, cs);
}
return url.ToString();
}


private static void UrlEncodeSingleParam(StringBuilder url, string name, object val, Func<object, string> cs)
{
if (url.Length > 0)
{
url.Append('&');
}
url.Append(UrlEncodeString(name));
if (val == null)
{
url.Append(":isnull=true");
}
else
{
url.Append('=');
url.Append(UrlEncodeString((cs == null) ? val.ToString() : cs(val)));
}
}

private static string UrlEncodeString(string param)
{
return UrlUtil.UrlEncode(param).Replace("'", "%27");
}


public static string UrlEncode(string input)
{
if (input == null)
{
return null;
}
return Uri.EscapeDataString(input);
}

public static string EscapeDataString(string stringToEscape)
{
if (stringToEscape == null)
{
throw new ArgumentNullException("stringToUnescape");
}
if (stringToEscape.Length == 0)
{
return string.Empty;
}
int destPos = 0;
char[] chArray = EscapeString(stringToEscape, 0, stringToEscape.Length, null, ref destPos, false, 0xffff, 0xffff, 0xffff);
if (chArray == null)
{
return stringToEscape;
}
return new string(chArray, 0, destPos);
}


private static unsafe char[] EscapeString(string input, int start, int end, char[] dest, ref int destPos, bool isUriString, char force1, char force2, char rsvd)
{
if ((end - start) >= 0xfff0)
{
throw GetException(ParsingError.SizeLimit);
}
........




As you can see above, SQL Server 2008 R2 while using the String Builder type is not performing any checks on the size limit. But SQL Server 2012 is throwing an exception when the site is greater than 65520 characters. This was the problem and we ended up modifying our code to fix this issue.