I am getting the following error:
Incorrect syntax near ‘.’.
I am passing a variable into a SQL procedure. The variable is being built from a Filter Widget. I suspect the issue is because the widget is making a “SQL Snippet”. If true, I don’t understand the syntax of what is being passed into the procedure. Here is my test procedure to try to capture what is coming into the procedure as a variable:
Question custom query: sandbox..testMetabaseVariable {{list}}
Procedure: `SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.testMetabaseVariable
@incomingString nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;
select @incomingString
END
GO
`
Here is the log from Metabase:
[c521eb94-6159-4d97-85ab-8e01953b633a] 2020-02-17T16:48:33-07:00 DEBUG metabase.middleware.log POST /api/dataset 200 [ASYNC: completed] 172.6 ms (12 DB calls) Jetty threads: 2/50 (6 idle, 0 queued) (202 total active threads) Queries in flight: 1
[c521eb94-6159-4d97-85ab-8e01953b633a] 2020-02-17T16:48:42-07:00 ERROR metabase.driver.sql-jdbc.execute nil
[c521eb94-6159-4d97-85ab-8e01953b633a] 2020-02-17T16:48:42-07:00 WARN metabase.query-processor.middleware.process-userland-query Query failure {:status :failed,
:class com.microsoft.sqlserver.jdbc.SQLServerException,
:error “Incorrect syntax near ‘.’.”,
: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
{:type “native”,
:native
{:template-tags {:list {:id “c7b4287c-572e-9f69-7433-e5a52f95834b”, :name “list”, :display_name “List”, :type “dimension”, :display-name “e”, :dimension [“field-id” 104860], :widget-type “id”}},
:query “sandbox…testMetabaseVariable {{list}}”},
:parameters [{:type “id”, :target [“dimension” [“template-tag” “list”]], :value [“Approve PTO Granted”]}],
:async? true,
:middleware {:add-default-userland-constraints? true, :userland-query? true},
:info
{:executed-by 2,
:context :ad-hoc,
:card-id nil,
:nested? false,
:query-hash [67, -90, -82, 66, 91, 22, -85, 24, -73, -102, -51, 75, 2, 9, 33, 92, -87, -125, -99, -14, -30, 124, -76, 25, 27, 21, -107, -39, 48, 11, 69, 21]},
:constraints {:max-results 10000, :max-results-bare-rows 2000}}}