SSRS Dataset Parameter on an REDSHIFT ODBC Connection

Question

I am currently trying to run reports off redshift via ODBC with SSRS.

I expected that the query would run as the SQL is valid however it looks like SSRS is passing the Parameter name into the SQL query back to Redshift instead of the parameter value as id expect please consider the below query.

SELECT x.Col_1, y.Col_2, x.Col_3, x.Col_4 
From x_table as x
JOIN Y_table as y on x.Col_4 = y.Col_4
WHERE x.Col_4 = @Col_4 
  AND x.col_3 >= @From and x.col_3 < @To
  AND x.col_2 >= @From and x.col_2 < @To

In SSRS when I attempt to refresh the field list I get the below error,

ERROR [42702] [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42702] ERROR: column reference "From" is ambiguous

This query is using parameters to query the database to return only a subset of the data depending on the specific customer and a categorical field I cannot move the filters out of the report.

Troubleshooting steps

When running this query on redshift and removing the parameters it works fine.
When running this query on SQL Server with the parameters it works fine.
When running this query using the ? as the parameter name it works fine for the first parameter only.
When running this query using parameters other then column definitions it still fails with similar errors

Research this seems to be a common issue with ODBC data sources but without using multiple parameters most people seem to solve it by just using ? but that only works for single parameters.

I found two similar stack overflows but neither helped me solve this variation.

My suspicion is that ODBC datasets require some sort of escape or similar so that SSRS can replace the parameter names with values but I can't find this documented anywhere because if using ? as the parameter name works then surely it's just a matter of using some sort of escape.

You should be able to have multiple parameters escaped with ?, and they will each be populated in the same order that parameters are populated from your report. Ex: If you have Where x.Col_4 = @Col_4 and x.col_3 = @Col_3 , then replace it with Where x.col_3 = ? AND x.Col_4 = ? , if @col_3 is populated before @col_4. Is this not successful for you? — CoffeeNeedCoffee
it doesn't work because in the actual query I need to reference the parameters more then once for date time lookups — Dillon Wright
Ah, in that case, are you able to just throw a DECLARE @from DATETIME = ? up before your SELECT statement? I've done that within both SSIS and SSRS to manipulate parameters before the Select itself. This would enable you to preserve the parameters in the right order, and ODBC isn't trying to resolve the @col_3 from a parameter input, but will hopefully handle @from that you've explicitly laid out for it. — CoffeeNeedCoffee

Dillon Wright Dillon Wright · Accepted Answer · 2021-01-07T23:27:21

Just in case anyone else has this issue i ended up solving this by using a linked server and OPENQUERY this is actually a pretty ideal solution because it enables you to write PGSQL into the report if you want technically you could even do subsequent joins on SQL Server data.

DECLARE @OPENQUERY nvarchar(4000), @PGSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'Redshift'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @PGSQL = 'SELECT x.Col_1, y.Col_2, x.Col_3, x.Col_4 
From x_table as x
JOIN Y_table as y on x.Col_4 = y.Col_4
WHERE x.Col_4 = '''''+ @Col_4 +'''''
AND x.col_3 >= '''''+ @From +''''' and x.col_3 < '''''+ @To +'''''
AND x.col_2 >= '''''+ @From+ ''''' and x.col_2 < '''''+ @To +''''''
EXEC (@OPENQUERY+@PGSQL)

SSRS Dataset Parameter on an REDSHIFT ODBC Connection

1 Answers