hello, as the subject says i have an error trying to update my dnn, the line says something like
00:00:01.921 - Executing Script: 06.01.05.SqlDataProvider Error! (see 06.01.05.log.resources for more information)
i look at db and the FK_dnn602_AnonymousUsers_dnn602_Portals exist and the table dnn602_AnonymousUsers
is empty.
it is a dnn installation with two portals, and the content of 06.01.05.log.resources is..
System.Data.SqlClient.SqlException (0x80131904): 'FK_dnn60_AnonymousUsers_dnn60_Portals' no es una restricción. (there isn´t a restriction) No se puede quitar la restricción. Consulte los errores anteriores. (the restriction can´t be deleted , look for previus errors.)
Table Name: dnn60_AnonymousUsers Constraint:FK_dnn60_AnonymousUsers_dnn60_Portals
Table Name: dnn60_AnonymousUsers Constraint:FK_dnn60_AnonymousUsers_dnn60_Portals
at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection. (SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)
/************************************************************/
/***** SqlDataProvider *****/
/***** *****/
/***** *****/
/***** Note: To manually execute this script you must *****/
/***** perform a search and replace operation *****/
/***** for array. and dnn60_ *****/
/***** *****/
/************************************************************/
/************************************************************/
/***** SqlDataProvider *****/
/************************************************************/
/** DNN-13280 - Clustered Primary Key for Portals **/
DECLARE @TableName nvarchar(128), @ConstraintName nvarchar(128), @UpdateRule nvarchar(128), @DeleteRule nvarchar(128)
CREATE TABLE #constraints
(
TableName nvarchar(128),
ConstraintName nvarchar(128),
UpdateRule nvarchar(128),
DeleteRule nvarchar(128)
)
INSERT INTO #constraints
SELECT
K_Table = FK.TABLE_NAME,
Constraint_Name = C.CONSTRAINT_NAME,
C.UPDATE_RULE,
C.DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
WHERE PK.TABLE_NAME IN ('dnn60_Portals')
DECLARE @SQLString nvarchar(500);
DECLARE remove_constraint_tables_cursor CURSOR FOR
SELECT TableName, ConstraintName, UpdateRule, DeleteRule from #constraints
OPEN remove_constraint_tables_cursor
FETCH NEXT FROM remove_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Table Name: ' + @TableName + ' Constraint:' + @ConstraintName
SET @SQLString = N'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName
EXEC sp_executesql @SQLString
FETCH NEXT FROM remove_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
END
CLOSE remove_constraint_tables_cursor
DEALLOCATE remove_constraint_tables_cursor
ALTER TABLE array.dnn60_Portals DROP CONSTRAINT PK_dnn60_Portals
ALTER TABLE array.dnn60_Portals ADD CONSTRAINT
PK_dnn60_Portals PRIMARY KEY CLUSTERED
(
PortalID
)
DECLARE add_constraint_tables_cursor CURSOR FOR
SELECT TableName, ConstraintName, UpdateRule, DeleteRule from #constraints
OPEN add_constraint_tables_cursor
FETCH NEXT FROM add_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = N'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + @ConstraintName
+ ' FOREIGN KEY (PortalID) REFERENCES array.dnn60_Portals (PortalID) ON UPDATE ' + @UpdateRule + ' ON DELETE ' + @DeleteRule
PRINT @SQLString
EXEC sp_executesql @SQLString
FETCH NEXT FROM add_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
END
CLOSE add_constraint_tables_cursor
DEALLOCATE add_constraint_tables_cursor
System.Data.SqlClient.SqlException (0x80131904): 'FK_dnn60_UserRoles_dnn60_Roles' no es una restricción.
No se puede quitar la restricción. Consulte los errores anteriores.
Table Name: dnn60_UserRoles Constraint:FK_dnn60_UserRoles_dnn60_Roles
Table Name: dnn60_UserRoles Constraint:FK_dnn60_UserRoles_dnn60_Roles
at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection. (SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)
/** DNN-13278 - Clustered Primary Key for Roles **/
DECLARE @TableName nvarchar(128), @ConstraintName nvarchar(128), @UpdateRule nvarchar(128), @DeleteRule nvarchar(128)
CREATE TABLE #constraints
(
TableName nvarchar(128),
ConstraintName nvarchar(128),
UpdateRule nvarchar(128),
DeleteRule nvarchar(128)
)
INSERT INTO #constraints
SELECT
K_Table = FK.TABLE_NAME,
Constraint_Name = C.CONSTRAINT_NAME,
C.UPDATE_RULE,
C.DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
WHERE PK.TABLE_NAME IN ('dnn60_Roles')
DECLARE @SQLString nvarchar(500);
DECLARE remove_constraint_tables_cursor CURSOR FOR
SELECT TableName, ConstraintName, UpdateRule, DeleteRule from #constraints
OPEN remove_constraint_tables_cursor
FETCH NEXT FROM remove_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Table Name: ' + @TableName + ' Constraint:' + @ConstraintName
SET @SQLString = N'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName
EXEC sp_executesql @SQLString
FETCH NEXT FROM remove_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
END
CLOSE remove_constraint_tables_cursor
DEALLOCATE remove_constraint_tables_cursor
ALTER TABLE array.dnn60_Roles DROP CONSTRAINT PK_dnn60_Roles
ALTER TABLE array.dnn60_Roles ADD CONSTRAINT
PK_dnn60_Roles PRIMARY KEY CLUSTERED
(
RoleID
)
DECLARE add_constraint_tables_cursor CURSOR FOR
SELECT TableName, ConstraintName, UpdateRule, DeleteRule from #constraints
OPEN add_constraint_tables_cursor
FETCH NEXT FROM add_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = N'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + @ConstraintName
+ ' FOREIGN KEY (RoleID) REFERENCES array.dnn60_Roles (RoleID) ON UPDATE ' + @UpdateRule + ' ON DELETE ' + @DeleteRule
PRINT @SQLString
EXEC sp_executesql @SQLString
FETCH NEXT FROM add_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
END
CLOSE add_constraint_tables_cursor
DEALLOCATE add_constraint_tables_cursor
********* end of log file
i search for others with this errors but not found related to FK_dnn60_AnonymousUsers_dnn60_Portals
so if someone can show me the way it would be great.
thanks.