SQL Server Configuration Failed

[sajiworkz]

Hi ,

I am using Apache tomcat 6.0.18,Alfresco Labs 3 Stable in Windows XP Platform. The database is derby. Actually it works fine but i got the some errors when i tried to configure the same installation with SQL Server 2005. I was using version 3a with Sql Server 2005 and i have the alfesco database ready with the data.
So i decided to connect the same database to alfresco 3 stable. Here is summary of what i did in the configuration files.

1) Removed .sample extension of all the below 3 files from the folder C:\Alfresco\tomcat\shared\classes\alfresco\extension.
custom-repository.properties.sample
custom-repository-context.xml.sample
custom-hibernate-dialect.properties.sample

2) Edited the file custom-repository.properties and added the following lines
dir.root=/alfresco_prd_data/content
dir.indexes=/alfresco_prd_data/indexes
db.username=alfresco
db.password=alfresco
db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
db.url=jdbc:sqlserver://localhost:1433; DatabaseName=alfresco

also tried with jtds.jdbc driver as given below.
db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://localhost:1433/alfresco

From the database configuration page @ alfrescowiki , i read that the jtds-1.2.2.jar file should be copied to /tomcat/common/lib folder , but i dont have the folder common inside tomcat so i copied this file to /tomcat/lib folder but later i created one common/lib folder inside tomcat and copied the file ther as well.

3) Edited the file custom-hibernate-dialect.properties and added the following lines

# SQLServer dialect
hibernate.dialect=org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect

4) Started Alfresco Server and i got the following error messages.

Mar 3, 2009 2:08:19 PM org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
Mar 3, 2009 2:08:19 PM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 508 ms
Mar 3, 2009 2:08:19 PM org.apache.catalina.core.StandardService start
INFO: Starting service Catalina
Mar 3, 2009 2:08:19 PM org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/6.0.18
Mar 3, 2009 2:08:19 PM org.apache.catalina.startup.HostConfig deployWAR
INFO: Deploying web application archive alfresco.war
Mar 3, 2009 2:08:20 PM org.apache.catalina.core.StandardContext addApplicationLi
stener
INFO: The listener "org.apache.myfaces.webapp.StartupServletContextListener" is
already configured for this context. The duplicate definition has been ignored.
14:08:36,952  INFO  [config.xml.XMLConfigService$PropertyConfigurer] Loading pro
perties file from class path resource [alfresco/file-servers.properties]
14:08:47,971  INFO  [domain.schema.SchemaBootstrap] Schema managed by database d
ialect org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect.
14:08:50,171  ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
org.alfresco.error.AlfrescoRuntimeException: The schema script could not be foun
d at location classpath:alfresco/dbscripts/upgrade/2.2/${db.script.dialect}/upgr
ade-from-2.2SP1.sql
        at org.alfresco.error.AlfrescoRuntimeException.create(AlfrescoRuntimeExc
eption.java:46)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptUrl(Sche
maBootstrap.java:751)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.checkSchemaPatchScrip
ts(SchemaBootstrap.java:739)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBo
otstrap.java:641)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBoo
tstrap.java:1123)
        at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLi
fecycleBean.java:62)
        at org.springframework.context.event.SimpleApplicationEventMulticaster$1
.run(SimpleApplicationEventMulticaster.java:77)
        at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecut
or.java:49)
        at org.springframework.context.event.SimpleApplicationEventMulticaster.m
ulticastEvent(SimpleApplicationEventMulticaster.java:75)
        at org.springframework.context.support.AbstractApplicationContext.publis
hEvent(AbstractApplicationContext.java:246)
        at org.springframework.context.support.AbstractApplicationContext.refres
h(AbstractApplicationContext.java:355)
        at org.springframework.web.context.ContextLoader.createWebApplicationCon
text(ContextLoader.java:244)
        at org.springframework.web.context.ContextLoader.initWebApplicationConte
xt(ContextLoader.java:187)
        at org.springframework.web.context.ContextLoaderListener.contextInitiali
zed(ContextLoaderListener.java:49)
        at org.apache.catalina.core.StandardContext.listenerStart(StandardContex
t.java:3843)
        at org.apache.catalina.core.StandardContext.start(StandardContext.java:4
342)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase
.java:791)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:77
1)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:525)

        at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:830)

        at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:719
)
        at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:490
)
        at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1149)
        at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java
:311)
        at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(Lifecycl
eSupport.java:117)
        at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)

        at org.apache.catalina.core.StandardHost.start(StandardHost.java:719)
        at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)

        at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443
)
        at org.apache.catalina.core.StandardService.start(StandardService.java:5
16)
        at org.apache.catalina.core.StandardServer.start(StandardServer.java:710
)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:578)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)
14:08:50,187  ERROR [web.context.ContextLoader] Context initialization failed
org.alfresco.error.AlfrescoRuntimeException: Schema auto-update failed
        at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBoo
tstrap.java:1192)
        at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLi
fecycleBean.java:62)
        at org.springframework.context.event.SimpleApplicationEventMulticaster$1
.run(SimpleApplicationEventMulticaster.java:77)
        at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecut
or.java:49)
        at org.springframework.context.event.SimpleApplicationEventMulticaster.m
ulticastEvent(SimpleApplicationEventMulticaster.java:75)
        at org.springframework.context.support.AbstractApplicationContext.publis
hEvent(AbstractApplicationContext.java:246)
        at org.springframework.context.support.AbstractApplicationContext.refres
