Unable to view reports after Report Server April update

[mothman]

Hi all

We recently updated our report server from the March 2018 build to April (Build 15.0.2.402)

After the upgrade, we aren't able to view any of our saved PBIX files in our portal, getting the error 'unable to load folder contents'.

The upgrade seemed to work fine with no error messages.
However looking in the report server LogFiles folder, we found the attached log file which concerned us, especially the below.

ERROR|6|Aborting SQL batch sccript! Error executing SQL batch script at entry
------------
CREATE PROCEDURE [dbo].[GetAllProperties]

 ...
|FATAL|6|Database upgrade failed!!  The database may now be in an inconsistent state.| 

Could you please advise?
Thanks

2018-05-16 12:01:16.2047|INFO|1|File Logger created: C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles\RSManagement_2018_05_16_12_01_16.log - level Info, will roll at 32 Mb, process id 11668
2018-05-16 12:01:16.3133|INFO|1|CFG : Dumper.path = [..\LogFiles\] (env)
2018-05-16 12:01:16.3133|INFO|1|CFG : Name = [] (env)
2018-05-16 12:01:16.3133|INFO|1|CFG : Dumper.flags = [SendToWatson, AllThreads, AllMemory] (env)
2018-05-16 12:01:16.3133|INFO|1|CFG : Dumper.preventIfContains = [Microsoft.BIServer.HostingEnvironment.Exceptions.TrustedProcessTokenExpiredException] (env)
2018-05-16 12:01:16.3133|INFO|1|Do not dump on: Microsoft.BIServer.HostingEnvironment.Exceptions.TrustedProcessTokenExpiredException
2018-05-16 12:01:16.3133|INFO|1|CFG : listenerUrl = [http://+:8083/] (env)
2018-05-16 12:01:16.3133|INFO|1|CFG : rsConfigFilePath = [..\ReportServer\rsreportserver.config] (env)
2018-05-16 12:01:16.3290|INFO|1|SKU: Power BI Report Server - Evaluation
2018-05-16 12:01:16.4227|INFO|1|The web server needs to run elevated
2018-05-16 12:01:16.4227|INFO|1|Web Server is running.
2018-05-16 12:01:16.7661|INFO|6|Creating default Global MeterCollector| RequestID = s_bd19dfa8-cd03-45ff-a34d-e4679807b9b3 
2018-05-16 12:01:16.7661|INFO|6|CFG : DatabaseQueryTimeout = [300] (env)
2018-05-16 12:01:16.7661|INFO|6|CFG : SqlBatchTimeoutMinutes = [30] (default)
2018-05-16 12:01:16.8991|INFO|6|Database Version Detection: Trivial Upgrade: 
 Db Version = 15.0.2.378
 Code Version = 15.0.2.402
 Resulted in status UpgradeRequired| RequestID = s_bd19dfa8-cd03-45ff-a34d-e4679807b9b3 
2018-05-16 12:01:16.8991|INFO|6|Upgrading ReportServerPBITest database to version 15.0.2.402 hash 82A232C71E4B6F0383517F51F757B13005FCA16BAA125C1DE5A0A5A6952D7322| RequestID = s_bd19dfa8-cd03-45ff-a34d-e4679807b9b3 
2018-05-16 12:01:22.8855|ERROR|6|Aborting SQL batch sccript! Error executing SQL batch script at entry
------------
CREATE PROCEDURE [dbo].[GetAllProperties]
@Path nvarchar (425),
@EditSessionID varchar(32) = NULL,
@OwnerSid as varbinary(85) = NULL,
@OwnerName as nvarchar(260) = NULL,
@AuthType int
AS
BEGIN

DECLARE @OwnerID uniqueidentifier
if(@EditSessionID is not null)
BEGIN
 EXEC GetUserID @OwnerSid, @OwnerName, @AuthType, @OwnerID OUTPUT
END

select
 iif( Catalog.LinkSourceID is null, Catalog.Property, Linked.Property) as Property, 
 Catalog.Description,
 Catalog.Type,
 Catalog.ContentSize,
 Catalog.ItemID,
 C.UserName,
 C.UserName,
 Catalog.CreationDate,
 M.UserName,
 M.UserName,
 Catalog.ModifiedDate,
 Catalog.MimeType,
 Catalog.ExecutionTime,
 SecData.NtSecDescPrimary,
 Catalog.LinkSourceID,
 Catalog.Hidden,
 Catalog.ExecutionFlag,
 Catalog.SnapshotLimit,
 Catalog.Name,
 Catalog.SubType,
 Catalog.ComponentID,
 Catalog.ParentID
FROM ExtendedCatalog(@OwnerID, @Path, @EditSessionID) Catalog
 INNER JOIN Users C ON Catalog.CreatedByID = C.UserID
 INNER JOIN Users M ON Catalog.ModifiedByID = M.UserID
 LEFT OUTER JOIN SecData ON Catalog.PolicyID = SecData.PolicyID AND SecData.AuthType = @AuthType
 LEFT OUTER JOIN Catalog Linked ON Linked.ItemID = Catalog.LinkSourceID
END
------------
| RequestID = s_bd19dfa8-cd03-45ff-a34d-e4679807b9b3 System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'is'.
 vid System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
 vid System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
 vid System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
 vid System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
 vid System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
 vid System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
 vid Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection.ExecuteBatchScript(String script, TimeSpan individualCommandTimeout)
ClientConnectionId:6fe5b85f-0edd-44e9-8b26-f5dab8efbb72
Error Number:156,State:1,Class:15
2018-05-16 12:01:22.8855|FATAL|6|Database upgrade failed!! The database may now be in an inconsistent state.| RequestID = s_bd19dfa8-cd03-45ff-a34d-e4679807b9b3 System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'is'.
 vid System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
 vid System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
 vid System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
 vid System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
 vid System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
 vid System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
 vid Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection.ExecuteBatchScript(String script, TimeSpan individualCommandTimeout)
 vid Microsoft.BIServer.Management.WebApi.DatabaseUpgrade.DatabaseUpgrader.GetStatusAndUpdateIfRequired(ISqlAccess sqlAccess)
ClientConnectionId:6fe5b85f-0edd-44e9-8b26-f5dab8efbb72
Error Number:156,State:1,Class:15

[v-qiuyu-msft]

Hi @mothman,

1. Please launch power bi report server installation file and repair the report server. 

2. Please download the pbix file and open in the desktop optimized for report server, ensure it's working fine then republish to report server.

Best Regards,
Qiuyun Yu 

[bl33p]

We're getting the same error because our database is SQL Server 2008 and the iif function that's being used by the migration is only available from 2012 onwards.

select
 iif( Catalog.LinkSourceID is null, Catalog.Property, Linked.Property) as Property, 

SqlException (0x80131904): Incorrect syntax near the keyword 'is'.

 @v-qiuyu-msft Will this issue be fixed?