|
|
Member
|
info-plan-soft
-
3/4/2009 6:03:02 AM
Database error
I'm trying to install the new free edition, build 4.0.3328
In the database setup - Step 3, I receive the following error:
ERROR:An error occured: [DataConnection.ExecuteQuery]: Query: CREATE PROCEDURE Proc_CMS_ACL_ClearACLs @NodeID int, @NewACLID int AS BEGIN update CMS_Tree SET NodeACLID = @NewACLID where NodeID=@NodeID delete from CMS_ACLItem where ACLID in (select CMS_ACL.ACLID from CMS_ACL left join CMS_Tree on cms_tree.nodeid = cms_acl.aclownernodeid where (cms_tree.nodeid is null) or (cms_tree.nodeid is not null and cms_tree.nodeaclid <> cms_acl.aclid)) delete from cms_acl where aclid in (select cms_acl.aclid from cms_acl left join cms_tree on cms_tree.nodeid = cms_acl.aclownernodeid where (cms_tree.nodeid is null) or (cms_tree.nodeid is not null and cms_tree.nodeaclid <> cms_acl.aclid)) END : caused exception: The multi-part identifier "cms_tree.nodeid" could not be bound. The multi-part identifier "cms_acl.aclownernodeid" could not be bound. The multi-part identifier "cms_tree.nodeid" could not be bound. The multi-part identifier "cms_tree.nodeid" could not be bound. The multi-part identifier "cms_tree.nodeaclid" could not be bound. The multi-part identifier "cms_acl.aclid" could not be bound
Can somebody help me out here?
|
|
|
|
|
|
|
Member
|
info-plan-soft
-
3/4/2009 1:48:58 PM
RE:Database error
A good question!! I'm un a hosted environment, I'll ask their helpdesk...
|
|
|
|
|
Member
|
info-plan-soft
-
3/5/2009 6:24:33 PM
RE:Database error
Well, the server did not use it, but they changed the settings and now it should.
However, it didn't solve the problem. I created a new database, tried it again, but I get exactly the same error.
Any suggestions?
|
|
|
|
Kentico Consulting
|
kentico_mirekr
-
3/10/2009 4:11:28 AM
RE:Database error
Hi,
It seems that your database is still using CS collation. In this case I would recommend you to manually change the lower letter occurrences in ~\Webinstaller\SQL\Proc_CMS_ACL_ClearACLs.sql file.
Example:
Change cms_tree.nodeid to CMS_Tree.NodeID etc.
Thank you,
Best Regards,
Miroslav Remias
|
|
|
|
|
Member
|
info-plan-soft
-
3/11/2009 3:46:38 PM
RE:Database error
Yes, it did.
I spend hours tp change many files, but then my host finally changed the setting, and the installation went OK
Thanks so far
|
|
|
|
|
Member
|
info-plan-soft
-
3/13/2009 6:35:07 AM
RE:Database error: Must declare the scalar variable
Previously, I posted that the installation went OK.
But I many pages in CMSDesk. I still receive all kinds of database errors, something like:
Must declare the scalar variable "@ID".
The name of the scalar variable can change. After many hours trying and searching the internet, I finally found an article that exactly describes this problem, and also the cause of it:
http://aleemkhan.wordpress.com/2006/08/28/why-collation-affects-t-sql-syntax/
Fortunatly, my host did have a SQL 2000 Server where the IDENTIFIER_CASE parameter was set to MIXED. When I run teh wizard again, now everything works OK.
So the documentation isn't complete. Not only does the server need case insensitive collation, also the IDENTIFIER_CASE parameter should be set to MIXED!
If it isn't set to mixed, the wizard will stop in Step 3 when trying to create PROCEDURE [Proc_Analytics_ProcessLog], with the error:
Must declare the scalar variable "@StatisticsID". Must declare the scalar variable "@StatisticsID".
This is because this variable is declared as "@statisticsID" in this file
|
|
|
|
Kentico Developer
|
kentico_martind
-
3/17/2009 8:26:17 AM
RE:Database error: Must declare the scalar variable
Hello,
Thank you for letting us know about this requirement. We haven't realized this setting is necessary as IDENTIFIER_CASE parameter was probably set to MIXED on all SQL servers where we tested Kentico CMS by default. I'm sorry for this inconvenience. We will mention this requirement in our documentation since next version.
Best Regards,
Martin Dobsicek
|
|
|
|
|
Member
|
info-plan-soft
-
3/17/2009 8:38:21 AM
RE:Database error: Must declare the scalar variable
That's good, however especially in a hosted environment it will be very hard to change this setting, since it will affect all other databases as well! A better solution would be to change all scripts to (for example) lower case, so there is no need any more for this requirement...
|
|
|
|
|
Kentico Developer
|
kentico_martind
-
3/26/2009 9:51:19 AM
RE:Database error: Must declare the scalar variable
Hello,
I agree it would help in some enviroments. We will consider changing scripts to lower case in the future.
Best Regards,
Martin Dobsicek
|
|
|
|
|
Member
|
forum-muramatsu.com
-
3/30/2009 9:41:18 PM
RE:Database error: Must declare the scalar variable
info-plan-soft wrote: Previously, I posted that the installation went OK.
But I many pages in CMSDesk. I still receive all kinds of database errors, something like:
Must declare the scalar variable "@ID".
The name of the scalar variable can change. After many hours trying and searching the internet, I finally found an article that exactly describes this problem, and also the cause of it:
http://aleemkhan.wordpress.com/2006/08/28/why-collation-affects-t-sql-syntax/
Fortunatly, my host did have a SQL 2000 Server where the IDENTIFIER_CASE parameter was set to MIXED. When I run teh wizard again, now everything works OK.
So the documentation isn't complete. Not only does the server need case insensitive collation, also the IDENTIFIER_CASE parameter should be set to MIXED!
If it isn't set to mixed, the wizard will stop in Step 3 when trying to create PROCEDURE [Proc_Analytics_ProcessLog], with the error:
Must declare the scalar variable "@StatisticsID". Must declare the scalar variable "@StatisticsID".
This is because this variable is declared as "@statisticsID" in this file
If You are db_owner or Sys Admin of database try this: Alter Database KenticoCMS
COLLATE SQL_Latin1_General_CP1_CI_AI
Or if You need another Collate use this to get a list of valide collation SELECT *
FROM fn_helpcollations()
|
|
|