h(AbstractApplicationContext.java:355)
        at org.springframework.web.context.ContextLoader.createWebApplicationCon
text(ContextLoader.java:244)
        at org.springframework.web.context.ContextLoader.initWebApplicationConte
xt(ContextLoader.java:187)
        at org.springframework.web.context.ContextLoaderListener.contextInitiali
zed(ContextLoaderListener.java:49)
        at org.apache.catalina.core.StandardContext.listenerStart(StandardContex
t.java:3843)
        at org.apache.catalina.core.StandardContext.start(StandardContext.java:4
342)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase
.java:791)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:77
1)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:525)

        at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:830)

        at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:719
)
        at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:490
)
        at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1149)
        at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java
:311)
        at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(Lifecycl
eSupport.java:117)
        at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)

        at org.apache.catalina.core.StandardHost.start(StandardHost.java:719)
        at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)

        at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443
)
        at org.apache.catalina.core.StandardService.start(StandardService.java:5
16)
        at org.apache.catalina.core.StandardServer.start(StandardServer.java:710
)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:578)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)
Caused by: org.alfresco.error.AlfrescoRuntimeException: The schema script could
not be found at location classpath:alfresco/dbscripts/upgrade/2.2/${db.script.di
alect}/upgrade-from-2.2SP1.sql
        at org.alfresco.error.AlfrescoRuntimeException.create(AlfrescoRuntimeExc
eption.java:46)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptUrl(Sche
maBootstrap.java:751)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.checkSchemaPatchScrip
ts(SchemaBootstrap.java:739)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBo
otstrap.java:641)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBoo
tstrap.java:1123)
        … 33 more
Mar 3, 2009 2:08:50 PM org.apache.catalina.core.StandardContext start
SEVERE: Error listenerStart
Mar 3, 2009 2:08:50 PM org.apache.catalina.core.StandardContext start
SEVERE: Context [/alfresco] startup failed due to previous errors
log4j:ERROR LogMananger.repositorySelector was null likely due to error in class
reloading, using NOPLoggerRepository.
Mar 3, 2009 2:08:52 PM org.apache.catalina.startup.HostConfig deployWAR
INFO: Deploying web application archive share.war
14:08:55,618  INFO  [web.site.FrameworkHelper] Successfully Initialized Web Fram
ework
Mar 3, 2009 2:08:55 PM org.apache.catalina.startup.HostConfig deployWAR
INFO: Deploying web application archive studio.war
14:08:57,913  INFO  [web.scripts.DeclarativeRegistry] Registered 67 Web Scripts
(+0 failed), 71 URLs
14:08:57,913  INFO  [web.scripts.AbstractRuntimeContainer] Initialised Presentat
ion Web Script Container (in 280.61124ms)
14:09:58,843  INFO  [web.scripts.DeclarativeRegistry] Registered 101 Web Scripts
(+0 failed), 105 URLs
14:09:58,843  INFO  [web.scripts.AbstractRuntimeContainer] Initialised WebFramew
ork Web Script Container (in 60974.754ms)
14:09:58,905  INFO  [web.site.FrameworkHelper] Successfully Initialized Web Fram
ework
Mar 3, 2009 2:09:59 PM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
Mar 3, 2009 2:09:59 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in 99609 ms

Please help me to solve this problem.

Thanks in advance

Regards,
Saji

[mikeh]

http://forums.alfresco.com/en/viewtopic.php?f=8&t=17252&p=56742#p56742

Mike

[sajiworkz]

Hi Mike,

Thanks for your prompt reply, but it was working fine with my previously Installed version 3.0.0 (a 1032) schema 128. Could you please confirm there is no way to connect MS SQL Server with alfresco 3 Stable version.

Regards,

Saji

[mikeh]

That's correct - you'll have to use Derby or MySQL (recommended).

Thanks,
Mike

[sajiworkz]

Hi Mike,

Thanks again, Could you please tell me how can i upgrade my previously installed version (version 3.0.0 (a 1032) schema 128) into alfresco 3 Stable version. Becuase as i mentioned the earlier version 3.0.0 (a 1032)  was working fine with SQL Server Database.

Regards,

Saji

[mikeh]

Please see http://wiki.alfresco.com/wiki/General_Upgrade_Process

Note you'll also have to move your SQL Server database over to MySQL. There are probably 3rd party tools to help you with this.

Thanks,
Mike

[sajiworkz]

Hello Mike,

Thanks for your support, but we need to continue with SQL Server 2005. Could you please suggest some workaround to keep MS SQL as the backend for Alfresco 3 stable.

Regards,

Saji Krishnan

[mikeh]

You'd need to take the MySQL upgrade scripts and translate them into SQL Server-compatible versions in the correctly named folder. Have a look at some v2.x upgrade scripts for examples.

Thanks,
Mike

[holgert]

Hi Mike and Saji,

I am surprised that Alfresco 3.0 and MS SQL should not work. I installed such a configuration (Alfresco3, Stable 1526, schema 1002) recently and it worked fine.
Alfresco did all the schema creation in Microsoft SQL (after I had changed the hibernate-config in 'custom-hibernate-dialect.properties' and added "hibernate.dialect=org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect" as config) and the installation is still up and running.
We have MS SQL 2000 SP3 (internal version 8.00.760). But it should work on MS SQL2005 if you configure the database to be run in 8.0-compatibility mode.

Or did I get something wrong and only the upgrade scripts from 3.0b to stable do not work correctly?

Holger