There is already an object named '#temp_dates' in the database

[ohdsi]

There is already an object named '#temp_dates' in the database

[mark-velez]

I noticed that multiple analyses are using the same name #temp_dates for temp tables. As a quick fix, I changed the name within each analysis. It seems to me to work- does this break anything?

[pbr6cornell]

In the code, the temp tables should be getting dropped after each use,
before they are recreated. the drops are honored if you are running
through the app. however, if you are running from sql server IDE, you may
be getting the error, unless you put a 'go' statement in between the
segments. this is not a fix for the script, which is tested to work in sql
server, but may be something to test if you are running local.

On Tue, Oct 13, 2015 at 4:17 PM, Mark Velez [email protected]
wrote:

I noticed that multiple analyses are using the same name #temp_dates for
temp tables. As a quick fix, I changed the name within each analysis. It
seems to me to work- does this break anything?

—
Reply to this email directly or view it on GitHub
#62 (comment).

[fdefalco]

I would need @cahilton or @alexfranken to weigh in on if changing that name in the script would break any of the heracles analyses. It is odd that this issue is occurring since it is my understanding that the scope of the #temp tables on MSSQL is limited to the current connection. Can you provide a few additional details about your environment including the version of MSSQL?

[charhart]

I would defer to you guys (Frank and Patrick), but the file should run fine in the app, however, if you're running command line, they'll be some tweaks you'll have to make.

I wouldn't think these would break the application, but couldn't say for sure without testing.

[mvelez1984]

@pbr6cornell @cahilton This is from WebAPI, but thanks for the GO tip.

@fdefalco We use SQL Server 2012 Enterprise; JDK 1.8; jTDS JDBC driver 1.3.1.

Does this output suggest that the SQL blocks are not being split properly? Thanks OHDSI for the responsiveness.

[chrisknoll]

Yes, in that SQL you sent us, it looks like multiple statements are included in 1 sql query. But this 1 block of SQL should be split by the ';' and then executed in batch (using the Java batch execute function).

That would explain why the parser things that you are trying to create a table that already exists.

[mark-velez]

We've also been seeing the seemingly related exception below logged in BATCH_STEP_EXECUTION.EXIT_MESSAGE. This also seems to relate to this topic in the OHDSI forums.

I did a trace and found that 72 statements were passed to batchUpdate which @chrisknoll referred to. In MSSQL I monitored the queries associated with the job and found that statements were all joined into a single query. I'm not familiar enough with Spring Batch to determine if it might be pooling these into the same transaction or session nor what to do about this. I appreciate any advice.

org.springframework.transaction.TransactionSystemException: Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction marked as rollbackOnly
  at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:526)
  at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757)
  at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:483)
  at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
  at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
  at com.sun.proxy.$Proxy102.commit(Unknown Source)
  at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:150)
  at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:271)
  at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)
  at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
  at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
  at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
  at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)
  at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:198)
  at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)
  at org.springframework.ba

[chrisknoll]

I'm not sure about this later error message: exceptions in JPA means that one of the entities failed to save to the database (related to a transaction). There may be inner exceptions being raised that we aren't seeing here, but the 'table already exists' errors from the prior messages isn't the same as the 'transaction could not be committed'.

@mvelez1984: This is what I've been doing in my cohort definition code, not sure if it's different where the code is executing for you:

      String expressionSql = expressionQueryBuilder.buildExpressionQuery(expression, options);
      String translatedSql = SqlTranslate.translateSql(expressionSql, "sql server", jobParams.get("target_dialect").toString(), SessionUtils.sessionId(), null);
      String[] sqlStatements = SqlSplit.splitSql(translatedSql);
      result = GenerateCohortTasklet.this.jdbcTemplate.batchUpdate(sqlStatements);

The array of sql statements should be executed separately and if an exception is throw with SQL in it, it should just have the executing query. But that's a bit of an assumption on my side, but I haven't seen these types of errors.

-Chris

[mark-velez]

I was able to resolve the issue by using the Microsoft JDBC driver. I suppose (have not confirmed) the jTDS driver supports batch updates whereas the MS driver doesn't- and so JdbcTemplate.batchUpdate behaves differently. If this is the case, should we consider running these explicitly as separate updates?

[fdefalco]

Would the intent be to do that to support the jTDS driver?

[mark-velez]

@fdefalco The intent would be to prevent this error when using a JDBC driver that supports batch update. I'm not sure which do, but I suspect jTDS does. Support might also be added in future versions of other drivers and this error would be hard to spot (that is, if my understanding of the issue is even correct). I think I understand the benefit of using batch update when it is supported, but it would seem that flows are possible to CohortAnalysisTasklet where steps should not be batched. What do you think?