Thursday, August 4, 2016

Report Server cannot load the SQLPDW extension

While troubleshooting SSRS issues, you may run into the following error messages in logs and traces:

Report Server (MSSQLSERVER) cannot load the SQLPDW extension.

Report Server (MSSQLSERVER) cannot load the TERADATA extension.

On numerous occasions I have seen both customers and colleagues believe that these errors were the source of their problems. After which, the wild goose chase to resolve these errors ensues. I am here to tell you that it is highly unlikely that they are related to your problem at hand (especially if you are only using SSRS for CRM reporting).

These errors occur because the Teradata and SQLPDW extensions are registered in the Reporting Services configuration file by default but the assemblies for these extensions do not get installed in the same manner. Typically these extensions are used with big data and business analytics.

If the errors do not bother you, simply ignore them and track down other error messages. However, if you are the type that prefers clean logs you can take the following steps to alleviate the error messages from being logged:
  • Open the rsreportserver.config file. This can usually be found in \Program Files\Microsoft SQL Server\MSRSXX_X.MSSQLSERVER\Reporting Services\ReportServer.
  • In this config file, simply search for and comment out the entries related to SQLPDW and TERADATA. There should be 2 entries for SQLPDW and 3 for TERADATA. Below is an example:
<!--Extension Name="SQLPDW" Type="Microsoft.ReportingServices.DataExtensions.SqlDwConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/-->

<!--Extension Name="SQLPDW" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.MSSQLADW.MSSqlAdwSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
    <Configuration>
        <EnableMathOpCasting>False</EnableMathOpCasting>
    </Configuration>
</Extension-->

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/-->

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Teradata.TdSqlSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
    <Configuration>
        <EnableMathOpCasting>True</EnableMathOpCasting>
        <ReplaceFunctionName>oREPLACE</ReplaceFunctionName>
    </Configuration>
</Extension-->

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Teradata.TdSqlModelGenerator,Microsoft.ReportingServices.SemanticQueryEngine"/-->
  • Save and close the config file.
  • Restart the SSRS service.