Wednesday, November 20, 2019

AX 2012 R2 - SSRS - The formatter thew an exception while trying to deserialize the message

This strange error popped up with one of my users the other day while running a report that use on a regular basis:

The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://tempuri.org/:queryBuilderArgs. The InnerException message was 'Element 'http://tempuri.org/:queryBuilderArgs' contains data from a type that maps to the name 'http://schemas.datacontract.org/2004/07/XppClasses:SrsReportProviderQueryBuilderArgs'. The deserializer has no knowledge of any type that maps to this name. Consider using a DataContractResolver if you are using DataContractSerializer or add the type corresponding to 'SrsReportProviderQueryBuilderArgs' to the list of known types - for example, by using the KnownTypeAttribute attribute or by adding it to the list of known types passed to the serializer.'.  Please see InnerException for more details.

A quick google search revealed most people restarting the AOS / SSRS servers to get it to clear out.
Luckily I found this post here.  This solved my problem. 

Tuesday, November 12, 2019

SQl Server SSMS - Get letters from beginning of string

I had a requirement, in SSMS, where I needed to get the first letters of a Voucher string. It could be anywhere from 3-6 letters so I couldn't hard code a start and end point.

I ended up coming up with this:

SUBSTRING([MyCol], 1, PATINDEX('%[^a-z]%', [MyCol]) - 1)

I was able to put a start of '1' as my Voucher letters ALWAYS come at the beginning.  The 'PATINDEX' finds the column AFTER my letters end.  So, I subtract 1 and I've found the end!