| |
|
|
|
|
|
|
|
|
|
 |  | |
| | |
| | |
| | |
 | | |
|
|
|
| Possible problem with GetEventsByDate Procedure on publishing DNN DB |
|
|
Hello,
If I should be posting this item elsewhere, please let me know. However, I think someone who knows the Events Module may be helpful.
I'm trying to publish my local database to GoDaddy hosting using the MS Database Publishing Wizard. I have tried with databases for two different DNN installations and the process has failed on both occasions while trying to create procedure GetEventsByDate.
Objets are being creat Ok on the remote but the process fails with the message below. There are references to "invalid column names". I don't know if the CREATE PROCEDURE command validates the existence of referenced columns or tables. Also, from my limited experience wth SQL, I believe an error message could describe an error that is only a consequence of whatever the original unidentified error is.
I am not an expert in SQL but I wonder if there may be some syntax problem in the procedure. For example I hadn't seen syntax like the use of two single quotes as there are around ''IconFile'' in a SELECT clause like the procedure has.
ie:
.
.
Events.Period,
''IconFile'' = case when Files.FileName is null then Events.IconFile else Files.Folder + Files.FileName end,
.
.
.
The error messages are below. Any help is appreciated.
Thanks,
Ivan Torres
--------------------------------------------------------------------------
TITLE: SqlPubWiz
An error occured while trying to publish the following script:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEventsByDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[GetEventsByDate]
@ModuleId int,
@StartDate datetime,
@EndDate datetime
as
select
Events.ItemId,
Events.ModuleId,
Events.Description,
Events.DateTime,
Events.Title,
Events.ExpireDate,
Events.CreatedByUser,
Events.CreatedDate,
Events.Every,
Events.Period,
''IconFile'' = case when Files.FileName is null then Events.IconFile else Files.Folder + Files.FileName end,
Events.AltText
from Events
left outer join Files on Events.IconFile = ''fileid='' + convert(varchar,Files.FileID)
where ModuleId = @ModuleId
and ( (Period is null and (DateTime >= @StartDate and DateTime <= @EndDate)) or Period is not null )
order by DateTime
'
END
(SqlPubWiz)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.PublishToWebWriterProvider.PublishScript(String script)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.PublishToWebWriterProvider.PublishCachedScript()
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.PublishToWebWriterProvider.WriteLine(String script)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptWriter.Write(String script, Boolean useBatch)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptWriter.Write(String script)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptObjectContainer.AppendScriptFragment(ScriptWriter scriptWriter, IEnumerable fragment)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptObjectContainer.AppendCreateScript(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptObjectContainer.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptedObjectCollection.ScriptAll(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.DependencyScriptHandler.ScriptCore(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.SingleScriptTarget.OnScriptingFinished(Object source, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptProcessor.OnScriptingCompleted()
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptProcessor.GenerateScript()
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptingEngine.GenerateScript()
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptingDriver.GenerateScript()
at Microsoft.SqlServer.Management.SqlManagerUI.GenerateScriptMaker.DoScript()
at Microsoft.SqlServer.Management.SqlManagerUI.GenScriptWizform.Script()
===================================
Invalid column name 'IconFile'.
Invalid column name 'DateTime'.
Invalid column name 'DateTime'.
Invalid column name 'ItemId'.
Invalid column name 'Description'.
Invalid column name 'DateTime'.
Invalid column name 'Title'.
Invalid column name 'ExpireDate'.
Invalid column name 'CreatedByUser'.
Invalid column name 'IconFile'.
Invalid column name 'AltText'. (System.Web.Services)
------------------------------
Program Location:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
|
|
|
|
| |
|
|
| Re: Possible outdated procedures in DNN DB |
|
|
I looked at the Events table and the columns mentioned in the error message are definitely not there.
I looked at the Procedures and it seems that there may be obsolete Event module related procedures in the database. I'm not sure if these are procedures that should have been dropped by script during a version upgrade of DNN or the Events module.I see there is a EventsGetByDate proc that references the correct column names (attached below). There is also the GetEventsByDate which is the one causing the problem. Apparently the naming conventions have changed. There are around 11 procedures that start with GetEvent** and I presume they are obsolete and wil cause the same problem while publishing.
I could delete them and keep trying, but I may not delete all the ones I should (eg. AddEvent vs. EventAdd, etc) or I could delete more than I should. I'd rather wait to see if someone can point out the obsolete ones or an easy way to clean up. Note: I upgraded to DNN 4.08.04, Events is 03.03.07.
Thanks.
EventsGetByDate
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EventsGetByDate]
(
@EventDate datetime,
@ModuleID int
)
AS
SET DATEformAT mdy
SELECT Events.PortalID, EventID, ModuleID, EventDateBegin, EventDateEnd,
EventTimeBegin, Duration, EventName, EventDesc,
Importance, CreatedDate,
CreatedBy = Users.FirstName + ' ' + Users.LastName,
CreatorID = CreatedBy,
Every,
Period,
RepeatType,
Notify,
Approved,
Signups,
MaxEnrollment,
(Select count(*) from EventsSignups WHERE EventID = Events.EventID and Events.Signups = 1) as Enrolled,
EnrollRoleID,
EnrollFee,
EnrollType,
PayPalAccount,
PayPalPassword,
Cancelled,
ImageURL,
ImageType,
ImageWidth,
ImageHeight,
ImageDisplay,
Events.Location,
c.LocationName,
c.MapURL,
Events.Category,
b.CategoryName,
b.Color,
Reminder,
TimezoneOffset,
SendReminder,
ReminderTime,
ReminderTimeMeasurement,
ReminderFrom,
SearchSubmitted
FROM Events
left outer join Users on Events.CreatedBy = Users.UserID
left join EventsCategory b on Events.Category = b.Category
left join EventsLocation c on Events.Location = c.Location
WHERE ((@EventDate BETWEEN EventDateBegin AND EventDateEnd)
AND (ModuleID = @ModuleID)
AND (Approved = 1))
OR
((EventDateEnd >= @EventDate)
AND (ModuleID = @ModuleID)
AND (Approved = 1))
ORDER BY (SELECT (datepart(hour, EventTimeBegin) * 10000) +
(datepart(minute, EventTimeBegin) * 100) +
(datepart(second, EventTimeBegin)))
|
|
|
|
| |
|
|
| Re: Possible outdated procedures in DNN DB |
|
|
Hi Ivan,
I'm not sure I can debug this issue, since I don't use MS DB Publishing Wizard. However, the double quotes shown around iconfile are single quotes in the orginal script, so not sure what the Wizard is doing.
That said, all Events sprocs now start 'Events...'. So I don't quite understand why you should have any others in your database to upload to GoDaddy. What version of the Events module are you using? In the latest version (4.0.2) there is not EventsGetByDate sproc either, it has been removed in favour of using EventsGetByRange.
Cheers
Roger
Events 4.0.2 - It's released !! - See the annoucement.
Requirements/Bugs - Submit them here - Gemini - Raise against project 'DNNP', and put 'Events' in the title. |
|
|
|
| |
|
|
| Re: Possible outdated procedures in DNN DB |
|
|
Roger Selwyn wrote
That said, all Events sprocs now start 'Events...'. So I don't quite understand why you should have any others in your database to upload to GoDaddy. What version of the Events module are you using? In the latest version (4.0.2) there is not EventsGetByDate sproc either, it has been removed in favour of using EventsGetByRange. |
Hello, Roger.
I don't think we need to debug what the Publishing Wizard is doing, given our other findings. (questions in bold)
As I said on the last post, I gathered that the procedure names had changed from one version to another. As to why I would have the old procedure versions on my system I could say that:
1. In order for the old procedures to be in my DB at, I had to install that old version of the Events module originally (Do you know which version changed the procedure names? 03.03.08?)
2. Then after I upgraded to DNN 04.08.04, I installed Events v. 03.03.08 (or was updated automatically). At this point, if the procedure names changed, the upgrade script would need to drop the old procedures. Perhaps they can remain there unused, but for cleanliness of the code and the database they should be deleted. (Agree?)
Now, if the old procedures were not dropped either:
a) Dropping them was not contemplated in the upgrade procedure. (and the Events upgrade script should be corrected) or
b) The upgrade procedure did not run completely when I upgraded. (I couldn't find what runs in the upgrade process to check, perhaps you have ready access to the scripts and can check if the upgrade process is supposed to drop the old procs.)
So, if it's (a) you may want to updae the upgrade script to drop them. They could perhaps stay in the DB without being run, but someday someone may have a problem. (Like someone using a Publishing Wizard that checks if the refernced columns in a script exist. :-) ) Anywy, it would be cleaner to drop the unused procedures.
If it is (b), then it was a one time fluke in my case that the old procs weren't deleted and you don't need to worry about the upgrade script.
Now, either way..I think the way to solve my problem is to find out the list of procedures that are no longer used by the Events module so I can drop them. Then I expect that the Database Publishing Wizard will work fine. (Or if I can get the names of the old version procedures and the new version procedures I can compare them myself) Do you have that info or know where I can find it?
I need this DPW to work because GoDaddy doesn't allow restores of SQL database backups that were not created by their system. So, I can't FTP and restore a backup file.
Thanks for the help. |
|
|
|
| |
|
|
| Re: Possible outdated procedures in DNN DB |
|
|
Hi Ivan,
Without checking through all the old scripts..... You should be looking to use version 4.0.2 which fixes many many problems from version 3.3.8. I know there was a big clean up of sprocs in version 4.0.1, and I'm 99% sure that a correct install (either upgrade or new install) of version 4.0.2 will leave you no incorrect sprocs in the database.
I would recommend you upgrade to 4.0.2, then investigate your database, prior to migrating to GoDaddy.
Cheers
Roger
Events 4.0.2 - It's released !! - See the annoucement.
Requirements/Bugs - Submit them here - Gemini - Raise against project 'DNNP', and put 'Events' in the title. |
|
|
|
| |
| | |
| | |
| | |
|  |
| |
|
|
|
These Discussion Forums are dedicated to the discussion of the DotNetNuke Web Application Framework.
For the benefit of the community and to protect the integrity of the project, please observe the following posting guidelines:
1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DotNetNuke.
2. Discussion or promotion of DotNetNuke product releases under a different brand name are strictly prohibited.
3. No Flaming or Trolling.
4. No Profanity, Racism, or Prejudice.
5. Site Moderators have the final word on approving/removing a thread or post or comment.
6. English language posting only, please.
|
| |
|
|
|
|
|
|
|
|