Error syncing fields for all tables in SQL Server unless using case-sensitive exact database name

[matt-snider]

Describe the bug

Metabase is able to connect to the SQL Server instance and query the list of tables, but it is not able to obtain any information about the columns.

As such, when building a question, no columns are available and it is not possible to use this feature. SQL Queries however, do work properly.

Each table in the database results in an error: The database name component of the object qualifier must be the name of the current database.

No columns are synced into Metabase.

Logs

This is an example log for one table. This occurs for every table in the database:

Click to expand logs

2022-09-20 08:05:16,778 WARN sync.util :: Error syncing Fields for Table 'Table 1,234 'dbo.My Organization$Some Table''
com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of the current database.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1676)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7730)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3786)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:456)
	at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(SQLServerDatabaseMetaData.java:665)
	at com.mchange.v2.c3p0.impl.NewProxyDatabaseMetaData.getColumns(NewProxyDatabaseMetaData.java:3068)
	at metabase.driver.sql_jdbc.sync.describe_table$jdbc_fields_metadata$fn__60058.invoke(describe_table.clj:89)
	at metabase.driver.sql_jdbc.sync.common$reducible_results$reify__59813.reduce(common.clj:34)
	at clojure.core$transduce.invokeStatic(core.clj:6946)
	at clojure.core.Eduction.reduce(core.clj:7750)
	at clojure.core$reduce.invokeStatic(core.clj:6885)
	at clojure.core$cat$fn__8851.invoke(core.clj:7693)
	at clojure.lang.PersistentVector.reduce(PersistentVector.java:343)
	at clojure.core$reduce.invokeStatic(core.clj:6885)
	at clojure.core$reduce.invoke(core.clj:6868)
	at metabase.driver.sql_jdbc.sync.describe_table$fields_metadata$reify__60074.reduce(describe_table.clj:131)
	at clojure.core$transduce.invokeStatic(core.clj:6946)
	at clojure.core$into.invokeStatic(core.clj:6962)
	at clojure.core$into.invoke(core.clj:6950)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table_fields.invokeStatic(describe_table.clj:139)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table_fields.doInvoke(describe_table.clj:136)
	at clojure.lang.RestFn.invoke(RestFn.java:445)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table_STAR_.invokeStatic(describe_table.clj:173)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table_STAR_.invoke(describe_table.clj:170)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table.invokeStatic(describe_table.clj:184)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table.invoke(describe_table.clj:177)
	at metabase.driver.sql_jdbc$fn__85240.invokeStatic(sql_jdbc.clj:66)
	at metabase.driver.sql_jdbc$fn__85240.invoke(sql_jdbc.clj:64)
	at clojure.lang.MultiFn.invoke(MultiFn.java:239)
	at metabase.sync.fetch_metadata$fn__66090$table_metadata__66095$fn__66096.invoke(fetch_metadata.clj:19)
	at metabase.sync.fetch_metadata$fn__66090$table_metadata__66095.invoke(fetch_metadata.clj:16)
	at metabase.sync.sync_metadata.fields.fetch_metadata$fn__66358$db_metadata__66363$fn__66364.invoke(fetch_metadata.clj:88)
	at metabase.sync.sync_metadata.fields.fetch_metadata$fn__66358$db_metadata__66363.invoke(fetch_metadata.clj:84)
	at metabase.sync.sync_metadata.fields$fn__66856$sync_fields_for_table_BANG___66865$fn__66868$fn__66869.invoke(fields.clj:72)
	at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:158)
	at metabase.sync.util$do_with_error_handling.invoke(util.clj:151)
	at metabase.sync.sync_metadata.fields$fn__66856$sync_fields_for_table_BANG___66865$fn__66868.invoke(fields.clj:71)
	at metabase.sync.sync_metadata.fields$fn__66856$sync_fields_for_table_BANG___66865.invoke(fields.clj:65)
	at clojure.core$partial$fn__5908.invoke(core.clj:2641)
	at clojure.core$map$fn__5935.invoke(core.clj:2770)
	at clojure.lang.LazySeq.sval(LazySeq.java:42)
	at clojure.lang.LazySeq.seq(LazySeq.java:51)
	at clojure.lang.RT.seq(RT.java:535)
	at clojure.core$seq__5467.invokeStatic(core.clj:139)
	at clojure.core$filter$fn__5962.invoke(core.clj:2826)
	at clojure.lang.LazySeq.sval(LazySeq.java:42)
	at clojure.lang.LazySeq.seq(LazySeq.java:51)
	at clojure.lang.Cons.next(Cons.java:39)
	at clojure.lang.RT.boundedLength(RT.java:1790)
	at clojure.lang.RestFn.applyTo(RestFn.java:130)
	at clojure.core$apply.invokeStatic(core.clj:669)
	at clojure.core$apply.invoke(core.clj:662)
	at metabase.sync.sync_metadata.fields$fn__66894$sync_fields_BANG___66899$fn__66900.invoke(fields.clj:85)
	at metabase.sync.sync_metadata.fields$fn__66894$sync_fields_BANG___66899.invoke(fields.clj:77)
	at clojure.lang.AFn.applyToHelper(AFn.java:154)
	at clojure.lang.AFn.applyTo(AFn.java:144)
	at clojure.core$apply.invokeStatic(core.clj:669)
	at clojure.core$apply.invoke(core.clj:662)
	at metabase.sync.util$fn__42311$run_step_with_metadata__42316$fn__42320$fn__42322.doInvoke(util.clj:388)
	at clojure.lang.RestFn.invoke(RestFn.java:397)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:100)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:94)
	at metabase.sync.util$with_start_and_finish_debug_logging.invokeStatic(util.clj:117)
	at metabase.sync.util$with_start_and_finish_debug_logging.invoke(util.clj:114)
	at metabase.sync.util$fn__42311$run_step_with_metadata__42316$fn__42320.invoke(util.clj:383)
	at metabase.sync.util$fn__42311$run_step_with_metadata__42316.invoke(util.clj:378)
	at metabase.sync.util$fn__42530$run_sync_operation__42535$fn__42536$fn__42544.invoke(util.clj:493)
	at metabase.sync.util$fn__42530$run_sync_operation__42535$fn__42536.invoke(util.clj:491)
	at metabase.sync.util$fn__42530$run_sync_operation__42535.invoke(util.clj:485)
	at metabase.sync.sync_metadata$fn__68461$sync_db_metadata_BANG___68466$fn__68467$fn__68468.invoke(sync_metadata.clj:50)
	at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:158)
	at metabase.sync.util$do_with_error_handling.invoke(util.clj:151)
	at clojure.core$partial$fn__5910.invoke(core.clj:2647)
	at metabase.driver$fn__33070.invokeStatic(driver.clj:599)
	at metabase.driver$fn__33070.invoke(driver.clj:599)
	at clojure.lang.MultiFn.invoke(MultiFn.java:239)
	at metabase.sync.util$sync_in_context$fn__42220.invoke(util.clj:136)
	at metabase.sync.util$with_db_logging_disabled$fn__42217.invoke(util.clj:127)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:100)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:94)
	at metabase.sync.util$with_start_and_finish_logging$fn__42206.invoke(util.clj:112)
	at metabase.sync.util$with_sync_events$fn__42201.invoke(util.clj:86)
	at metabase.sync.util$with_duplicate_ops_prevented$fn__42192.invoke(util.clj:65)
	at metabase.sync.util$do_sync_operation.invokeStatic(util.clj:179)
	at metabase.sync.util$do_sync_operation.invoke(util.clj:176)
	at metabase.sync.sync_metadata$fn__68461$sync_db_metadata_BANG___68466$fn__68467.invoke(sync_metadata.clj:49)
	at metabase.sync.sync_metadata$fn__68461$sync_db_metadata_BANG___68466.invoke(sync_metadata.clj:46)
	at metabase.api.database$fn__76578$fn__76579.invoke(database.clj:832)
	at clojure.core$binding_conveyor_fn$fn__5823.invoke(core.clj:2047)
	at clojure.lang.AFn.call(AFn.java:18)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)

