I've created a report that uses variables to collect data and have them declared in the SQL Statement field. But when validating the report statement an error occurs: "DataCollector returned error: Must declare the scalar variable "@OvrBgtMinAmt". I am able to run this same query successfully from a SQL Server client however. Help!

The short answer is to remove the semicolon after the DECLARE statement.

The long answer: there are several things affecting this issue, the most important being that most JDBC drivers do not allow multiple statements to be sent to the database in one JDBC query.

For example, MySQL, PostgreSQL, and Oracle all fail on the following query when run as a single JDBC statement:

create temporary table foo as select 1 i union select 2; 
select * from foo

This is actually a good thing, because it helps protect against SQL injection attacks. Comparatively, SQL Server is one of the few JDBC drivers that actually allows sending multiple statements in one JDBC call.

Since Metric Insight users often want to run sql scripts like what's shown above for some light ETL procedures, MI was designed to parse out sql scripts separated by semicolons and run each query separately, all sharing the same connection and database session. This allows for users to do multiple ETL queries before getting to the actual metric query.

Unfortunately, not all JDBC drivers and databases are created equal, even though things like this work in MySQL in MI's batch mode processing:

set @foop :=3 ; select @foop

(MI first sends `set @foop :=3`, then sends another `select @foop` to the database)

SQL Server does NOT like this. It will yell when running "select @foop" in it's own jdbc call, even though the declare statement was run earlier. However, SQL Server is fine with this query:

declare @foop int
set @foop = 3
select @foop

where each of these statements is separated by new lines or whitespace. So, in order for this to work with SQL Server, do not include any semicolons to separate statements. The above syntax will work in Metric Insights.