SQL Server Invalid Object Name

michaelgillum · November 27, 2019, 2:31pm

Using Metabase 0.33.5.1 on Centos connecting to SQL Server 2014

We randomly started getting SQL Server exceptions in the form of “Invalid object name ‘TableName’” where 100% the table exists. Some of these are our main invoice tables etc, there is no doubt that they are there. Our solution generally has been to add to the queries the full [DatabaseName].dbo.TableName which works sometimes, however you cannot use for field filter variables.

Any thoughts? The database name is setup in the connection and it is syncing every night. One minute the question will work, then refresh and the log shows the error. We have seen this on multiple Metabase versions, hoping installing the latest will have a fix.

Thanks in advance.

flamber · November 27, 2019, 4:52pm

Hi @michaelgillum
Can you post the full stack error?
Since which version did you start seeing this problem? And is there a version, where everything worked?
I haven’t seen this error before, but doing an internet search on sql server jdbc "Invalid object name" returns a lot of results.
The MSSQL driver will be upgraded from 7.0.0 to 7.4.1 in Metabase 0.34.0, so maybe that’ll change/fix something.

michaelgillum · December 5, 2019, 2:07pm

Unfortunately I haven’t seen it since I posted this. To get around it we have been rebooting the server nightly, which I have since disabled to try to get the error to happen. It’s very sporadic so I’m hoping the lack of reboot will make it pop up again.

michaelgillum · December 16, 2019, 7:28pm

Here is one example:

[85d4642f-0831-4fd6-a326-8e02c6191e23] 2019-12-16T13:44:57-05:00 WARN metabase.query-processor.middleware.process-userland-query Query failure {:status :failed,
:class com.microsoft.sqlserver.jdbc.SQLServerException,
:error “Invalid object name ‘HcArHist’.”,
:stacktrace
(“com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254)”
“com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608)”
“com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:578)”
“com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508)”
“com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)”
“com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)”
“com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)”
“com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)”
“com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:434)”
“com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)”
“clojure.java.jdbc$execute_query_with_params.invokeStatic(jdbc.clj:1072)”
“clojure.java.jdbc$execute_query_with_params.invoke(jdbc.clj:1066)”
“clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1081)”
“clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1075)”
“clojure.java.jdbc$query.invokeStatic(jdbc.clj:1164)”
“clojure.java.jdbc$query.invoke(jdbc.clj:1126)”
“–> driver.sql_jdbc.execute$cancelable_run_query$fn__66651.invoke(execute.clj:182)”
“driver.sql_jdbc.execute$do_with_ensured_connection.invokeStatic(execute.clj:159)”
“driver.sql_jdbc.execute$do_with_ensured_connection.invoke(execute.clj:157)”
“driver.sql_jdbc.execute$cancelable_run_query.invokeStatic(execute.clj:174)”
“driver.sql_jdbc.execute$cancelable_run_query.invoke(execute.clj:170)”
“driver.sql_jdbc.execute$run_query.invokeStatic(execute.clj:196)”
“driver.sql_jdbc.execute$run_query.invoke(execute.clj:192)”
“driver.sql_jdbc.execute$do_with_auto_commit_disabled.invokeStatic(execute.clj:243)”
“driver.sql_jdbc.execute$do_with_auto_commit_disabled.invoke(execute.clj:231)”
“driver.sql_jdbc.execute$do_in_transaction$fn__66672.invoke(execute.clj:248)”
“driver.sql_jdbc.execute$do_in_transaction.invokeStatic(execute.clj:247)”
“driver.sql_jdbc.execute$do_in_transaction.invoke(execute.clj:246)”
“driver.sql_jdbc.execute$run_query_without_timezone.invokeStatic(execute.clj:265)”
“driver.sql_jdbc.execute$run_query_without_timezone.invoke(execute.clj:264)”
“driver.sql_jdbc.execute$execute_query$fn__66699.invoke(execute.clj:297)”
“driver.sql_jdbc.execute$do_with_try_catch.invokeStatic(execute.clj:223)”
“driver.sql_jdbc.execute$do_with_try_catch.invoke(execute.clj:217)”
“driver.sql_jdbc.execute$execute_query.invokeStatic(execute.clj:291)”
“driver.sql_jdbc.execute$execute_query.invoke(execute.clj:285)”
“driver.sql_jdbc$fn__67318.invokeStatic(sql_jdbc.clj:44)”
“driver.sql_jdbc$fn__67318.invoke(sql_jdbc.clj:43)”
“query_processor$fn__44023$execute_query__44028$fn__44029.invoke(query_processor.clj:71)”
“query_processor$fn__44023$execute_query__44028.invoke(query_processor.clj:65)”
“query_processor.middleware.mbql_to_native$mbql__GT_native$fn__34284.invoke(mbql_to_native.clj:38)”
“query_processor.middleware.annotate$result_rows_maps__GT_vectors$fn__36550.invoke(annotate.clj:541)”
“query_processor.middleware.annotate$add_column_info$fn__36456.invoke(annotate.clj:485)”
“query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__37491.invoke(cumulative_aggregations.clj:57)”
“query_processor.middleware.resolve_joins$resolve_joins$fn__41131.invoke(resolve_joins.clj:184)”
“query_processor.middleware.limit$limit$fn__38092.invoke(limit.clj:19)”
“query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__43890.invoke(results_metadata.clj:87)”
“query_processor.middleware.format_rows$format_rows$fn__38080.invoke(format_rows.clj:26)”
“query_processor.middleware.add_dimension_projections$add_remapping$fn__35043.invoke(add_dimension_projections.clj:232)”
“query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__35684.invoke(add_source_metadata.clj:107)”
“query_processor.middleware.resolve_source_table$resolve_source_tables$fn__41181.invoke(resolve_source_table.clj:46)”
“query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__35531.invoke(add_row_count_and_status.clj:16)”
“query_processor.middleware.driver_specific$process_query_in_context$fn__37567.invoke(driver_specific.clj:12)”
“query_processor.middleware.add_settings$add_settings$fn__35554.invoke(add_settings.clj:45)”
“query_processor.middleware.resolve_driver$resolve_driver$fn__40795.invoke(resolve_driver.clj:22)”
“query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36881$fn__36882.invoke(bind_effective_timezone.clj:9)”
“util.date$call_with_effective_timezone.invokeStatic(date.clj:88)”
“util.date$call_with_effective_timezone.invoke(date.clj:77)”
“query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36881.invoke(bind_effective_timezone.clj:8)”
“query_processor.middleware.store$initialize_store$fn__43915$fn__43916.invoke(store.clj:11)”
“query_processor.store$do_with_store.invokeStatic(store.clj:46)”
“query_processor.store$do_with_store.invoke(store.clj:40)”
“query_processor.middleware.store$initialize_store$fn__43915.invoke(store.clj:10)”
“query_processor.middleware.async$async__GT_sync$fn__34195.invoke(async.clj:23)”
“query_processor.middleware.async_wait$runnable$fn__36607.invoke(async_wait.clj:89)”),
:query
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :native,
:middleware {:userland-query? true},
:native
{:query
“SELECT \r\tTOP 20\r\tFULLNAME \r\t,ROUND(SUM(QUANTitY * EquivalentUnits),0) AS UnitsSold\r\t,HCD.DivisionName AS HomeDivision\r\rFROM \r\tHcArHist ARH \r\t\tINNER JOIN HcProduct HCP \r\t\t\tON HCP.ItemNumber = ARH.ItemNumber\r\t\tINNER JOIN HrEmployee HRE \r\t\t\tON HRE.EmployeeID = ARH.DriverNumber\r\t\tINNER JOIN ArCust ARC\r\t\t\tON ARC.CustomerNumber = ARH.CustomerNumber\r\t\tINNER JOIN HcSalesClass SC \r\t\t\tON ARC.SalesClassification = SC.ClassCode\r\t\tINNER JOIN HcDivision HcD \r\t\t ON HCD.DivisionNumber = HRE.DivisionNumber\r\t\t\rWHERE \r\tInvoiceDate >= ‘01/01/2019’\r–\tAND SC.UseForRoutePlanning = 1\r\tAND EmployeeID NOT like ‘9%’\r\tAND EmployeeID NOT LIKE ‘6%’\r\tAND EMPLOYEEID <> ‘DOCKS’\r\r\t\r\tAND ARH.ItemSaleAmount < 1500\rGROUP BY \r\tFullName\r\t,HCD.DivisionName\r\rORDER BY \r\tSUM(quantity * EquivalentUnits) DESC”},
:info
{:executed-by nil,
:context :public-question,
:card-id 376,
:dashboard-id nil,
:query-hash [-124, 98, -16, 16, -19, -75, 52, -20, -128, -25, -18, 117, -39, 118, -30, 82, -20, -85, -100, -22, -19, -5, -107, -29, 95, 58, -24, 20, 24, -26, 35, 94]},
:parameters (),
:async? true,
:cache-ttl nil}}

