Hi Roger,
I am running DNN 4.9.0, have SQL Server 2000 and upgraded from Events Module 4.0.2 to 5.0.1 and I ran into the situation described in this forum post. I'm interested in that script that you wrote. My existing events are no longer visible but they are still in the database.
When I upgraded my production DNN versions (2 of them), I failed to check the log for any errors and even failed to back up the database and DNN installations. This was my mistake--not feeling well today. I was able to reproduce the problem in my developer machine and confirmed the error message is there:
StartJob Start Sql execution: 04.01.00.SqlDataProvider file
Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: The text, ntext, or image data type cannot be selected as DISTINCT. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 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) /* Add dummy values to EventsRecurMaster table */ INSERT dbo.EventsRecurMaster ( ModuleID, PortalID, RRULE, DTSTART, Duration, Until, EventName, EventDesc, Importance, Notify, Approved, Signups, MaxEnrollment, EnrollRoleID, EnrollFee, EnrollType, PayPalAccount, ImageURL, ImageWidth, ImageHeight, ImageDisplay, Location, Category, Reminder, TimezoneOffset, SendReminder, ReminderTime, ReminderTimeMeasurement, ReminderFrom, CustomField1, CustomField2, EnrollListView, DisplayEndDate, AllDayEvent, CultureName, OwnerID, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate ) Select Distinct ModuleID, Portalid, 'Dummy', GetUTCDate(), '0M', GetUTCDate(), '', '', 2, '', 0, 0, 0, '', 0, '', '', '', 0, 0, 0, null, null, '', 0, 0, 0, '', '', '', '', 0, 0, 0, '', 0, 0, GetUTCDate(), 0, GetUTCDate() from dbo.Events System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'RecurMasterID', table 'DotNetNuke.dbo.Events'; column does not allow nulls. UPDATE fails. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 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) /* Populate RecurMasterID field in Events table with dummy value */ UPDATE dbo.Events SET RecurMasterID = dbo.EventsRecurMaster.RecurMasterID FROM dbo.Events LEFT OUTER JOIN dbo.EventsRecurMaster ON dbo.Events.ModuleID = dbo.EventsRecurMaster.ModuleID WHERE Events.RecurMasterID is null /* Make RecurMasterID Mandatory */ ALTER TABLE dbo.Events ALTER COLUMN RecurMasterID INT NOT NULL
EndJob End Sql execution: 04.01.00.SqlDataProvider file