Issues using Openquery pulling PostGres data to SQL Server

We currently have a third party tool that runs on a MYSQL database.
This tool is going to be upgraded and part of the upgrade requires a migration to a PostGres database.
Here is the query that works from SQL Server to Mysql -

SELECT Email, CONVERT(INT, count) AS clicks, date AS Last_Clicked_Date
FROM OPENQUERY(test, 'SELECT Email, SUM(count) count, MAX(date) date FROM tracking WHERE action="click" GROUP BY Email')

There were some schema changes involved so I modified the query as follows -

select email_address, CONVERT(INT, count) as clicks, data AS Last_Clicked_date
from openquery (test, select email_address, sum(count) count, max(tracking_date) tracking_date from tracking where action_name =''click'' group by email_address')

I get this error when running the above statement.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "test" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "test".

I can run the following with no problems, so I believe it just maybe in the syntax, I haven't worked with Postgres before -

select email_address, tracking_date
from openquery(test, 'select * from tracking')

Any help would be greatly appreciated.
Thanks

The mysql example and the query in the working example are both quoted. The query in the non-working example is not.

My first thought was a bad odbc config for the postgres database but if you have a working query, my guess is the quoting.

Also, the data value, in postgres should normally be quoted with single quotes.

LewisC

I'm not sure which version of the database you're using or if it is advanced server.

Something else you might want to try is to use AS in your alias, i.e.:

'select email_address, sum(count) AS count, max(tracking_date) AS tracking_date from tracking where action_name =''click'' group by email_address'

That might be the error also.

LewisC

This is a third party software that includes the postgres database.
I believe it's 8.7.1 and isn't advanced server.

When I run the query on Postgres, it does use single quotes.
However, when I try running it like that via SQL I get the following error message.

Incorrect syntax near 'click'.

If I use two single quotes or a double quote I get the catastrophic error message.

I also added the 'AS' and changed the alias to not use any SQL Server reserved words, but still received the same error message
--Modified code
from openquery
(test, 'select email_address, sum(count)as MYCount , max(tracking_date) as MYDate
from tracking where action_name = "click" group by email_address')

Now when I run the following query (with two single quotes) I get a result set back.

Select *
from openquery
(test, 'select email_address, count
from tracking where action_name = ''click'' group by email_address, count')

Could the issue be with Max/Sum?

I created a sample table:

I added some data:

I cut and pasted your query and it had double quotes around the action_type. I changed that to single quotes and it worked. Other than that, the query is fine.

It may be that sql server is not passing the string over correctly. If you have a postgres admin, they can check the log to see exactly what is being sent.

LewisC

Yes the query does work in Postgres, I will try and find where the log is and take a look.
If I can't figure it out, I will open a ticket with Microsoft.

Thanks for your help

I was able to get the query to work when using a derived table - Thanks for your help!!

select *

from openquery

(test,'SELECT * FROM

(select email_address, sum(count) as MYCount , max(tracking_date) as MYDate

from tracking where action_name = ''click'' group by email_address) as email_counts')

Cool. That's weird that it sends it over like a sub-select.

Glad you got it working!

LewisC