flamber · December 16, 2019, 7:53pm

@michaelgillum
That’s the SQL Server returning the error Invalid object name 'HcArHist', which looking at the query seems to be the FROM-clause FROM \r\tHcArHist ARH

So is HcArHist a table, function, …?
Is HcArHist accessible in the schema by the user that Metabase is using?

Try running the query on your SQL Server with the same user as you’re using for Metabase.

michaelgillum · December 17, 2019, 12:31am

HcArHist is a table that I can 100% guarantee exists. It works fine when I run it on the server. It runs fine a lot of times inside Metabase as well, but every so often we get that error. If I refresh the page a few times it will eventually run, then refresh again and we get the error.

If we add [DATABASE].[dbo] in front of the table name inside the query it will work fine also, but we don’t have to add it in front of every table in the query, just the one giving the error. It is not the same table every time.

If I restart the Metabase server, I will not see the error for a few days. I’ve disabled the rebooting for right now.

flamber · December 17, 2019, 12:43am

@michaelgillum
Okay, but the error is coming from MSSQL, as far as I can tell. Check your log on the database, maybe it’ll tell you more details.

Have you tried adding the databaseName=YourDatabaseNameHere to the Connection String?
https://stackoverflow.com/questions/44543855/jdbc-invalid-object-name-table-name
(found by doing an internet search for sql server jdbc "Invalid object name")

It might have something to do with the JDBC connection driver, but as I said before, it will be upgraded in 0.34.0 (coming soon)