SQL Queries Broken in 0.39.0.1

brennan · April 22, 2021, 5:48pm

Previously working queries against MSSQL Server broken since update to 0.39.0.1

"Select statement did not produce a ResultSet for native query"

Logs show error_type : invalid-query

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "Java(TM) SE Runtime Environment",
    "java.runtime.version": "1.8.0_201-b09",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_201",
    "java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
    "java.vm.version": "25.201-b09",
    "os.name": "Linux",
    "os.version": "4.15.0-142-generic",
    "user.language": "en",
    "user.timezone": "America/New_York"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "sqlserver"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "10.16 (Ubuntu 10.16-0ubuntu0.18.04.1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.18"
      }
    },
    "run-mode": "prod",
    "version": {
      "tag": "v0.39.0.1",
      "date": "2021-04-20",
      "branch": "release-x.39.x",
      "hash": "47bb5f2"
    },
    "settings": {
      "report-timezone": "US/Eastern"
    }
  }
}

flamber · April 22, 2021, 6:08pm

Hi @brennan
Which version did you upgrade from?
Please post the full stacktrace from Admin > Troubleshooting > Logs.
I cannot reproduce - can you provide an example of a query that doesn't work?
If you copy the query and run it in a new question, does it then work?
By the way, you should upgrade Java: https://www.metabase.com/docs/latest/operations-guide/java-versions.html

brennan · April 26, 2021, 2:39pm

Hi @flamber , sorry for the late response on this.

I had upgraded from 0.38.3 to 0.39.0.1. Creating new questions with existing SQL resulted in the same error message. Unfortunately my devops team rolled back the database and virtual machine over the weekend so I can't pull an extensive log listing.

I will upgrade Java per your recommendation however.

Regards, Brennan

mrrnlds · May 10, 2021, 10:14pm

Same issue. Upgraded from 0.37.0 to 0.39.1, using MSSQL.
Queries that worked under 0.37.0 now fail under 0.39.1 with the error message:
"Select statement did not produce a ResultSet for native query"

Update: Previous queries work as well with 0.38.5 but not with 0.39.1

I cannot post a stacktrace because I get this message:
Sorry, new users can only put 2 links in a post.

flamber · May 11, 2021, 8:15am

@mrrnlds Try again, and can you also provide the query and which type of variables you're using?

mrrnlds · May 11, 2021, 12:26pm

@flamber, thanks for you quick follow-up. It appears to be related to my use of temporary tables in direct SQL queries.

All my queries are written directly in SQL and some create temp tables to use in later statements. With the change to 0.39.1 I also started seeing some failed queries with messages like this: "There is already an object named '#adjEndDates' in the database."

Although it should not be needed, when I explicitly dropped the temp table at top of the query I received the other error ("Select statement did not produce a ResultSet for native query").

So in one case I restructured the query to eliminate use of a temp table and now it runs correctly.

mrrnlds · May 11, 2021, 12:35pm

To answer your earlier question, here is an example using temp tables and giving the error. I'll attach the stack trace next.

drop table if exists #adjEndDates;
drop table if exists #SchdWks;

SELECT 
  c.ID as ID,
  coalesce(c.EndDate, max(convert(date, notes.OutDateTime))) AS [EndDate]
INTO
  #adjEndDates
FROM 
  Client c
LEFT JOIN 
   XREF_ClientNote notes ON c.ID = notes.ClientID 
Group by 
  c.ID, c.EndDate  

SELECT 
  CAST(dateadd(
        day, 
        (1 - datepart(weekday, dateadd(day, 7, vwCampusCalendar.Date))),
        dateadd(day, 7, vwCampusCalendar.Date)
      ) AS date) AS WeekEnding
Into 
  #SchdWks
FROM 
  vwCampusCalendar
WHERE vwCampusCalendar.Date >= convert(date, '2012-01-01')
GROUP BY 
  CAST(dateadd(
        day, 
	(1 - datepart(weekday, dateadd(day, 7, vwCampusCalendar.Date))), 
    	dateadd(day, 7, vwCampusCalendar.Date)
      ) AS date)

Select 
  wks.WeekEnding as WeekEnding,
  cp.Description as Campus,
  count(*) as Total
From
  #SchdWks wks,  
  LU_Campus cp,
  Client c
Left JOIN
  #adjEndDates ad ON c.ID = ad.ID
Where c.CampusID = cp.CampusID 
  AND c.FirstName NOT LIKE '%'+'Tester'+'%'
  and ((
              c.Active = 1 
          and datediff(day, c.StartDate, wks.WeekEnding) > 0
       ) or (
              c.Active = 0 
          and datediff(day, c.StartDate, wks.WeekEnding) > 0
          and datediff(day, ad.EndDate, wks.WeekEnding) < 0
       ))
