September 2, 2021 at 8:07 pm
So I have done everything according to the article (illustrates going from 2012 to 2017)
https://www.mssqltips.com/sqlservertip/6323/move-sql-reporting-services-to-another-server/
Seems fine until I try to navigate to the reports which I receive this
The service is not available.
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
In the management RS log file I get this error, any thoughts?
2021-09-02 15:16:07.0759|INFO|7|Database Version Detection: Create DB
Db Version = NULL
Code Version = Sku: SsrsEnterprise, BuildNumber: 15.0.1102.911, DbSchemaHash: 745B9EAC6F7E2F5161F9A19F9836BB98FEE44D599D4E14E2083CF3308FA95C0F
Resulted in status UpgradeRequired| RequestID = s_e8899ad5-f332-482e-8150-9ec3b37e602b
2021-09-02 15:16:07.0759|INFO|7|Upgrading ReportServer database to version 15.0.1102.911 hash 745B9EAC6F7E2F5161F9A19F9836BB98FEE44D599D4E14E2083CF3308FA95C0F| RequestID = s_e8899ad5-f332-482e-8150-9ec3b37e602b
2021-09-02 15:16:07.1696|ERROR|7|Aborting SQL batch sccript! Error executing SQL batch script at entry
------------
CREATE FUNCTION [dbo].[ExtendedCatalog]
(@OwnerID as uniqueidentifier,
@Path as nvarchar(425),
@EditSessionID as varchar(32))
RETURNS TABLE
AS RETURN
(
SELECT TOP 1 * FROM (
SELECT
C.[ItemID],
C.[PolicyID],
C.[Path],
C.[Name],
C.[Description],
C.[Property],
C.[Type],
C.[ExecutionFlag],
C.[Parameter],
C.[Intermediate],
CONVERT(BIT, 1) AS IntermediateIsPermanent,
C.[SnapshotDataID],
C.[LinkSourceID],
C.[ExecutionTime],
C.[SnapshotLimit],
C.[CreatedByID],
C.[ModifiedByID],
C.[CreationDate],
C.[ModifiedDate],
C.[MimeType],
C.[Content],
C.[ContentSize],
C.[Hidden],
NULL AS [EditSessionID],
C.[SubType],
C.[ComponentID],
C.[ParentID]
FROM [Catalog] C
WHERE C.Path = @Path AND @EditSessionID IS NULL
UNION ALL
SELECT
TC.[TempCatalogID],
NULL as [PolicyID],
TC.[ContextPath],
TC.[Name],
TC.[Description],
TC.[Property],
2 as [Type],
1 as [ExecutionFlag],
TC.[Parameter],
TC.[Intermediate],
TC.[IntermediateIsPermanent],
NULL as [SnapshotDataID],
NULL as [LinkSourceID],
NULL as [ExecutionTime],
0 as [SnapshotLimit],
TC.[OwnerID] as [CreatedByID],
TC.[OwnerID] as [ModifiedByID],
TC.[CreationTime] as [CreationDate],
TC.[CreationTime] as [ModifiedDate],
NULL as [MimeType],
TC.Content,
DATALENGTH(TC.[Content]) AS [ContentSize],
convert(bit, 0) as [Hidden],
TC.[EditSessionID] AS [EditSessionID],
NULL as [SubType],
NULL as [ComponentID],
NULL as [ParentID]
FROM [ReportServerTempDB].dbo.TempCatalog TC
WHERE TC.OwnerID = @OwnerID AND
TC.ContextPath = @Path AND
TC.EditSessionID = @EditSessionID
) A )
------------
| RequestID = s_e8899ad5-f332-482e-8150-9ec3b37e602b System.Data.SqlClient.SqlException (0x80131904): Cannot convert to text/ntext or collate to '?×?.???........???.???.???.???...' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.
Cannot convert to text/ntext or collate to '?×?.???........???.???.???.???...' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.
Cannot convert to text/ntext or collate to '?×?.???........???.???.???.???...' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.
Cannot convert to text/ntext or collate to '?×?.???........???.???.???.???...' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection.ExecuteBatchScript(String script, TimeSpan individualCommandTimeout)
ClientConnectionId:70696abc-dbad-45b3-9f8b-5edc73c47628
Error Number:4189,State:0,Class:16
2021-09-02 15:16:07.1853|FATAL|7|Database upgrade failed!! The database may now be in an inconsistent state.| RequestID = s_e8899ad5-f332-482e-8150-9ec3b37e602b System.Data.SqlClient.SqlException (0x80131904): Cannot convert to text/ntext or collate to '?×?.???........???.???.???.???...' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.
Cannot convert to text/ntext or collate to '?×?.???........???.???.???.???...' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.
Cannot convert to text/ntext or collate to '?×?.???........???.???.???.???...' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.
Cannot convert to text/ntext or collate to '?×?.???........???.???.???.???...' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection.ExecuteBatchScript(String script, TimeSpan individualCommandTimeout)
at Microsoft.BIServer.Management.WebApi.DatabaseUpgrade.DatabaseUpgrader.GetStatusAndUpdateIfRequired(ISqlAccess sqlAccess)
ClientConnectionId:70696abc-dbad-45b3-9f8b-5edc73c47628
Error Number:4189,State:0,Class:16
September 3, 2021 at 7:46 am
The article seems correct.
Is this a native SSRS 2012 reporting to native 2019?
(reason: Sharepoint integrated is mentioned in https://stackoverflow.com/questions/44277492/ssrs-reporting-service-gives-an-error-the-found-version-is-163-the-expected-v )
Have you tested SSRS 2019 before restoring Reportserver-database + restore key? So that a fresh SSRS works fine before importing reports etc with the restore. Not sure why ReportServerTempdb needs to be restored.
September 3, 2021 at 9:11 am
This was removed by the editor as SPAM
September 3, 2021 at 1:34 pm
It is native. SSRS 2019 was fine before I moved the reportserver databases over.
September 3, 2021 at 2:06 pm
Also, since what its trying to do is add objects to upgrade the report server database, I tried to create the function in ssms and get the same error....
CREATE FUNCTION [dbo].[ExtendedCatalog]..........etc...
error is
Cannot convert to text/ntext or collate to '?×?.???........???.???.???.???...' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.
So this looks like the issue but not sure what i am missing as i didnt change any collation or anything.
September 3, 2021 at 2:59 pm
Specifically, the error is from the union where it is trying to convert the catalog.propertyfield which is ntext on the original SSRS 2012 db to the one in ReportServerTempDB tempcatalog.property which is nvarchar(max).
If you run the unioned query on 2012 it works fine....
September 6, 2021 at 4:04 am
This was removed by the editor as SPAM
September 6, 2021 at 8:52 am
We didn't hit that error. Catalog property is still ntext, Tempcatalog property is nvarchar(max). Collation Latin1_General_100_CI_AS_KS_WS. Instance collation Latin1_General_CI_AS.
September 6, 2021 at 11:31 am
This was removed by the editor as SPAM
September 7, 2021 at 8:43 am
Wat are the instance/database collactions of the 2012 reporting server? Reinstall the 2019 server using the 2012 collations?
April 26, 2022 at 6:27 am
jcourtjrSQL, did You reach any conclusions / solution to this issue?. I have the exact same issue when trying to upgrade.
May 3, 2022 at 5:40 am
After some trial and error - the cause of action, that solved the issue, was to migrate the SQL/SSRS 2012, to SQL/SSRS 2017, and from there onto the SQL/SSRS2019 installation.
May 3, 2022 at 8:34 am
So the step to 2017 was required?
May 3, 2022 at 8:52 am
Using SQL2017 as a "stepping stone", did indeed solve the specific issue with the function ExtendedCatalog.
The ReportServer databases are running the Latin1_General_CI_AS_KS_WS collation and the SQL instance is running Danish_Norwegian_CI_AS, sofar - with no issues.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply