Worked on a case yesterday where the customer was getting errors when running a Data Driven subscription.
In the RS Log, we saw something similar to the following:
library!WindowsService_15!950!12/10/2009-10:49:11:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'Param1' is not a valid value., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'Param1' is not a valid value.
My initial thought was that either the value we were supplying for the Data Driven Subscription didn’t match with the Parameter Definition, or it didn’t match with what we listed for the Valid Values. Meaning either it was Null and the parameter didn’t accept null values, or it just wasn’t in the list of valid values.
The customer had spent a lot of time going over the data being supplied to the report already as well as the Parameters used in the reports. Everything seemed to match. When I reviewed it with him, they also seemed to match.
The Parameters were using a DataSet query for the valid values list.
The query was something similar to the following:
select distinct ParameterValue, ParameterValue as descrip from tblValidValues where ParameterName = 'Param1' UNION select 'ALL', '*ALL' as descrip
Which produced results similar to the following:
We noticed that the issue was only occurring if the Parameter value within the subscription table was anything but “ALL”. If we entered something like “Arizona” or “Texas”, we would get the error. And, unfortunately, the verbose log output really didn’t offer any hints as to what was going on.
I went through the basic things to check, which the customer had also done before calling in. This included making sure we were hitting the data source we thought we were hitting for the Parameter data and subscription data. Checking the ExecutionLog which was missing entries for the failed runs. It only contained entries for the successful runs. Nothing was panning out.
InvalidReportParameterException is a very specific exception. Being that all the data looks good visually, I would make an assumption that we might have a trailing space issue. I know that the tblDDSub table doesn’t have any trailing spaces because I just re-entered them by hand, so it probably has to do with where the Valid Values are coming from which would be my tblValidValues table.
select distinct ParameterValue, ParameterValue as descrip, LEN(ParameterValue) as ParamLength from tblValidValues where ParameterName = 'Param1' UNION select 'ALL', '*ALL' as descrip, 0
Well, that looks fine. Doesn’t indicate that I have trailing spaces. I will not claim to be a expert on T-SQL as I don’t spend my days in that. When I saw this, I immediately looked up the documentation on the LEN command to see if it was trimming because there was nothing else that explained the exception.
LEN (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms190329.aspx
“Returns the number of characters of the specified string expression, excluding trailing blanks.”
That would explain it. Looks like DATALENGTH should get the true length.
select distinct ParameterValue, ParameterValue as descrip, LEN(ParameterValue) as ParamLength, DATALENGTH(ParameterValue) as ParamDataLength from tblValidValues where ParameterName = 'Param1' UNION select 'ALL', '*ALL' as descrip, 0, 0
Summary
We definitely had a trailing space issue. After correcting the trailing space, the subscription ran successfully. What made this difficult to find was that visual inspection showed that everything was fine from a parameter value perspective. It came down to the space we couldn’t see.
Techie Details
Being that I love dumps, I had also requested a dump to prove that the trailing space was the cause of the issue. This can be done within the rsreportserver.config file using SQLDumper. We wanted to get a full dump, so that we could use the .NET Debugger Extension. In a mini dump, we only have a certain level of indirection in the data that it collects. We only have full process memory if we do a full dump. .NET debugging doesn’t work well without a full dump.
<!-- <Add Key="WatsonFlags" Value="0x0430" /> full dump--> <!-- <Add Key="WatsonFlags" Value="0x0428" /> minidump --> <!-- <Add Key="WatsonFlags" Value="0x0002" /> no dump--> <Add Key="WatsonFlags" Value="0x0430"/> <Add Key="WatsonDumpOnExceptions" Value="Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException,Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException,Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException"/>
This will generate a dump for each failed subscription attempt. In my case, I had 2, which produce two full dumps.
This first thing I need to do with Managed Dumps is to load the SOS debugging extension which ships with the debugger.
0:037> .loadby sos mscorwks
Then I want to look at the call stack where the exception occured.
0:037> !clrstack OS Thread Id: 0xf18 (37) Child-SP RetAddr Call Site 0000000007c8df30 000007ff01790bc2 DomainBoundILStubClass.IL_STUB() 0000000007c8dff0 000007ff017905df Microsoft.ReportingServices.Diagnostics.Dumper+<>c__DisplayClass2.<DumpHere>b__0() 0000000007c8e0d0 000007fef6e195a9 Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext+<>c__DisplayClass4.<RunFromRestrictedCasContext>b__3(System.Object) 0000000007c8e120 000007ff0179051e System.Security.SecurityContext.Run(System.Security.SecurityContext, System.Threading.ContextCallback, System.Object) 0000000007c8e160 000007ff01790228 Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.RunFromRestrictedCasContext(Microsoft.ReportingServices.Diagnostics.ContextBody) 0000000007c8e1c0 000007ff015efb70 Microsoft.ReportingServices.Diagnostics.Dumper.DumpHere(System.Exception, System.String, Boolean) 0000000007c8e250 000007ff015ef937 Microsoft.ReportingServices.Diagnostics.Utilities.RSException..ctor(Microsoft.ReportingServices.Diagnostics.Utilities.ErrorCode, System.String, System.Exception, Microsoft.ReportingServices.Diagnostics.Utilities.RSTrace, System.String, System.Diagnostics.TraceLevel, System.Object[]) 0000000007c8e2a0 000007ff015ef710 Microsoft.ReportingServices.Diagnostics.Utilities.ReportCatalogException..ctor(Microsoft.ReportingServices.Diagnostics.Utilities.ErrorCode, System.String, System.Exception, System.String, System.Object[]) 0000000007c8e310 000007ff015e21f9 Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException..ctor(System.String) 0000000007c8e360 000007ff00386a48 Microsoft.ReportingServices.ReportProcessing.ParameterInfoCollection.ValidateInputValues(Microsoft.ReportingServices.ReportProcessing.ParamValues, Boolean) 0000000007c8e4b0 000007ff015ed2f1 Microsoft.ReportingServices.Library.SubscriptionManager.ValidateSubscriptionParameters(System.String, Microsoft.ReportingServices.Library.Soap.ParameterValueOrFieldReference[], Microsoft.ReportingServices.Diagnostics.JobType) 0000000007c8e5e0 000007ff015ed03a Microsoft.ReportingServices.Library.NotificationQueueWorker.HandleNotification(Microsoft.ReportingServices.Library.QueueItem) 0000000007c8e730 000007ff0037a86c Microsoft.ReportingServices.Library.NotificationQueueWorker.QueueWorker(Microsoft.ReportingServices.Library.QueueItem) 0000000007c8e760 000007fef6db2bbb Microsoft.ReportingServices.Library.QueuePollWorker.WorkItemStart(System.Object) 0000000007c8e860 000007fef6e27411 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object) 0000000007c8e8b0 000007fef6e2724f System.Threading._ThreadPoolWaitCallback.PerformWaitCallbackInternal(System.Threading._ThreadPoolWaitCallback) 0000000007c8e900 000007fef7e4d502 System.Threading._ThreadPoolWaitCallback.PerformWaitCallback(System.Object)
You’ll notice that we are on a x64 platform by the way the memory addresses look. They are a Quad Word as opposed to a Double Word value. The exception appears to be thrown from ParameterInfoCollection.ValidateInputValues(). We can look at this code using Reflector because it is a .NET assembly. In our case, the exception is being thrown from the following code segment:
We can validate that based on what is contained within ParameterInfoCollection. !dso will show us the objects on the stack. ParameterInfoCollection should be one of those objects.
0:037> !dso OS Thread Id: 0xf18 (37) RSP/REG Object Name ... 0000000007c8e360 0000000140f37c70 Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException 0000000007c8e368 0000000140f37a68 System.String 0000000007c8e370 0000000140f37ae0 System.String 0000000007c8e3f8 0000000140f37ae0 System.String 0000000007c8e408 0000000140f34510 Microsoft.ReportingServices.ReportProcessing.ParamValues 0000000007c8e418 0000000140f37c58 System.Object 0000000007c8e420 0000000140f37c58 System.Object 0000000007c8e430 0000000140f37b08 Microsoft.ReportingServices.ReportProcessing.ParamValueList 0000000007c8e460 0000000140c20f90 Microsoft.ReportingServices.Library.RSService 0000000007c8e468 0000000140c21620 Microsoft.ReportingServices.Library.SubscriptionManager 0000000007c8e470 0000000140edf4f0 System.Collections.Specialized.NameValueCollection 0000000007c8e478 0000000140edf378 System.Collections.Hashtable 0000000007c8e480 0000000140ed85e8 System.Object[] (Microsoft.ReportingServices.Library.Soap.ParameterValue[]) 0000000007c8e488 0000000140c20f90 Microsoft.ReportingServices.Library.RSService 0000000007c8e490 0000000140edf2a8 Microsoft.ReportingServices.Library.ReportParameterDefinition 0000000007c8e4a0 0000000140f34510 Microsoft.ReportingServices.ReportProcessing.ParamValues 0000000007c8e4b0 0000000140ee42c8 Microsoft.ReportingServices.ReportProcessing.ParameterInfoCollection 0000000007c8e4b8 0000000140f34510 Microsoft.ReportingServices.ReportProcessing.ParamValues 0000000007c8e4e0 0000000140eded70 Microsoft.ReportingServices.Library.ReportSnapshot 0000000007c8e4f0 0000000140edd188 System.String 0000000007c8e4f8 0000000140edf4f0 System.Collections.Specialized.NameValueCollection 0000000007c8e508 0000000140ed4a88 Microsoft.ReportingServices.Diagnostics.DataDrivenSubscriptionJobType 0000000007c8e538 0000000140ed86b8 System.Xml.XmlChildEnumerator 0000000007c8e560 0000000140ed4a88 Microsoft.ReportingServices.Diagnostics.DataDrivenSubscriptionJobType 0000000007c8e568 0000000140ee42c8 Microsoft.ReportingServices.ReportProcessing.ParameterInfoCollection 0000000007c8e570 0000000140ed8708 Microsoft.ReportingServices.Diagnostics.CatalogItemContext 0000000007c8e580 0000000140edff18 Microsoft.ReportingServices.Library.RSService+WorkspaceContext 0000000007c8e590 0000000140ed4a88 Microsoft.ReportingServices.Diagnostics.DataDrivenSubscriptionJobType 0000000007c8e598 0000000101220f20 Microsoft.ReportingServices.Library.NotificationQueueItem 0000000007c8e5a0 0000000140c21410 Microsoft.ReportingServices.Library.ConnectionManager 0000000007c8e5a8 0000000140c20f90 Microsoft.ReportingServices.Library.RSService 0000000007c8e5b0 0000000140c21620 Microsoft.ReportingServices.Library.SubscriptionManager ...
We see the ParameterInfoCollection here. Above that, we also see ParamValues. ParamValues will contain the values that we actually passed into the report. Lets have a look at that first.
0:037> !do 0000000140f34510 Name: Microsoft.ReportingServices.ReportProcessing.ParamValues MethodTable: 000007ff0116a4c0 EEClass: 000007ff01564280 Size: 96(0x60) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6ef67b0 400098d 8 ...ashtable+bucket[] 0 instance 0000000140f346e8 buckets 000007fef6ef5f00 400098e 38 System.Int32 1 instance 2 count 000007fef6ef5f00 400098f 3c System.Int32 1 instance 0 occupancy 000007fef6ef5f00 4000990 40 System.Int32 1 instance 7 loadsize 000007fef6eeb388 4000991 44 System.Single 1 instance 0.720000 loadFactor 000007fef6ef5f00 4000992 48 System.Int32 1 instance 2 version 000007fef6eede60 4000993 4c System.Boolean 1 instance 0 isWriterInProgress 000007fef6ef6330 4000994 10 ...tions.ICollection 0 instance 0000000000000000 keys 000007fef6ef6330 4000995 18 ...tions.ICollection 0 instance 0000000000000000 values 000007fef6ef6f98 4000996 20 ...IEqualityComparer 0 instance 0000000000000000 _keycomparer 000007fef6eee580 4000997 28 System.Object 0 instance 0000000000000000 _syncRoot 000007fef6f33cd8 4000998 30 ...SerializationInfo 0 instance 0000000000000000 m_siInfo 000007fef6ef65e8 40005ea 50 ...ections.Hashtable 0 instance 0000000140f34570 m_fields
This contains a Hashtable with the values. To get the actual values, we will need to use the dq command instead of an actual command that is in the SOS extension. dq will dump Quad Word values.
0:037> dq 0000000140f346e8 00000001`40f346e8 000007fe`f6ef67b0 00000000`0000000b 00000001`40f346f8 00000001`40f37ba0 00000001`40f37bf0 <-- Param2 00000001`40f34708 00000000`40734a1d 00000000`00000000 00000001`40f34718 00000000`00000000 00000000`00000000 00000001`40f34728 00000000`00000000 00000000`00000000 00000001`40f34738 00000000`00000000 00000001`40f37a68 <-- Param1 00000001`40f34748 00000001`40f37b08 00000000`2869a7ee 00000001`40f34758 00000000`00000000 00000000`00000000
0:037> !do 00000001`40f37a68 Name: System.String MethodTable: 000007fef6eeec90 EEClass: 000007fef6afb038 Size: 38(0x26) bytes (C:\Windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll) String: Param1 <— this is the name of the parameter 0:037> !do 00000001`40f37b08 Name: Microsoft.ReportingServices.ReportProcessing.ParamValueList MethodTable: 000007ff016f8cf8 EEClass: 000007ff01719820 Size: 40(0x28) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6ee4748 400094c 8 System.Object[] 0 instance 0000000140f37b30 _items 000007fef6ef5f00 400094d 18 System.Int32 1 instance 1 _size 000007fef6ef5f00 400094e 1c System.Int32 1 instance 1 _version 0:037> !da 0000000140f37b30 <— remember, it is an array so we have to do !da (dumpArray) instead of !do (dumpObject) Name: System.Object[] MethodTable: 000007fef6ee4748 EEClass: 000007fef6afb660 Size: 64(0x40) bytes Array: Rank 1, Number of elements 4, Type CLASS Element Methodtable: 000007fef6eee580 [0] 0000000140f378e8 [1] null [2] null [3] null 0:037> !do 0000000140f378e8 Name: Microsoft.ReportingServices.ReportProcessing.ParamValue MethodTable: 000007ff016f8c28 EEClass: 000007ff01719778 Size: 48(0x30) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007ff0116a4c0 40005ef 8 ...ssing.ParamValues 0 instance 0000000140f34510 m_parent 000007fef6eeec90 40005f0 10 System.String 0 instance 0000000140f37a68 m_name 000007fef6eeec90 40005f1 18 System.String 0 instance 0000000140f37ae0 m_value 000007fef6eeec90 40005f2 20 System.String 0 instance 0000000000000000 m_fieldName 0:037> !do 0000000140f37ae0 Name: System.String MethodTable: 000007fef6eeec90 EEClass: 000007fef6afb038 Size: 36(0x24) bytes (C:\Windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll) String: Texas Fields: MT Field Offset Type VT Attr Value Name 000007fef6ef5f00 4000096 8 System.Int32 1 instance 6 m_arrayLength 000007fef6ef5f00 4000097 c System.Int32 1 instance 5 m_stringLength
0:037> !do 00000001`40f37a68 Name: System.String MethodTable: 000007fef6eeec90 EEClass: 000007fef6afb038 Size: 38(0x26) bytes (C:\Windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll) String: Param1 <— this is the name of the parameter
0:037> !do 00000001`40f37b08 Name: Microsoft.ReportingServices.ReportProcessing.ParamValueList MethodTable: 000007ff016f8cf8 EEClass: 000007ff01719820 Size: 40(0x28) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6ee4748 400094c 8 System.Object[] 0 instance 0000000140f37b30 _items 000007fef6ef5f00 400094d 18 System.Int32 1 instance 1 _size 000007fef6ef5f00 400094e 1c System.Int32 1 instance 1 _version
0:037> !da 0000000140f37b30 <— remember, it is an array so we have to do !da (dumpArray) instead of !do (dumpObject) Name: System.Object[] MethodTable: 000007fef6ee4748 EEClass: 000007fef6afb660 Size: 64(0x40) bytes Array: Rank 1, Number of elements 4, Type CLASS Element Methodtable: 000007fef6eee580 [0] 0000000140f378e8 [1] null [2] null [3] null
0:037> !do 0000000140f378e8 Name: Microsoft.ReportingServices.ReportProcessing.ParamValue MethodTable: 000007ff016f8c28 EEClass: 000007ff01719778 Size: 48(0x30) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007ff0116a4c0 40005ef 8 ...ssing.ParamValues 0 instance 0000000140f34510 m_parent 000007fef6eeec90 40005f0 10 System.String 0 instance 0000000140f37a68 m_name 000007fef6eeec90 40005f1 18 System.String 0 instance 0000000140f37ae0 m_value 000007fef6eeec90 40005f2 20 System.String 0 instance 0000000000000000 m_fieldName
0:037> !do 0000000140f37ae0 Name: System.String MethodTable: 000007fef6eeec90 EEClass: 000007fef6afb038 Size: 36(0x24) bytes (C:\Windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll) String: Texas Fields: MT Field Offset Type VT Attr Value Name 000007fef6ef5f00 4000096 8 System.Int32 1 instance 6 m_arrayLength 000007fef6ef5f00 4000097 c System.Int32 1 instance 5 m_stringLength
So, we know that “Texas” was passed into the report, and had a length of 5 characters which looks good. Lets move onto the Valid Values list.
We can dump out ParameterInfoCollection using !do (dumpObj). This is what will get us to the Parameters of the actual report along with the Valid Value list that is being populated with the query we defined.
0:037> !do 0000000140ee42c8 Name: Microsoft.ReportingServices.ReportProcessing.ParameterInfoCollection MethodTable: 000007ff01580038 EEClass: 000007ff0156ae10 Size: 48(0x30) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6ee4748 400094c 8 System.Object[] 0 instance 0000000140ee43c0 _items 000007fef6ef5f00 400094d 18 System.Int32 1 instance 2 _size 000007fef6ef5f00 400094e 1c System.Int32 1 instance 2 _version 000007fef6eee580 400094f 10 System.Object 0 instance 0000000000000000 _syncRoot 000007fef6ee4748 4000950 388 System.Object[] 0 shared static emptyArray >> Domain:Value 000000000081a100:000000007fff1f80 0000000002b04080:00000000c002d9e8 0000000002c3a810:000000007ffff030 000000000210c080:000000014018fe48 << 000007ff0116fe30 40005f3 20 System.Int32 1 instance 0 m_userProfileState 000007fef6eede60 40005f4 24 System.Boolean 1 instance 1 m_validated 000007ff0158a7b8 40005f5 b8 ...tence.Declaration 0 static 0000000140cd1f48 m_Declaration
_items looks to be the interesting item here. Lets dump that out. We can use !da (dumpArray) because it is an object array.
0:037> !da 0000000140ee43c0 Name: System.Object[] MethodTable: 000007fef6ee4748 EEClass: 000007fef6afb660 Size: 64(0x40) bytes Array: Rank 1, Number of elements 4, Type CLASS Element Methodtable: 000007fef6eee580 [0] 0000000140ee4320 [1] 0000000140ee4428 [2] null [3] null
0:037> !do 0000000140ee4320 Name: Microsoft.ReportingServices.ReportProcessing.ParameterInfo MethodTable: 000007ff0158c790 EEClass: 000007ff015a4db8 Size: 120(0x78) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6eeec90 40005b2 8 System.String 0 instance 0000000140ee3388 m_name 000007ff0158bde8 40005b3 20 System.Int32 1 instance 18 m_dataType 000007fef6eede60 40005b4 28 System.Boolean 1 instance 0 m_nullable 000007fef6eede60 40005b5 29 System.Boolean 1 instance 1 m_promptUser 000007fef6eede60 40005b6 2a System.Boolean 1 instance 0 m_usedInQuery 000007fef6eede60 40005b7 2b System.Boolean 1 instance 0 m_allowBlank 000007fef6eede60 40005b8 2c System.Boolean 1 instance 0 m_multiValue 000007fef6ee4748 40005b9 10 System.Object[] 0 instance 0000000000000000 m_defaultValues 000007ff0158bf38 40005ba 24 System.Int32 1 instance 2 m_usedInQueryAsDefined 000007fef6ef65e8 40005bb 18 ...ections.Hashtable 0 instance 0000000000000000 m_dependencies 000007ff0158a7b8 40005bc a8 ...tence.Declaration 0 static 0000000140cd5728 m_Declaration 000007fef6ee4748 40005d9 30 System.Object[] 0 instance 0000000000000000 m_values 000007fef6ee4748 40005da 38 System.Object[] 0 instance 0000000140ee4398 m_labels 000007fef6eede60 40005db 2d System.Boolean 1 instance 1 m_isUserSupplied 000007fef6eede60 40005dc 2e System.Boolean 1 instance 1 m_dynamicValidValues 000007fef6eede60 40005dd 2f System.Boolean 1 instance 0 m_dynamicDefaultValue 000007fef6eede60 40005de 68 System.Boolean 1 instance 0 m_dynamicPrompt 000007fef6eeec90 40005df 40 System.String 0 instance 0000000140ee3708 m_prompt 000007ff01580038 40005e0 48 ...terInfoCollection 0 instance 0000000000000000 m_dependencyList 000007ff0158b838 40005e1 50 ...ng.ValidValueList 0 instance 0000000140f2be18 m_validValues 000007fef6ef5db0 40005e2 58 System.Int32[] 0 instance 0000000000000000 m_dependencyIndexList 000007fef6eede60 40005e3 69 System.Boolean 1 instance 1 m_valuesChanged 000007ff0158c628 40005e4 60 System.Int32 1 instance 1 m_state 000007fef6eede60 40005e5 6a System.Boolean 1 instance 0 m_othersDependOnMe 000007fef6eede60 40005e6 6b System.Boolean 1 instance 0 m_useExplicitDefaultValue 000007fef6ef5f00 40005e7 64 System.Int32 1 instance -1 m_indexInCollection 000007ff0158a7b8 40005e8 b0 ...tence.Declaration 0 static 0000000140cd5508 m_Declaration 0:037> !do 0000000140f2be18 Name: Microsoft.ReportingServices.ReportProcessing.ValidValueList MethodTable: 000007ff0158b838 EEClass: 000007ff015a4ab0 Size: 40(0x28) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6ee4748 400094c 8 System.Object[] 0 instance 0000000140f2eee8 _items 000007fef6ef5f00 400094d 18 System.Int32 1 instance 4 _size 000007fef6ef5f00 400094e 1c System.Int32 1 instance 4 _version 0:037> !da 0000000140f2eee8 Name: System.Object[] MethodTable: 000007fef6ee4748 EEClass: 000007fef6afb660 Size: 64(0x40) bytes Array: Rank 1, Number of elements 4, Type CLASS Element Methodtable: 000007fef6eee580 [0] 0000000140f2eeb0 <-- ALL [1] 0000000140f2f160 <-- Arizona [2] 0000000140f2f3d0 <-- New York [3] 0000000140f2f630 <-- Texas 0:037> !do 0000000140f2f630 Name: Microsoft.ReportingServices.ReportProcessing.ValidValue MethodTable: 000007ff0158db68 EEClass: 000007ff015a6380 Size: 56(0x38) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6eee580 400059c 8 System.Object 0 instance 0000000140f2f4d0 m_value 000007fef6eeec90 400059d 10 System.String 0 instance 0000000140f2f548 m_label 000007fef6eeec90 400059e 18 System.String 0 instance 0000000000000000 m_stringValue 000007fef6eeec90 400059f 20 System.String 0 instance 0000000000000000 m_cachedAutogenLabel 000007fef6eede60 40005a0 28 System.Boolean 1 instance 1 m_labelAutoGenerated 000007ff0158a7b8 40005a1 a0 ...tence.Declaration 0 static 0000000140da1dd8 m_Declaration 0:037> !do 0000000140f2f4d0 Name: System.String MethodTable: 000007fef6eeec90 EEClass: 000007fef6afb038 Size: 38(0x26) bytes (C:\Windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll) String: Texas Fields: MT Field Offset Type VT Attr Value Name 000007fef6ef5f00 4000096 8 System.Int32 1 instance 7 m_arrayLength 000007fef6ef5f00 4000097 c System.Int32 1 instance 6 m_stringLength 000007fef6ef06d8 4000098 10 System.Char 1 instance 54 m_firstChar
0:037> !do 0000000140ee4320 Name: Microsoft.ReportingServices.ReportProcessing.ParameterInfo MethodTable: 000007ff0158c790 EEClass: 000007ff015a4db8 Size: 120(0x78) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6eeec90 40005b2 8 System.String 0 instance 0000000140ee3388 m_name 000007ff0158bde8 40005b3 20 System.Int32 1 instance 18 m_dataType 000007fef6eede60 40005b4 28 System.Boolean 1 instance 0 m_nullable 000007fef6eede60 40005b5 29 System.Boolean 1 instance 1 m_promptUser 000007fef6eede60 40005b6 2a System.Boolean 1 instance 0 m_usedInQuery 000007fef6eede60 40005b7 2b System.Boolean 1 instance 0 m_allowBlank 000007fef6eede60 40005b8 2c System.Boolean 1 instance 0 m_multiValue 000007fef6ee4748 40005b9 10 System.Object[] 0 instance 0000000000000000 m_defaultValues 000007ff0158bf38 40005ba 24 System.Int32 1 instance 2 m_usedInQueryAsDefined 000007fef6ef65e8 40005bb 18 ...ections.Hashtable 0 instance 0000000000000000 m_dependencies 000007ff0158a7b8 40005bc a8 ...tence.Declaration 0 static 0000000140cd5728 m_Declaration 000007fef6ee4748 40005d9 30 System.Object[] 0 instance 0000000000000000 m_values 000007fef6ee4748 40005da 38 System.Object[] 0 instance 0000000140ee4398 m_labels 000007fef6eede60 40005db 2d System.Boolean 1 instance 1 m_isUserSupplied 000007fef6eede60 40005dc 2e System.Boolean 1 instance 1 m_dynamicValidValues 000007fef6eede60 40005dd 2f System.Boolean 1 instance 0 m_dynamicDefaultValue 000007fef6eede60 40005de 68 System.Boolean 1 instance 0 m_dynamicPrompt 000007fef6eeec90 40005df 40 System.String 0 instance 0000000140ee3708 m_prompt 000007ff01580038 40005e0 48 ...terInfoCollection 0 instance 0000000000000000 m_dependencyList 000007ff0158b838 40005e1 50 ...ng.ValidValueList 0 instance 0000000140f2be18 m_validValues 000007fef6ef5db0 40005e2 58 System.Int32[] 0 instance 0000000000000000 m_dependencyIndexList 000007fef6eede60 40005e3 69 System.Boolean 1 instance 1 m_valuesChanged 000007ff0158c628 40005e4 60 System.Int32 1 instance 1 m_state 000007fef6eede60 40005e5 6a System.Boolean 1 instance 0 m_othersDependOnMe 000007fef6eede60 40005e6 6b System.Boolean 1 instance 0 m_useExplicitDefaultValue 000007fef6ef5f00 40005e7 64 System.Int32 1 instance -1 m_indexInCollection 000007ff0158a7b8 40005e8 b0 ...tence.Declaration 0 static 0000000140cd5508 m_Declaration
0:037> !do 0000000140f2be18 Name: Microsoft.ReportingServices.ReportProcessing.ValidValueList MethodTable: 000007ff0158b838 EEClass: 000007ff015a4ab0 Size: 40(0x28) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6ee4748 400094c 8 System.Object[] 0 instance 0000000140f2eee8 _items 000007fef6ef5f00 400094d 18 System.Int32 1 instance 4 _size 000007fef6ef5f00 400094e 1c System.Int32 1 instance 4 _version
0:037> !da 0000000140f2eee8 Name: System.Object[] MethodTable: 000007fef6ee4748 EEClass: 000007fef6afb660 Size: 64(0x40) bytes Array: Rank 1, Number of elements 4, Type CLASS Element Methodtable: 000007fef6eee580 [0] 0000000140f2eeb0 <-- ALL [1] 0000000140f2f160 <-- Arizona [2] 0000000140f2f3d0 <-- New York [3] 0000000140f2f630 <-- Texas
0:037> !do 0000000140f2f630 Name: Microsoft.ReportingServices.ReportProcessing.ValidValue MethodTable: 000007ff0158db68 EEClass: 000007ff015a6380 Size: 56(0x38) bytes (C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.ProcessingCore.dll) Fields: MT Field Offset Type VT Attr Value Name 000007fef6eee580 400059c 8 System.Object 0 instance 0000000140f2f4d0 m_value 000007fef6eeec90 400059d 10 System.String 0 instance 0000000140f2f548 m_label 000007fef6eeec90 400059e 18 System.String 0 instance 0000000000000000 m_stringValue 000007fef6eeec90 400059f 20 System.String 0 instance 0000000000000000 m_cachedAutogenLabel 000007fef6eede60 40005a0 28 System.Boolean 1 instance 1 m_labelAutoGenerated 000007ff0158a7b8 40005a1 a0 ...tence.Declaration 0 static 0000000140da1dd8 m_Declaration
0:037> !do 0000000140f2f4d0 Name: System.String MethodTable: 000007fef6eeec90 EEClass: 000007fef6afb038 Size: 38(0x26) bytes (C:\Windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll) String: Texas Fields: MT Field Offset Type VT Attr Value Name 000007fef6ef5f00 4000096 8 System.Int32 1 instance 7 m_arrayLength 000007fef6ef5f00 4000097 c System.Int32 1 instance 6 m_stringLength 000007fef6ef06d8 4000098 10 System.Char 1 instance 54 m_firstChar
That proves that the value coming back from the query has a trailing space. It has a length of 6 as opposed to 5.
Adam W. Saxton | Microsoft SQL Server Escalation Services
Thanks, this is very interesting. I wouldn't agree though that the InvalidReportParameterException is very specific. I was getting the same exception when SSRS had problems with converting date from query to proper format - it was working on my machine, but not on the server. I would appreciate if there was more information available within the exception about the nature of the problem.