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;
PostgreSQL ODBC driver using SSRS with temp tables
Re: PostgreSQL ODBC driver using SSRS with temp tables
Hi John,
In order to use multiple commands please set Protocol = pv20 in settings of your ODBC Driver for PostgreSQL.
Regards,
Evgeniy
In order to use multiple commands please set Protocol = pv20 in settings of your ODBC Driver for PostgreSQL.
Regards,
Evgeniy
Re: PostgreSQL ODBC driver using SSRS with temp tables
Hi Evgeniy,
Thank you so much! The pv20 worked perfect!
John
Thank you so much! The pv20 worked perfect!
John
Re: PostgreSQL ODBC driver using SSRS with temp tables
Hi John,
I’m glad that your issue is resolved and you can use all features of the software. Please feel free to contact us anytime in case you have any other questions.
Regards,
Evgeniy
I’m glad that your issue is resolved and you can use all features of the software. Please feel free to contact us anytime in case you have any other questions.
Regards,
Evgeniy
Re: PostgreSQL ODBC driver using SSRS with temp tables
Hi Evgeniy,
I am getting so close. We have moved my prototype from DEV to UAT and here we unfortunately have SSH security. I am getting the message "Host key not verified." I have given the public and private files different names. Any help very welcome.
Private_id_rsa
id_rsa.pub
I am getting so close. We have moved my prototype from DEV to UAT and here we unfortunately have SSH security. I am getting the message "Host key not verified." I have given the public and private files different names. Any help very welcome.
Private_id_rsa
id_rsa.pub
Re: PostgreSQL ODBC driver using SSRS with temp tables
Encase someone ends up here, I successfully configured the SSH. The company uses Jumpcloud and I found the need was to leave the server public key blank but key in the path.
Re: PostgreSQL ODBC driver using SSRS with temp tables
Hi John,
You may find information about how to set up SSH connection for ODBC Driver for PostgreSQL in our online documentation:
https://www.devart.com/odbc/postgresql/docs/ssh.htm
If you face any issues with setting up SSH connection according to instructions from documentation please feel free to contact us back.
Best regards,
Evgeniy
You may find information about how to set up SSH connection for ODBC Driver for PostgreSQL in our online documentation:
https://www.devart.com/odbc/postgresql/docs/ssh.htm
If you face any issues with setting up SSH connection according to instructions from documentation please feel free to contact us back.
Best regards,
Evgeniy