Find Reporting Services (SSRS) Reports using Dynamics CRM (DB)

Recently we migrated a CRM 4.0 implementation to Dynamics CRM Online. As part of the migration, we revamped all scripts, integration and reports connecting to CRM.

Once the system was live, it was discovered that the DBA has created some reports that were hosted in an SSRS server that was not connected to CRM. There was no CRM local or shared data-source.

DBA decided to use the data from CRM within joins of some of his reports. So he added CRM as a linked DB to his SQL server and then developed queries within the reports that uses data from CRM as well as from his SQL DBs. These reports were not available in any of the lower environments or in source code repository. The only way to identify the reports consuming data from CRM, as per him, was to export reports from SSRS and open their definitions one-by-one, which was cumbersome, for around 60 reports hosted on the SSRS server.

Fortunately, with the help of some forums, I was able to come up with the following query to list all SQL queries within all reports reading data from CRM DB. This query will list reports and the SQL statements within these reports consuming data from CRM DB:

Declare @Namespace NVARCHAR(500)

Declare @SQL   VARCHAR(max)

SELECT @Namespace= SUBSTRING(

x.CatContent

,x.CIndex

,CHARINDEX(‘”‘,x.CatContent,x.CIndex+7) – x.CIndex

)

FROM

(

SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))

,CIndex    = CHARINDEX(‘xmlns=”‘,CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))

FROM Reportserver.dbo.Catalog C

WHERE C.Content is not null

AND C.Type  = 2

) X

 

SELECT @Namespace = REPLACE(@Namespace,’xmlns=”‘,”) + ” ;

 

SELECT @SQL = ‘WITH XMLNAMESPACES ( DEFAULT ”’ + @Namespace + ”’, ”http://schemas.microsoft.com/SQLServer/reporting/reportdesigner” AS rd )

SELECT  ReportName        = name

,DataSetName        = x.value(”(@Name)[1]”, ”VARCHAR(250)”)

,DataSourceName    = x.value(”(Query/DataSourceName)[1]”,”VARCHAR(250)”)

,CommandText        = x.value(”(Query/CommandText)[1]”,”VARCHAR(500)”)

,Fields            = df.value(”(@Name)[1]”,”VARCHAR(250)”)

,DataField        = df.value(”(DataField)[1]”,”VARCHAR(250)”)

,DataType        = df.value(”(rd:TypeName)[1]”,”VARCHAR(250)”)

FROM (  SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML

FROM  ReportServer.dbo.Catalog C

WHERE  C.Content is not null

AND  C.Type = 2

) a

CROSS APPLY reportXML.nodes(”/Report/DataSets/DataSet”) r ( x )

CROSS APPLY x.nodes(”Fields/Field”) f(df)

Where x.value(”(Query/CommandText)[1]”,”VARCHAR(500)”) LIKE ”%_MSCRM%”

ORDER BY name ‘

EXEC(@SQL)

Dynamics CRM Online – Azure Service Bus – Bad Gateway Error

After a gap of 4 years, I have finally convinced myself to start blogging again. There is so much to blog. I will start with a simple problem that I faced last year while integrating Dynamics CRM Online with on-premises microservices.

If you are wondering, how a cloud based Dynamics CRM can send message to on-premises API, then have a look at this MSDN article.

Once the Azure service bus was configured, service bus listener was implemented and functional, custom workflow activity was registered within CRM Online, and service endpoint was configured, data started flowing to the back-end services. Great! At this time, our developer got stuck with a “50200: Bad Gateway” error within the custom workflow activity which was supposed to send data to on-premises service via Azure Service Bus.

When I started troubleshooting, I noticed that the data flow is fine for all except Account entity. Account entity, in our case, has a number of additional attributes. This entity was the largest in our case. All other custom and out-of-the-box entities were fine.

While googling I came to this article, which proposed to check system proxy that makes sense. But in my case, there was no system proxy on dev box, so it was not the proxy issue. I felt it has something to do with the message size as account entity has around 180 attributes with most of them filled.

Interestingly this guess proved to be correct despite a confusing and misleading error message. It turned out that the message size for the account entity from the CRM was bigger than the default WCF message size. Increasing the received message size for the Service Bus listener by adding maxReceivedMessageSize in the config file of WCF with a higher value than the default, fixed the issue:

<bindings>
  <ws2007HttpRelayBinding>
    <binding name="default" maxReceivedMessageSize="2147483647">
    </binding>
  </ws2007HttpRelayBinding>
</bindings>

Hoping this will save time for the others and will be helpful.