To Reproduce

Steps to reproduce the behavior:

1. Use SQL Server 2016
2. Set up database connection settings
3. Click "Sync database schema now"

Expected behavior

I expect that the database tables and columns sync properly.

Screenshots

In the Data Model section, the list of columns is complete, however every table has an empty schema.

Information about your Metabase Installation:

You can get this information by going to Admin -> Troubleshooting.

• Your browser and the version: N/A
• Your operating system: N/A
• Your databases: SQL Server 2016
• Metabase version: 0.44.3
• Metabase hosting environment: Docker
• Metabase internal database: MySQL 8.0.23

Severity

This restricts our usage of Metabase to SQL Queries because without any schema information, Metabase knows about the tables but not the columns, therefore using Questions does not work.

Additional context

I tried to read through the driver code in metabase/driver/sql_jdbc/sync/describe_table and followed it down into the MSSQL JDBC code where it seems like sp_columns_100 is being called.

I tried to reproduce this error by executing the same stored procedure from within a Metabase SQL Query. Oddly, it seems to work if I change the database name to uppercase.

// Error: The database name component of the object qualifier must be the name of the current database.
exec sp_columns_100 'My Organization$Some Table','dbo','mydb'
// Works!
exec sp_columns_100 'My Organization$Some Table','dbo','MYDB'

