SSRS error must declare scalar variable when using ODBC connection to SQL SERVER

Question

I have an SSRS dataset query with a single value parameter called BENEFIT. My datasource is SQL SERVER 2014: The following query gives the error "ERROR [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@BENEFIT".

SELECT  c.BENEFIT  FROM  CLAIM AS c
WHERE c.DOWNLOAD_DATE > '20150701' AND c.BENEFIT = (@ BENEFIT)

The error exists ONLY when using an ODBC connection and goes away using a direct connection. Without the parameter the query parses correctly using either connection method.

Any thoughts appreciated.

Thanks, Alasdair

Answer 1

ODBC does not support named parameters, so try changing the parameter name to ?:

SELECT c.BENEFIT FROM CLAIM AS c WHERE c.DOWNLOAD_DATE > '20150701' AND c.BENEFIT = ?

Further information here: https://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.90).aspx

Pay mind to the section "Using Parameters with an OleDbCommand or OdbcCommand."

Answer 2

SET NOCOUNT ON before the query and then SET NOCOUNT OFF after the query if you declare the variable in the query. If the variable is declared outside the query then use ? as PicoDeGallo stated.