PostgreSQL ODBC driver using SSRS with temp tables
Posted: Fri 03 Sep 2021 05:44
I've successfully configured the Devart ODBC driver to connect a data source in SSRS.
It works fine with a straightforward select query and returns the data as expected. I was moving down the complexity scale and I tried to get a temp table to work. I cut the query back to its bare essentials and it continues to fail. I tried removing the drop table statement and adding a # to the temp table, as I've done with MSSQL to no improvement.
I am receiving this error message in SSRS:
ERROR [HY000] [Devart][ODBC][PostgreSQL]cannot insert multiple commands into a prepared statement
This is the basic query. My fear is that the driver doesn't support temp tables in postgreSQL. Has anyone found a way to use temp tables via the ODBC driver in SSRS? All help would be welcome.
--Date query
drop table if exists daterange;
CREATE TEMP TABLE daterange(
runDate date
)
;
insert into daterange
SELECT
CURRENT_DATE
;
select * from daterange;
It works fine with a straightforward select query and returns the data as expected. I was moving down the complexity scale and I tried to get a temp table to work. I cut the query back to its bare essentials and it continues to fail. I tried removing the drop table statement and adding a # to the temp table, as I've done with MSSQL to no improvement.
I am receiving this error message in SSRS:
ERROR [HY000] [Devart][ODBC][PostgreSQL]cannot insert multiple commands into a prepared statement
This is the basic query. My fear is that the driver doesn't support temp tables in postgreSQL. Has anyone found a way to use temp tables via the ODBC driver in SSRS? All help would be welcome.
--Date query
drop table if exists daterange;
CREATE TEMP TABLE daterange(
runDate date
)
;
insert into daterange
SELECT
CURRENT_DATE
;
select * from daterange;