So at this point I'm not sure if this is an issue in Metabase, MS SQL JDBC, or even something specific to MS SQL Server 2016.

There is also a Microsoft JDBC Driver issue that might be relevant about this error being returned when a stored procedure is called from a different database.

The tables I am syncing are named like My Organization$Some Table in the database MYDB and schema dbo.

This is the sample code that the reporter of that issue posted:

Click to sample code

// Sample code by  @arpanpatel11 from https://github.com/microsoft/mssql-jdbc/issues/1882
public class SqlServerJDBCTest {
    public static void main(String[] args) {

        String connectionUrl = "jdbc:sqlserver://dbserver;databaseName=DB1;user=user1;password=pwd";
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        try {
            //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection(connectionUrl);

            /** To invoke procedure without parameters */
            //callableStatement = con.prepareCall("{call DB2.dbo.Getdetails}");

            /** To invoke procedure with parameters */
            callableStatement = con.prepareCall("{call DB2.dbo.DetailsWithArgs1(?,?)}");
            callableStatement.setString("Name","def");
            callableStatement.setString("Add","456");

            callableStatement.execute();
            rs = callableStatement.getResultSet();
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }

            // Iterate through the data in the result set and display it.
            while (rs.next()) {
                System.out.println(rs.getString("name") + " " + rs.getString("email_sent"));
            }

        }
        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                rs.close();
                callableStatement.close();
            }
            catch (Exception e){

            }
        }
    }
}

[matt-snider]

This was a very good example of rubber-ducking. As I wrote up the question I realized the problem might be the case sensitivity of the configured database name.

It seems that some functionality like connecting and using SQL Queries is not case sensitive, so the following worked fine in the database settings:

However, it looks like sp_columns_100 is case sensitive and it was failing when called with mydb instead of MYDB.

I changed the configuration and it worked!

I'm leaving this here as help for anyone else who runs into the issue.

This can probably be closed, but perhaps there is something that should be done in the Metabase or MSSQL JDBC code to improve this? Even changing the label to Database name (case sensitive) might help prevent this issue

[matt-snider]

Oh by the way I should also mention that before upgrading from 0.43.3 to v0.44.3, we weren't getting such a useful error message. It was just something like:

Error syncing Fields for Table 'Table 1,234 'dbo.My Organization$Some Table'' NullPointerException