Products

Evoq Content Overview Content Creation Workflow Asset Management Mobile Responsive Personalization Content Analytics SEO Integrations Security Website PerformanceEvoq Engage Overview Community Management  Dashboard  Analytics  Member Profile Gamification Advocate Marketing Community Engagement  Ideas  Answers  Discussions  Groups  Wikis  Events Mobile ReadyDNN Support PackagesEvoq: CMS FeaturesEvoq OnDemandCompare DNN's CMS Products

Solutions

Content Management SystemMulti-Channel PublishingDigital MarketingOnline Community ManagementIntranet Software SolutionOur CustomersPrime

Resources

Test DrivesSchedule A DemoDocumentation CenterWebinarsWhite PapersProduct ManualsRequest PricingData SheetsCase StudiesEvoq Preferred Products Content Management Ecommerce Forms Identity Management and Authentication Site Management Social Themes

Partners

DNN Partners Partner Directory Become a DNN Partner Partner Program Overview  Hosting  Implementation  ISV  Training  Competencies

Community

Participate Ask a Question Forums Community Showcase DNN MVP  Program Overview  Lifetime MVPs  Honorary MVPs Subscribe to DNN Digest Advisory Groups  Awareness Advisory Group  Developer Advisory Group  Partner Advisory Group  Technology Advisory GroupLearn Documentation Wiki Community Blog Video Library Project History Development RoadmapDownload DNN Store Language Packs Manuals Nightly BuildsSecurity Policies Security Center

Blog

About

News Press Releases News Coverage Press KitCareersContact DNN

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsEventsEventsPossible problem with GetEventsByDate Procedure on publishing DNN DBPossible problem with GetEventsByDate Procedure on publishing DNN DB
Previous
 
Next
New Post
9/8/2008 9:59 AM
 
Ivan Torres


Joined: 6/1/2006
Posts: 40

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)

 
 
New Post
9/8/2008 11:08 AM
 
Ivan Torres


Joined: 6/1/2006
Posts: 40

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)))

 
 
New Post
9/8/2008 11:49 AM
 
Roger Selwyn


www.thedivingclub.co.uk
Joined: 9/23/2004
Posts: 3531

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

DNN MVP
Events - Get the latest version - Upgrade now!!
Feedback 6.4.2 - Now available - Give it a go!
Find us on Codeplex - DNN Events, DNN Feedback
Requirements/Bugs - Please submit them on Codeplex
 
New Post
9/8/2008 1:45 PM
 
Ivan Torres


Joined: 6/1/2006
Posts: 40

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.
 
New Post
9/8/2008 1:50 PM
 
Roger Selwyn


www.thedivingclub.co.uk
Joined: 9/23/2004
Posts: 3531

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

DNN MVP
Events - Get the latest version - Upgrade now!!
Feedback 6.4.2 - Now available - Give it a go!
Find us on Codeplex - DNN Events, DNN Feedback
Requirements/Bugs - Please submit them on Codeplex
 
 Page 1 of 2
12Next 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsEventsEventsPossible problem with GetEventsByDate Procedure on publishing DNN DBPossible problem with GetEventsByDate Procedure on publishing DNN DB


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, 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 DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out