Initialization failed with MSSQL

Hi,

I have a PoC workflow works good with MySQL. The settings is like this.

spring:
  profiles: local-mysql
  datasource:
    url:  jdbc:mysql://localhost:3306/camunda
    username: xxx
    password: xxx
camunda:
  bpm:
    database:
      type: mysql

However, after I switched to Microsoft SQL, I’m no longer started the workflow. Here is the configuration related to DB.

spring:
  profiles: local-sql
  datasource:
    url: jdbc:sqlserver://<host>:1433;databaseName=<db>
    username: <user_id>
    password: <user_pswd>
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        default_schema: Fulfillment

camunda:
  datasource:
    url: jdbc:sqlserver://<host>;databaseName=<db>
    username: <another_user_id>
    password: <another_user_pswd>
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

In POM, I’ve added MSSQL as

        <!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>6.4.0.jre8</version>
        </dependency>

But, I’m getting error as

Caused by: org.camunda.bpm.engine.ProcessEngineException: Process engine persistence exception
        at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.rethrow(CommandInvocationContext.java:153) ~[camunda-engine-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:229) ~[camunda-engine-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:119) ~[camunda-engine-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:46) ~[camunda-engine-spring-7.12.0.jar:7.12.0]
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.1.9.RELEASE.jar:5.1.9.RELEASE]
        at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:44) ~[camunda-engine-spring-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70) ~[camunda-engine-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33) ~[camunda-engine-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.impl.ProcessEngineImpl.executeSchemaOperations(ProcessEngineImpl.java:121) ~[camunda-engine-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.impl.ProcessEngineImpl.<init>(ProcessEngineImpl.java:93) ~[camunda-engine-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:815) ~[camunda-engine-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.spring.SpringTransactionsProcessEngineConfiguration.buildProcessEngine(SpringTransactionsProcessEngineConfiguration.java:63) ~[camunda-engine-spring-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:55) ~[camunda-engine-spring-7.12.0.jar:7.12.0]
        at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:34) ~[camunda-engine-spring-7.12.0.jar:7.12.0]
        at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:171) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
        ... 180 common frames omitted
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'ACT_GE_PROPERTY'.
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.PropertyEntity.selectProperty-Inline
### The error occurred while setting parameters
### SQL: select * from ACT_GE_PROPERTY where NAME_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'ACT_GE_PROPERTY'.
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.4.4.jar:3.4.4]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150) ~[mybatis-3.4.4.jar:3.4.4]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.4.jar:3.4.4]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77) ~[mybatis-3.4.4.jar:3.4.4]

I’ve verified and used the <another_user_id>/<another_pswd> to login to SQL sever and I’m seeing the table ACT_GE_PROPERTY.
image .

Any suggestion is appreciated!

@oakgreen can you provide the complete exception stack Trace?

And which version of MSSQL db are you using?

Microsoft SQL Server 2014 (SP2-CU10-GDR) (KB4052725) - 12.0.5571.0 (X64) 
	Jan 10 2018 15:52:08 
	Copyright (c) Microsoft Corporation
	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Resolved. The POM I am using is of Camunda BPM 7.12.0. While the scripts DBA used to create schema was based on Camunda BPM 7.8.0. It seems DB structure changes after v 7.8.0.