group by 
  wks.WeekEnding, cp.Description
order by 
  wks.WeekEnding, cp.Description Desc

mrrnlds · May 11, 2021, 12:39pm

And here is the stack trace:

[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:57-04:00 INFO metabase.core 
Metabase v0.39.1 (6beba48 release-x.39.x) 

Copyright © 2021 Metabase, Inc. 

Metabase Enterprise Edition extensions are NOT PRESENT.
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:57-04:00 WARN metabase.core WARNING: You have enabled namespace tracing, which could log sensitive information like db passwords.
[c3c6030f-fe86-479d-93f2-9329e89ab697] 2021-05-11T08:31:57-04:00 INFO metabase.core Starting Metabase in STANDALONE mode
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:57-04:00 INFO metabase.server Launching Embedded Jetty Webserver with config: 
 {:port 3000, :host "0.0.0.0"}

[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:57-04:00 INFO metabase.core Starting Metabase version v0.39.1 (6beba48 release-x.39.x) ...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:57-04:00 INFO metabase.core System info:
 {"file.encoding" "UTF-8",
 "java.runtime.name" "OpenJDK Runtime Environment",
 "java.runtime.version" "11.0.11+9-Ubuntu-0ubuntu2.20.04",
 "java.vendor" "Ubuntu",
 "java.vendor.url" "https://ubuntu.com/",
 "java.version" "11.0.11",
 "java.vm.name" "OpenJDK 64-Bit Server VM",
 "java.vm.version" "11.0.11+9-Ubuntu-0ubuntu2.20.04",
 "os.name" "Linux",
 "os.version" "5.4.0-1048-aws",
 "user.language" "en",
 "user.timezone" "America/New_York"}

[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:57-04:00 INFO metabase.plugins Loading plugins in /var/opt/metabase/plugins...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :presto...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.driver.impl Registered abstract driver :sql  🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.driver.impl Registered driver :presto (parents: [:sql]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :hive-like...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.driver.impl Registered abstract driver :sql-jdbc (parents: [:sql]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.driver.impl Registered abstract driver :hive-like (parents: [:sql-jdbc]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :sparksql...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.driver.impl Registered driver :sparksql (parents: [:hive-like]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :sqlite...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.driver.impl Registered driver :sqlite (parents: [:sql-jdbc]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :mongo...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.driver.impl Registered driver :mongo  🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :druid...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.driver.impl Registered driver :druid  🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.plugins.dependencies Metabase cannot initialize plugin Metabase Vertica Driver due to required dependencies. Metabase requires the Vertica JDBC driver in order to connect to Vertica databases, but we can't ship it as part of Metabase due to licensing restrictions. See https://metabase.com/docs/latest/administration-guide/databases/vertica.html for more details.

[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.plugins.dependencies Metabase Vertica Driver dependency {:class com.vertica.jdbc.Driver} satisfied? false
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 INFO metabase.plugins.dependencies Plugins with unsatisfied deps: ["Metabase Vertica Driver"]
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:58-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :sqlserver...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.driver.impl Registered driver :sqlserver (parents: [:sql-jdbc]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :snowflake...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.driver.impl Registered driver :snowflake (parents: [:sql-jdbc]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Plugin 'Metabase BigQuery Driver' depends on plugin 'Metabase Google Drivers Shared Dependencies'
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Metabase BigQuery Driver dependency {:plugin Metabase Google Drivers Shared Dependencies} satisfied? false
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Plugins with unsatisfied deps: ["Metabase Vertica Driver" "Metabase BigQuery Driver"]
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Plugin 'Metabase Google Analytics Driver' depends on plugin 'Metabase Google Drivers Shared Dependencies'
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Plugins with unsatisfied deps: ["Metabase Vertica Driver" "Metabase BigQuery Driver" "Metabase Google Analytics Driver"]
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Metabase Google Analytics Driver dependency {:plugin Metabase Google Drivers Shared Dependencies} satisfied? false
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :google...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.driver.impl Registered abstract driver :google  🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Metabase BigQuery Driver dependency {:plugin Metabase Google Drivers Shared Dependencies} satisfied? true
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Metabase Google Analytics Driver dependency {:plugin Metabase Google Drivers Shared Dependencies} satisfied? true
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 DEBUG metabase.plugins.initialize Dependencies satisfied; these plugins will now be loaded: ["Metabase Google Analytics Driver" "Metabase BigQuery Driver"]
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :googleanalytics...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.driver.impl Registered driver :googleanalytics (parents: [:google]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 WARN metabase.plugins.lazy-loaded-driver Warning: plugin manifest for :bigquery does not include connection properties
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :bigquery...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.driver.impl Registered driver :bigquery (parents: [:sql :google]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Metabase cannot initialize plugin Metabase Oracle Driver due to required dependencies. Metabase requires the Oracle JDBC driver in order to connect to Oracle databases, but we can't ship it as part of Metabase due to licensing restrictions. See https://metabase.com/docs/latest/administration-guide/databases/oracle.html for more details.

[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Metabase Oracle Driver dependency {:class oracle.jdbc.OracleDriver} satisfied? false
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.plugins.dependencies Plugins with unsatisfied deps: ["Metabase Oracle Driver" "Metabase Vertica Driver"]
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :redshift...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.driver.impl Registered driver :postgres (parents: [:sql-jdbc]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.driver.impl Registered driver :redshift (parents: [:postgres]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.driver.impl Registered driver :h2 (parents: [:sql-jdbc]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.driver.impl Registered driver :mysql (parents: [:sql-jdbc]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.core Setting up and migrating Metabase DB. Please sit tight, this may take a minute...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.db.setup Verifying postgres Database Connection ...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.db.setup Successfully verified PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) application database connection. ✅
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.db.setup Running Database Migrations...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:31:59-04:00 INFO metabase.db.setup Setting up Liquibase...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:00-04:00 INFO metabase.db.setup Liquibase is ready.
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:00-04:00 INFO metabase.db.liquibase Checking if Database has unrun migrations...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:03-04:00 INFO metabase.db.liquibase Database has unrun migrations. Waiting for migration lock to be cleared...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:03-04:00 INFO metabase.db.liquibase Migration lock is cleared. Running migrations...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:03-04:00 INFO metabase.db.setup Database Migrations Current ...  ✅
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:03-04:00 INFO metabase.db.data-migrations Running all necessary data migrations, this may take a minute.
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:03-04:00 INFO metabase.db.data-migrations Finished running data migrations.
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.events Starting events listener: :metabase.events.dependencies/Dependencies 👂
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.events Starting events listener: :metabase.events.activity-feed/ActivityFeed 👂
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.events Starting events listener: :metabase.events.metabot-lifecycle/MetaBotLifecycle 👂
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.events Starting events listener: :metabase.events.last-login/LastLogin 👂
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.events Starting events listener: :metabase.events.notifications/Notifications 👂
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.events Starting events listener: :metabase.events.driver-notifications/DriverNotifications 👂
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.events Starting events listener: :metabase.events.sync-database/Sync 👂
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.events Starting events listener: :metabase.events.revision/Revisions 👂
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.events Starting events listener: :metabase.events.view-log/ViewLog 👂
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.task Initializing task SyncDatabases 📆
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.task Initializing task CheckForNewVersions 📆
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.task Initializing task SendAnonymousUsageStats 📆
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.task Initializing task SendAbandomentEmails 📆
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.task Initializing task SendPulses 📆
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.task Initializing task SendFollowUpEmails 📆
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.task Initializing task TaskHistoryCleanup 📆
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.driver.impl Initializing driver :sql...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.driver.impl Initializing driver :sql-jdbc...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.driver.impl Initializing driver :postgres...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:04-04:00 INFO metabase.core Metabase Initialization COMPLETE
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:30-04:00 INFO metabase.util.i18n.impl Reading available locales from locales.clj...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:31-04:00 DEBUG metabase.server.middleware.log GET /api/session/properties 200 58.8 ms (2 DB calls) App DB connections: 0/4 Jetty threads: 4/50 (3 idle, 0 queued) (42 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:32-04:00 DEBUG metabase.server.middleware.log GET /api/user/current 200 558.3 ms (3 DB calls) App DB connections: 0/4 Jetty threads: 3/50 (4 idle, 0 queued) (43 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:32-04:00 DEBUG metabase.server.middleware.log GET /api/database 200 37.4 ms (4 DB calls) App DB connections: 0/4 Jetty threads: 3/50 (4 idle, 0 queued) (43 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:32-04:00 INFO metabase.driver.impl Initializing driver :h2...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:32-04:00 INFO metabase.driver.impl Initializing driver :sqlserver...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:32-04:00 INFO metabase.plugins.classloader Added URL file:/var/opt/metabase/plugins/sqlserver.metabase-driver.jar to classpath
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:32-04:00 DEBUG metabase.plugins.init-steps Loading plugin namespace metabase.driver.sqlserver...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:32-04:00 INFO metabase.driver.impl Registered driver :sqlserver (parents: [:sql-jdbc]) 🚚
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:32-04:00 DEBUG metabase.server.middleware.log GET /api/database 200 316.4 ms (4 DB calls) App DB connections: 0/4 Jetty threads: 5/50 (2 idle, 0 queued) (43 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:32-04:00 DEBUG metabase.server.middleware.log GET /api/card/57 200 534.0 ms (6 DB calls) App DB connections: 0/4 Jetty threads: 5/50 (2 idle, 0 queued) (43 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:33-04:00 DEBUG metabase.server.middleware.log GET /api/native-query-snippet 200 18.3 ms (1 DB calls) App DB connections: 0/4 Jetty threads: 6/50 (1 idle, 0 queued) (45 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:33-04:00 DEBUG metabase.server.middleware.log GET /api/alert/question/57 200 35.5 ms (1 DB calls) App DB connections: 0/4 Jetty threads: 6/50 (1 idle, 0 queued) (45 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:33-04:00 DEBUG metabase.server.middleware.log GET /api/collection/10 200 74.4 ms (3 DB calls) App DB connections: 0/4 Jetty threads: 4/50 (3 idle, 0 queued) (45 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:33-04:00 DEBUG metabase.server.middleware.log GET /api/collection 200 27.8 ms (3 DB calls) App DB connections: 0/4 Jetty threads: 4/50 (3 idle, 0 queued) (45 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:33-04:00 DEBUG metabase.plugins.jdbc-proxy Registering JDBC proxy driver for com.microsoft.sqlserver.jdbc.SQLServerDriver...
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:33-04:00 DEBUG metabase.server.middleware.log GET /api/database 200 1.5 s (9 DB calls) App DB connections: 0/4 Jetty threads: 3/50 (4 idle, 0 queued) (47 total active threads) Queries in flight: 1 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:36-04:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null
{:database_id 2,
 :started_at #t "2021-05-11T08:32:33.344002-04:00[America/New_York]",
 :via
 [{:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error "Error executing query",
   :stacktrace
   ["--> driver.sql_jdbc.execute$execute_reducible_query$fn__79429.invoke(execute.clj:470)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:467)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:453)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:462)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:453)"
    "driver.sql_jdbc$fn__80881.invokeStatic(sql_jdbc.clj:54)"
    "driver.sql_jdbc$fn__80881.invoke(sql_jdbc.clj:52)"
    "query_processor.context$executef.invokeStatic(context.clj:59)"
    "query_processor.context$executef.invoke(context.clj:48)"
    "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
    "query_processor.context.default$default_runf.invoke(default.clj:66)"
    "query_processor.context$runf.invokeStatic(context.clj:45)"
    "query_processor.context$runf.invoke(context.clj:39)"
    "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
    "query_processor.reducible$pivot.invoke(reducible.clj:31)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47242.invoke(mbql_to_native.clj:25)"
    "query_processor.middleware.check_features$check_features$fn__46484.invoke(check_features.clj:39)"
    "query_processor.middleware.limit$limit$fn__47228.invoke(limit.clj:37)"
    "query_processor.middleware.cache$maybe_return_cached_results$fn__45936.invoke(cache.clj:211)"
    "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__47488.invoke(optimize_temporal_filters.clj:204)"
    "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__49418.invoke(validate_temporal_bucketing.clj:50)"
    "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45055.invoke(auto_parse_filter_values.clj:43)"
    "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__41400.invoke(wrap_value_literals.clj:161)"
    "query_processor.middleware.annotate$add_column_info$fn__41275.invoke(annotate.clj:598)"
    "query_processor.middleware.permissions$check_query_permissions$fn__46356.invoke(permissions.clj:81)"
    "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48346.invoke(pre_alias_aggregations.clj:40)"
    "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46557.invoke(cumulative_aggregations.clj:60)"
    "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48645.invoke(resolve_joined_fields.clj:102)"
    "query_processor.middleware.resolve_joins$resolve_joins$fn__48958.invoke(resolve_joins.clj:171)"
    "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44631.invoke(add_implicit_joins.clj:190)"
    "query_processor.middleware.large_int_id$convert_id_to_string$fn__47192.invoke(large_int_id.clj:59)"
    "query_processor.middleware.format_rows$format_rows$fn__47173.invoke(format_rows.clj:74)"
    "query_processor.middleware.desugar$desugar$fn__46623.invoke(desugar.clj:21)"
    "query_processor.middleware.binning$update_binning_strategy$fn__45442.invoke(binning.clj:227)"
    "query_processor.middleware.resolve_fields$resolve_fields$fn__46159.invoke(resolve_fields.clj:34)"
    "query_processor.middleware.add_dimension_projections$add_remapping$fn__44280.invoke(add_dimension_projections.clj:312)"
    "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__44509.invoke(add_implicit_clauses.clj:147)"
    "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__49367.invoke(upgrade_field_literals.clj:40)"
    "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__44794.invoke(add_source_metadata.clj:123)"
    "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48520.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
    "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45002.invoke(auto_bucket_datetimes.clj:147)"
    "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46206.invoke(resolve_source_table.clj:45)"
    "query_processor.middleware.parameters$substitute_parameters$fn__48328.invoke(parameters.clj:111)"
    "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46258.invoke(resolve_referenced.clj:79)"
    "query_processor.middleware.expand_macros$expand_macros$fn__46879.invoke(expand_macros.clj:155)"
    "query_processor.middleware.add_timezone_info$add_timezone_info$fn__44803.invoke(add_timezone_info.clj:15)"
    "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49320.invoke(splice_params_in_response.clj:32)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48531$fn__48535.invoke(resolve_database_and_driver.clj:31)"
    "driver$do_with_driver.invokeStatic(driver.clj:60)"
    "driver$do_with_driver.invoke(driver.clj:56)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48531.invoke(resolve_database_and_driver.clj:25)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47119.invoke(fetch_source_query.clj:274)"
    "query_processor.middleware.store$initialize_store$fn__49329$fn__49330.invoke(store.clj:11)"
    "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
    "query_processor.store$do_with_store.invoke(store.clj:38)"
    "query_processor.middleware.store$initialize_store$fn__49329.invoke(store.clj:10)"
    "query_processor.middleware.validate$validate_query$fn__49374.invoke(validate.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__47255.invoke(normalize_query.clj:22)"
    "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44649.invoke(add_rows_truncated.clj:35)"
    "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49305.invoke(results_metadata.clj:147)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__46500.invoke(constraints.clj:42)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__48417.invoke(process_userland_query.clj:135)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46440.invoke(catch_exceptions.clj:173)"
    "query_processor.reducible$async_qp$qp_STAR___37978$thunk__37979.invoke(reducible.clj:103)"
    "query_processor.reducible$async_qp$qp_STAR___37978$fn__37981.invoke(reducible.clj:108)"],
   :error_type :invalid-query,
   :ex-data
   {:sql
    "-- Metabase:: userID: 2 queryType: native queryHash: acf23ed24b80a1c1a5e65feef7f72c5fcab9ada974d073b27f2f20d123387404\ndrop table if exists #adjEndDates;\ndrop table if exists #SchdWks;\n\nSELECT \n  c.ID as ID,\n  coalesce(c.EndDate, max(convert(date, notes.OutDateTime))) AS [EndDate]\nINTO\n  #adjEndDates\nFROM \n  Client c\nLEFT JOIN \n   XREF_ClientNote notes ON c.ID = notes.ClientID \nGroup by \n  c.ID, c.EndDate  \n\nSELECT \n  CAST(dateadd(\n        day, \n        (1 - datepart(weekday, dateadd(day, 7, vwCampusCalendar.Date))),\n        dateadd(day, 7, vwCampusCalendar.Date)\n      ) AS date) AS WeekEnding\nInto \n  #SchdWks\nFROM \n  vwCampusCalendar\nWHERE vwCampusCalendar.Date >= convert(date, '2012-01-01')\nGROUP BY \n  CAST(dateadd(\n        day, \n\t(1 - datepart(weekday, dateadd(day, 7, vwCampusCalendar.Date))), \n    \tdateadd(day, 7, vwCampusCalendar.Date)\n      ) AS date)\n\nSelect \n  wks.WeekEnding as WeekEnding,\n  cp.Description as Campus,\n  count(*) as Total\nFrom\n  #SchdWks wks,  \n  LU_Campus cp,\n  Client c\nLeft JOIN\n  #adjEndDates ad ON c.ID = ad.ID\nWhere c.CampusID = cp.CampusID \n  AND c.FirstName NOT LIKE '%'+'Tester'+'%'\n  and ((\n              c.Active = 1 \n          and datediff(day, c.StartDate, wks.WeekEnding) > 0\n       ) or (\n              c.Active = 0 \n          and datediff(day, c.StartDate, wks.WeekEnding) > 0\n          and datediff(day, ad.EndDate, wks.WeekEnding) < 0\n       ))\ngroup by \n  wks.WeekEnding, cp.Description\norder by \n  wks.WeekEnding, cp.Description Desc",
    :params nil,
    :type :invalid-query}}],
 :error_type :invalid-query,
 :json_query
 {:constraints {:max-results 10000, :max-results-bare-rows 2000},
  :type :native,
  :middleware {:js-int-to-string? true},
  :native
  {:query
   "drop table if exists #adjEndDates;\ndrop table if exists #SchdWks;\n\nSELECT \n  c.ID as ID,\n  coalesce(c.EndDate, max(convert(date, notes.OutDateTime))) AS [EndDate]\nINTO\n  #adjEndDates\nFROM \n  Client c\nLEFT JOIN \n   XREF_ClientNote notes ON c.ID = notes.ClientID \nGroup by \n  c.ID, c.EndDate  \n\nSELECT \n  CAST(dateadd(\n        day, \n        (1 - datepart(weekday, dateadd(day, 7, vwCampusCalendar.Date))),\n        dateadd(day, 7, vwCampusCalendar.Date)\n      ) AS date) AS WeekEnding\nInto \n  #SchdWks\nFROM \n  vwCampusCalendar\nWHERE vwCampusCalendar.Date >= convert(date, '2012-01-01')\nGROUP BY \n  CAST(dateadd(\n        day, \n\t(1 - datepart(weekday, dateadd(day, 7, vwCampusCalendar.Date))), \n    \tdateadd(day, 7, vwCampusCalendar.Date)\n      ) AS date)\n\nSelect \n  wks.WeekEnding as WeekEnding,\n  cp.Description as Campus,\n  count(*) as Total\nFrom\n  #SchdWks wks,  \n  LU_Campus cp,\n  Client c\nLeft JOIN\n  #adjEndDates ad ON c.ID = ad.ID\nWhere c.CampusID = cp.CampusID \n  AND c.FirstName NOT LIKE '%'+'Tester'+'%'\n  and ((\n              c.Active = 1 \n          and datediff(day, c.StartDate, wks.WeekEnding) > 0\n       ) or (\n              c.Active = 0 \n          and datediff(day, c.StartDate, wks.WeekEnding) > 0\n          and datediff(day, ad.EndDate, wks.WeekEnding) < 0\n       ))\ngroup by \n  wks.WeekEnding, cp.Description\norder by \n  wks.WeekEnding, cp.Description Desc\n",
   :template-tags {}},
  :database 2,
  :parameters [],
  :async? true,
  :cache-ttl nil},
 :status :failed,
 :class clojure.lang.ExceptionInfo,
 :stacktrace
 ["--> driver.sql_jdbc.execute$fn__79349.invokeStatic(execute.clj:336)"
  "driver.sql_jdbc.execute$fn__79349.invoke(execute.clj:332)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:342)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:339)"
  "driver.sql_jdbc.execute$execute_reducible_query$fn__79429.invoke(execute.clj:468)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:467)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:453)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:462)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:453)"
  "driver.sql_jdbc$fn__80881.invokeStatic(sql_jdbc.clj:54)"
  "driver.sql_jdbc$fn__80881.invoke(sql_jdbc.clj:52)"
  "query_processor.context$executef.invokeStatic(context.clj:59)"
  "query_processor.context$executef.invoke(context.clj:48)"
  "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
  "query_processor.context.default$default_runf.invoke(default.clj:66)"
  "query_processor.context$runf.invokeStatic(context.clj:45)"
  "query_processor.context$runf.invoke(context.clj:39)"
  "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
  "query_processor.reducible$pivot.invoke(reducible.clj:31)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47242.invoke(mbql_to_native.clj:25)"
  "query_processor.middleware.check_features$check_features$fn__46484.invoke(check_features.clj:39)"
  "query_processor.middleware.limit$limit$fn__47228.invoke(limit.clj:37)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__45936.invoke(cache.clj:211)"
  "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__47488.invoke(optimize_temporal_filters.clj:204)"
  "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__49418.invoke(validate_temporal_bucketing.clj:50)"
  "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45055.invoke(auto_parse_filter_values.clj:43)"
  "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__41400.invoke(wrap_value_literals.clj:161)"
  "query_processor.middleware.annotate$add_column_info$fn__41275.invoke(annotate.clj:598)"
  "query_processor.middleware.permissions$check_query_permissions$fn__46356.invoke(permissions.clj:81)"
  "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48346.invoke(pre_alias_aggregations.clj:40)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46557.invoke(cumulative_aggregations.clj:60)"
  "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48645.invoke(resolve_joined_fields.clj:102)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__48958.invoke(resolve_joins.clj:171)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44631.invoke(add_implicit_joins.clj:190)"
  "query_processor.middleware.large_int_id$convert_id_to_string$fn__47192.invoke(large_int_id.clj:59)"
  "query_processor.middleware.format_rows$format_rows$fn__47173.invoke(format_rows.clj:74)"
  "query_processor.middleware.desugar$desugar$fn__46623.invoke(desugar.clj:21)"
  "query_processor.middleware.binning$update_binning_strategy$fn__45442.invoke(binning.clj:227)"
  "query_processor.middleware.resolve_fields$resolve_fields$fn__46159.invoke(resolve_fields.clj:34)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__44280.invoke(add_dimension_projections.clj:312)"
  "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__44509.invoke(add_implicit_clauses.clj:147)"
  "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__49367.invoke(upgrade_field_literals.clj:40)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__44794.invoke(add_source_metadata.clj:123)"
  "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48520.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
  "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45003.invoke(auto_bucket_datetimes.clj:147)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46206.invoke(resolve_source_table.clj:45)"
  "query_processor.middleware.parameters$substitute_parameters$fn__48328.invoke(parameters.clj:111)"
  "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46258.invoke(resolve_referenced.clj:79)"
  "query_processor.middleware.expand_macros$expand_macros$fn__46879.invoke(expand_macros.clj:155)"
  "query_processor.middleware.add_timezone_info$add_timezone_info$fn__44803.invoke(add_timezone_info.clj:15)"
  "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49320.invoke(splice_params_in_response.clj:32)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48531$fn__48535.invoke(resolve_database_and_driver.clj:31)"
  "driver$do_with_driver.invokeStatic(driver.clj:60)"
  "driver$do_with_driver.invoke(driver.clj:56)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48531.invoke(resolve_database_and_driver.clj:25)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47119.invoke(fetch_source_query.clj:274)"
  "query_processor.middleware.store$initialize_store$fn__49329$fn__49330.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
  "query_processor.store$do_with_store.invoke(store.clj:38)"
  "query_processor.middleware.store$initialize_store$fn__49329.invoke(store.clj:10)"
  "query_processor.middleware.validate$validate_query$fn__49374.invoke(validate.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__47255.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44649.invoke(add_rows_truncated.clj:35)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49305.invoke(results_metadata.clj:147)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__46500.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__48417.invoke(process_userland_query.clj:135)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46440.invoke(catch_exceptions.clj:173)"
  "query_processor.reducible$async_qp$qp_STAR___37978$thunk__37979.invoke(reducible.clj:103)"
  "query_processor.reducible$async_qp$qp_STAR___37978$fn__37981.invoke(reducible.clj:108)"],
 :context :question,
 :error "Select statement did not produce a ResultSet for native query",
 :row_count 0,
 :running_time 0,
 :ex-data
 {:sql
  "-- Metabase:: userID: 2 queryType: native queryHash: acf23ed24b80a1c1a5e65feef7f72c5fcab9ada974d073b27f2f20d123387404\ndrop table if exists #adjEndDates;\ndrop table if exists #SchdWks;\n\nSELECT \n  c.ID as ID,\n  coalesce(c.EndDate, max(convert(date, notes.OutDateTime))) AS [EndDate]\nINTO\n  #adjEndDates\nFROM \n  Client c\nLEFT JOIN \n   XREF_ClientNote notes ON c.ID = notes.ClientID \nGroup by \n  c.ID, c.EndDate  \n\nSELECT \n  CAST(dateadd(\n        day, \n        (1 - datepart(weekday, dateadd(day, 7, vwCampusCalendar.Date))),\n        dateadd(day, 7, vwCampusCalendar.Date)\n      ) AS date) AS WeekEnding\nInto \n  #SchdWks\nFROM \n  vwCampusCalendar\nWHERE vwCampusCalendar.Date >= convert(date, '2012-01-01')\nGROUP BY \n  CAST(dateadd(\n        day, \n\t(1 - datepart(weekday, dateadd(day, 7, vwCampusCalendar.Date))), \n    \tdateadd(day, 7, vwCampusCalendar.Date)\n      ) AS date)\n\nSelect \n  wks.WeekEnding as WeekEnding,\n  cp.Description as Campus,\n  count(*) as Total\nFrom\n  #SchdWks wks,  \n  LU_Campus cp,\n  Client c\nLeft JOIN\n  #adjEndDates ad ON c.ID = ad.ID\nWhere c.CampusID = cp.CampusID \n  AND c.FirstName NOT LIKE '%'+'Tester'+'%'\n  and ((\n              c.Active = 1 \n          and datediff(day, c.StartDate, wks.WeekEnding) > 0\n       ) or (\n              c.Active = 0 \n          and datediff(day, c.StartDate, wks.WeekEnding) > 0\n          and datediff(day, ad.EndDate, wks.WeekEnding) < 0\n       ))\ngroup by \n  wks.WeekEnding, cp.Description\norder by \n  wks.WeekEnding, cp.Description Desc",
  :driver :sqlserver},
 :data {:rows [], :cols []}}

[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:36-04:00 DEBUG metabase.server.middleware.log POST /api/card/57/query 202 [ASYNC: completed] 2.8 s (6 DB calls) App DB connections: 0/4 Jetty threads: 2/50 (5 idle, 0 queued) (53 total active threads) Queries in flight: 1 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:49-04:00 DEBUG metabase.server.middleware.log GET /api/setting 200 7.3 ms (0 DB calls) App DB connections: 1/4 Jetty threads: 5/50 (2 idle, 0 queued) (53 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:49-04:00 DEBUG metabase.server.middleware.log GET /api/session/properties 200 40.2 ms (2 DB calls) App DB connections: 1/4 Jetty threads: 4/50 (3 idle, 0 queued) (53 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:49-04:00 DEBUG metabase.server.middleware.log GET /api/setup/admin_checklist 200 56.4 ms (11 DB calls) App DB connections: 0/4 Jetty threads: 3/50 (4 idle, 0 queued) (53 total active threads) Queries in flight: 0 (0 queued)
[c3c6030f-fe86-479d-93f2-9329e89ab696] 2021-05-11T08:32:50-04:00 DEBUG metabase.server.middleware.log GET /api/util/bug_report_details 200 22.4 ms (1 DB calls) App DB connections: 0/4 Jetty threads: 3/50 (4 idle, 0 queued) (53 total active threads) Queries in flight: 0 (0 queued)

flamber · May 11, 2021, 1:01pm

@mrrnlds Okay, Metabase has never officially supported multi-statements.
https://github.com/metabase/metabase/issues/4050 - upvote by clicking on the first post
But try adding it to a Stored Procedure and just use exec [myschema].[myprocedure] in Metabase.

Previously the error results from the first statements must have been ignored, but the newer driver, which was upgraded in 0.39.0 seems to have broken multi-statement for MSSQL.
https://github.com/metabase/metabase/pull/15287

flamber · May 21, 2021, 11:30am

There's now an issue tracking this:
https://github.com/metabase/metabase/issues/16182 - upvote by clicking on the first post

ChuckD_843 · July 26, 2021, 4:23pm

Hey Flamber is this fixed yet? I changed my query from creating Temp tables then dropping them, to just declaring Table variables so no "drop table" commands needed, and I'm still getting the same error as in this thread. I can send the full query too if you want. It just declares a Temp Table (previous year data), then another Temp table (current year data), then just runs a simple select against them. It's working perfectly fine in SSMS.

flamber · July 26, 2021, 4:42pm

@ChuckD_843 No, and it never will be. The issue is listed as Won't fix.

ChuckD_843 · July 26, 2021, 4:54pm

@flamber - Ok, well thanks for the quick response. I saw the Github ticket and I wish he would reconsider because he makes a point on not being able to drop tables, sure, but not allowing multiple sub queries, that should work as that is how a lot of SQL reports are made. I guess I will try CTE or I will probably try to just grab the old Jar file.
Chuck

Sire · October 22, 2021, 12:51pm

@flamber Are you aware of work-around for this scenario, which we used a lot (SQL Server):

declare @Table as table ( Column INT ) 
INSERT INTO @Table exec [storedProcedure] 
SELECT * FROM @Table

This counts as a multi-statement, even if I wrap it inside another stored procedure. It results in a single select query, but still produces a multi-statement error. I also use "set nocount" if this matters.

This worked in earlier versions of Metabase, but not any longer for security concerns, which is fine. But perhaps there is a secure work-around possible?

flamber · October 22, 2021, 12:53pm

@Sire Let's not have duplicates of all conversions. I think I have answered every which way I know.

Sire · October 25, 2021, 7:42am

Sorry. For others looking for a solution for MS SQL Server, use "SET NOCOUNT ON" as first line in the stored procedure solved it for us.
(I had it on line 2 which didn't work).

Workaround:

SET NOCOUNT ON
declare @Table as table ( Column INT ) 
INSERT INTO @Table exec [storedProcedure] 
SELECT * FROM @Table

nico8 · March 1, 2022, 3:14pm

Yes ! It works !