DB upgrade fails after installing 2019.2.14.0

Hello,

Could you confirm which type of datasource are you using?
SQL Server? MariaDB? MySQL?

Best regards,

It's SQL Server.

Thanks!
Anders

Hello,

We are currently investigating. I'll get back to you soon.

Best regards,

Thanks very much!

Hello,

Could you please send us your database schema.


To do so, use File -> Data Sources -> Upgrade tab -> Email Schema to Support.


Best regards,

Hi!

Just sent it!

Anders

Hi,

I'm still not sure what is going on. I will send you a script (via private message). Please execute it against your database using SQL Sever Management Studio (SSMS).

There are three sections to the script, the first will output some more schema information and the next two will try to execute the upgrade step that is failing. Send me the result of the first (it will be in XML format) and let me know if the next two produced any errors or if the succeeded (Messages tab in SSMS).

Best regards,

Hi,

today, we updated to the version of RD Manager and now we have the same problem. Our data source is located on a MS SQL 2016 server and when I try to update the db, I get the same error:



System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@Xml".
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 Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteNonQuery(String sql, IDbTransaction dbTransaction, IDbDataParameter[] parameters, Int32 commandTimeout)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteScript(String script, IDbTransaction dbTransaction)
at Devolutions.RemoteDesktopManager.Business.DatabaseUpgradeAction.Execute(IDatabaseUpdater updater, BaseUpdater scripts)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseUpdater.Upgrade(Boolean silent, Action`1 logMessage)
ClientConnectionId:dbab34e8-c2ae-4eed-9de7-ab4f9c673c24
Error Number:137,State:2,Class:15


Best regards,
Volker Ringel
HMS Technology Center Ravensburg GmbH

Same issue for me, have you a fix for it ?

We can't work ...

Here is the fix/work around.

Execute the following SQL script and then let RDM finish the upgrade.

Only perform this script if you have the exact same error message and your database version is currently at 569:

Must declare the scalar variable "@Xml"

Cause of the issue? Case sensitive server setting, thank you Anders for debugging this with us.

If you require an more assistance please let us know.

Best regards,


(edited)
use the script attached below: https://forum.devolutions.net/messages.aspx?TopicID=32761&MessageID=134165#post134162

It doesn't work... :(


Mens. 156, Nivel 15, Estado 1, Línea 1
Incorrect syntax near the keyword 'OR'.
Mens. 111, Nivel 15, Estado 1, Línea 1
'ALTER FUNCTION' must be the first statement in a query batch.
Mens. 137, Nivel 15, Estado 2, Línea 12
Must declare the scalar variable "@FolderID".
Mens. 137, Nivel 15, Estado 2, Línea 19
Must declare the scalar variable "@Permission".
Mens. 178, Nivel 15, Estado 1, Línea 22
A RETURN statement with a return value cannot be used in this context.
Mens. 178, Nivel 15, Estado 1, Línea 25
A RETURN statement with a return value cannot be used in this context.
Mens. 137, Nivel 15, Estado 2, Línea 29
Must declare the scalar variable "@Permission".
Mens. 137, Nivel 15, Estado 2, Línea 30
Must declare the scalar variable "@UsersAndRoles".
Mens. 137, Nivel 15, Estado 2, Línea 35
Must declare the scalar variable "@Permission".
Mens. 178, Nivel 15, Estado 1, Línea 38
A RETURN statement with a return value cannot be used in this context.
Mens. 178, Nivel 15, Estado 1, Línea 41
A RETURN statement with a return value cannot be used in this context.
Mens. 137, Nivel 15, Estado 2, Línea 45
Must declare the scalar variable "@Permission".
Mens. 137, Nivel 15, Estado 2, Línea 46
Must declare the scalar variable "@UsersAndRoles".
Mens. 137, Nivel 15, Estado 2, Línea 51
Must declare the scalar variable "@Permission".
Mens. 178, Nivel 15, Estado 1, Línea 54
A RETURN statement with a return value cannot be used in this context.
Mens. 178, Nivel 15, Estado 1, Línea 57
A RETURN statement with a return value cannot be used in this context.
Mens. 137, Nivel 15, Estado 2, Línea 61
Must declare the scalar variable "@Permission".
Mens. 137, Nivel 15, Estado 2, Línea 62
Must declare the scalar variable "@UsersAndRoles".
Mens. 178, Nivel 15, Estado 1, Línea 66
A RETURN statement with a return value cannot be used in this context.
Mens. 102, Nivel 15, Estado 1, Línea 70
Incorrect syntax near 'OR'.
Mens. 111, Nivel 15, Estado 1, Línea 70
'ALTER FUNCTION' must be the first statement in a query batch.
Mens. 137, Nivel 15, Estado 2, Línea 82
Must declare the scalar variable "@Permission".
Mens. 137, Nivel 15, Estado 2, Línea 84
Must declare the scalar variable "@FolderID".
Mens. 137, Nivel 15, Estado 1, Línea 85
Must declare the scalar variable "@FolderID".
Mens. 137, Nivel 15, Estado 2, Línea 86
Must declare the scalar variable "@FolderID".
Mens. 137, Nivel 15, Estado 2, Línea 89
Must declare the scalar variable "@Xml".
Mens. 137, Nivel 15, Estado 1, Línea 91
Must declare the scalar variable "@Xml".
Mens. 137, Nivel 15, Estado 2, Línea 95
Must declare the scalar variable "@Xml".
Mens. 137, Nivel 15, Estado 2, Línea 97
Must declare the scalar variable "@PermissionValue".
Mens. 178, Nivel 15, Estado 1, Línea 98
A RETURN statement with a return value cannot be used in this context.
Mens. 137, Nivel 15, Estado 2, Línea 100
Must declare the scalar variable "@PermissionValue".
Mens. 178, Nivel 15, Estado 1, Línea 101
A RETURN statement with a return value cannot be used in this context.
Mens. 137, Nivel 15, Estado 2, Línea 103
Must declare the scalar variable "@PermissionValue".
Mens. 137, Nivel 15, Estado 2, Línea 106
Must declare the scalar variable "@Xml".
Mens. 137, Nivel 15, Estado 2, Línea 107
Must declare the scalar variable "@UsersAndRoles".
Mens. 137, Nivel 15, Estado 2, Línea 110
Must declare the scalar variable "@PermissionValue".
Mens. 137, Nivel 15, Estado 2, Línea 112
Must declare the scalar variable "@FolderID".
Mens. 137, Nivel 15, Estado 1, Línea 113
Must declare the scalar variable "@FolderID".
Mens. 137, Nivel 15, Estado 2, Línea 115
Must declare the scalar variable "@FolderID".
Mens. 137, Nivel 15, Estado 2, Línea 118
Must declare the scalar variable "@PermissionValue".
Mens. 137, Nivel 15, Estado 2, Línea 120
Must declare the scalar variable "@FolderID".
Mens. 137, Nivel 15, Estado 1, Línea 121
Must declare the scalar variable "@FolderID".
Mens. 137, Nivel 15, Estado 2, Línea 123
Must declare the scalar variable "@FolderID".
Mens. 178, Nivel 15, Estado 1, Línea 126
A RETURN statement with a return value cannot be used in this context.

Try this version

(edited)
use the script attached below: https://forum.devolutions.net/messages.aspx?TopicID=32761&MessageID=134165#post134162

Mensaje 2714, nivel 16, estado 3, procedimiento PAMHasFolderPermission, línea 66 [línea de inicio de lote 0]
There is already an object named 'PAMHasFolderPermission' in the database.
Mensaje 137, nivel 15, estado 1, procedimiento PAMHasCredentialPermission, línea 27 [línea de inicio de lote 68]
Must declare the scalar variable "@PermissionValue".
Mensaje 137, nivel 15, estado 2, procedimiento PAMHasCredentialPermission, línea 29 [línea de inicio de lote 68]
Must declare the scalar variable "@PermissionValue".
Mensaje 137, nivel 15, estado 2, procedimiento PAMHasCredentialPermission, línea 32 [línea de inicio de lote 68]
Must declare the scalar variable "@PermissionValue".
Mensaje 137, nivel 15, estado 2, procedimiento PAMHasCredentialPermission, línea 35 [línea de inicio de lote 68]
Must declare the scalar variable "@PermissionValue".
Mensaje 137, nivel 15, estado 2, procedimiento PAMHasCredentialPermission, línea 42 [línea de inicio de lote 68]
Must declare the scalar variable "@PermissionValue".
Mensaje 137, nivel 15, estado 2, procedimiento PAMHasCredentialPermission, línea 50 [línea de inicio de lote 68]
Must declare the scalar variable "@PermissionValue".

I also had this error.

The two SQL scripts have an error on line 94:

should be:

Please also note the last line says "<569" while I'm upgrading from =569 to 575 database version.

I executed the statements on a SQL Server Management Studio that was running directly on the server, then I connected with RDM as 'sa' user and had again the same error about @Xml variable.

I'm really having a bad day with this script. Sorry everyone this is the "good" version.

Thank you @mgraziani

Best regards,

please note this last script still has the typo in line "DECLARE @Permi@PermissionValuessionValue".

I've re-uploaded the newest version and all should be good now.

Thank you again@mgraziani

Hello again,

The "v3" version of the script fixed it for me. I was able to run the script without any errors. It upgraded the db to version 571. Then I was able to upgrade the DB to version 575 in RD Manager 2019.2.14.0 without errors.

Thanks for the fix.

Best regards,

Volker Ringel
HMS Technology Center Ravensburg GmbH

Working!

Thanks.