I have a Rollbase private cloud installation version 5.2.5 and I am using SQL Azure for Database.
I want to update server to version 5.3.0 and while executing the update_5.3.0.sql for the database it throws an error.
I followed the link in the release notes page for known issues to resolve these errors but the instructions are only for Open Edge or MySQL databases.
Thank you in advance
Can you try to edit the statement and cast the NTEXT field as a NVARCHAR :
CAST (nTextColumn AS NVARCHAR(MAX))
So that would result in something like this (untested, so please make sure you've backed up everything :):
INSERT INTO RB_TRANSLATION (CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME,TEXT_VALUE) SELECT DISTINCT CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME,CAST(TEXT_VALUE AS NVARCHAR(MAX)) FROM RB_TRANSLATION_BACKUP GROUP BY CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME;
"it throws an error"
What error?
[Err] 23000 - [SQL Server]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.RB_TRANSLATION' and the index name 'PK__RB_TRANS__210751BFA7300EC4'. The duplicate key value is (9011, el, 22918, name)
Please check the following KnowledgeBase article, which explains why these errors occur and how you can solve them:
I followed the instructions and in step 6 where we Insert back only distinct records when I run the query I get the following error in SQl Server Management Studio:
Msg 8120, Level 16, State 1, Line 2
Column 'RB_TRANSLATION_BACKUP.TEXT_VALUE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.
I am using SQL Azure as database for the Rollbase server.
Can you try to edit the statement and cast the NTEXT field as a NVARCHAR :
CAST (nTextColumn AS NVARCHAR(MAX))
So that would result in something like this (untested, so please make sure you've backed up everything :):
INSERT INTO RB_TRANSLATION (CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME,TEXT_VALUE) SELECT DISTINCT CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME,CAST(TEXT_VALUE AS NVARCHAR(MAX)) FROM RB_TRANSLATION_BACKUP GROUP BY CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME;
That statement did the trick:
INSERT INTO RB_TRANSLATION (CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME,TEXT_VALUE)
SELECT DISTINCT CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME,CAST(TEXT_VALUE AS NVARCHAR(MAX))
FROM RB_TRANSLATION_BACKUP
GROUP BY CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME,CAST(TEXT_VALUE AS NVARCHAR(MAX))
Thank you very much Ruben.