error with FixDNNRoles732.sql

Sep 12, 2014 at 6:06 PM
I ran the script AdjustDNNHostSettings732.sql without any errors, though it did not fix the problem. I then ran the script FixDNNRoles732.sql and got the following error:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'P'.
Incorrect syntax near the keyword 'ELSE'.
Incorrect syntax near the keyword 'WHERE'.
Incorrect syntax near ','.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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 asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script)
ClientConnectionId:8d38b5c6-be32-4b35-98b4-2ac3bebdd6bb

/* Fix DNN Roles - Version 0.9.4 (2014-09-10)
================================================================================
(c) Sebastian Leupold, dnnWerk/gamma concept mbH 2014

Run this Script to fix role settings and assignment in DNN Platform V.7.3.2

== Please make sure to use latest version from http://dnnscript.codeplex.com ===

Instructions:
=============
  • Install by running as script from SQL item in Host menu inside DNN or run in
    SQL Server Management Studio, after replacing placeholders dbo. and
    by its proper values from web.config file.
  • Make sure that the currently used account is member of dbOwner database role.
License and Disclaimer:
=======================
Published under Microsoft Open Source Reciprocal License (Ms-RL). For details,
please read http://dnnscript.codeplex.com/license.
Feel free to use this script as you need, but there is no warranty or liability
for any damage or effort, eventually been caused.

Please report issues at https://dnnscript.codeplex.com/WorkItem/Create
================================================================================
*/


UPDATE dbo.[Portals] P
SET AdministratorRoleId = CASE
WHEN Exists(SELECT * FROM dbo.[Roles] R WHERE R.PortalID = P.PortalID AND R.RoleName Like 'Admin%')
  SELECT MIN(RoleID) FROM dbo.[Roles] R WHERE R.PortalID = P.PortalID AND R.RoleName Like 'Admin%'
ELSE
  SELECT MIN(RoleID) FROM dbo.[Roles] R WHERE R.PortalID = P.PortalID
END
WHERE AdministratorRoleId Is Null OR AdministratorRoleId != IsNull((SELECT RolelID FROM dbo.[Roles] R WHERE R.PortalID = P.PortalID AND R.RoleID = P.AdministratorRoleId),-1)
Coordinator
Sep 13, 2014 at 8:48 AM
Thanks for heading up, I fixed it and replaced the file.
Marked as answer by leupold on 3/24/2015 at 5:22 AM
Sep 15, 2014 at 1:50 PM
Thanks! Now the script runs to completion, but I still can't see my Site Settings under Admin.