Error when creating cohort tables using `executeSql()` on Microsoft SQL Server

[bdj5051]

Hi,

I'm trying to run the Strategus package with Microsoft SQL Server, but I'm getting the following error when running the CohortGenerator module:

Error executing SQL:

com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.

I found that this error occurs when createCohortTables() from the CohortGenerator package executes executeSql().

It seems that when a CREATE TABLE statement is executed, SQL Server returns a result set, which causes this error in R/JDBC.

I temporarily fixed this issue by using querySql() instead of executeSql() when executing a CREATE TABLE statement.

Is there any known workaround, or does anyone know why this happens?

Environment:

R version: 4.4.3 (2025-02-28 ucrt)
Platform: x86_64-w64-mingw32/x64
OS: Windows Server 2022 x64 (build 20348)

CohortGenerator version: 1.1.0
DatabaseConnector version: 7.1.0
Java version: 17.0.13

DBMS: Microsoft SQL Server

Thanks.

[schuemie]

Hi @bdj5051!

That is weird, it should just work, and I am unable to reproduce it:

sql <- "CREATE TABLE ohdsi.dbo.test_table (x INT);"
executeSql(connection, sql)
#  |================================================================================================================| 100%
# Executing SQL took 0.109 secs

or

cohortTableNames <- CohortGenerator::getCohortTableNames("test_cohort")
CohortGenerator::createCohortTables(
  connection = connection, 
  cohortDatabaseSchema = "ohdsi.dbo",
  cohortTableNames = cohortTableNames
)
# Creating cohort tables
# - Created table ohdsi.dbo.test_cohort
# - Created table ohdsi.dbo.test_cohort
# - Created table ohdsi.dbo.test_cohort_inclusion
# - Created table ohdsi.dbo.test_cohort_inclusion_result
# - Created table ohdsi.dbo.test_cohort_inclusion_stats
# - Created table ohdsi.dbo.test_cohort_summary_stats
# - Created table ohdsi.dbo.test_cohort_censor_stats
# - Created table ohdsi.dbo.test_cohort_subset_attrition
# - Created table ohdsi.dbo.test_cohort_checksum
# Creating cohort tables took 1.73secs

What version of the SQL Server JDBC driver are you using?

[bdj5051]

Thank you for checking.

As shown in the attached screenshot, running DatabaseConnector::executeSql() throws this error directly:
Error in .createErrorReport():
! Error executing SQL:
com.microsoft.sqlserver.jdbc.SQLServerException: 업데이트에 대한 결과 집합이 생성되었습니다. (which translates to "A result set was generated for update.") The table is created successfully despite the error.

This doesn't happen with DatabaseConnector 6.0.0 or earlier — it only started with the newer version. I also tested with both mssql-jdbc-9.2.0.jre8 and mssql-jdbc-13.4.0.jre8, and got the same behavior with both.

Also, this only happens in our internal network environment. When I run the same application against our external test environment with a sample dataset, it works fine with no errors.
Could this be related to the database version somehow?

[schuemie]

Gemini suggests there may be DDL triggers configured on your server that cause the CREATE TABLE statement to generate a result set. You should be able to list any triggers using

SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE';

(On my test server this returns 0 rows)

[bdj5051]

Thanks for checking — I ran that query and got the same result as you:

I also tried adding SET NOCOUNT ON before the CREATE TABLE statement, but it made no difference — I'm still getting the same error.

[schuemie]

Weird! Maybe you could see what the result is that the CREATE TABLE statement is generating?

sql <- "CREATE TABLE cohortdb.dbo.test_table (x INT);"
querySql(conn, sql)

(This throws an error on my SQL Server server because it returns no results)

[bdj5051]

When I run it against my SQL Server instance, querySql() doesn't throw — it just returns a message confirming the statement executed:
(which translates to "Table created successfully")