I have a postgres database running on a remote server. I'd like to be able to query it in SQL Server.
I downloaded the ODBC Driver for postgres and created a system DSN which successfully runs a connection test.
I created the linked server on my SQL Server and I can see the tables of my postgres database.
However, I get the following errors when trying to query those tables:
1 - select * from [LinkedServer].[postgresDB].[public].[TableName]
The OLE DB provider "MSDASQL" for linked server "[LinkedServer]" supplied inconsistent metadata for a column. The column "Name" (compile-time ordinal 2) of object [postgresDB].[public].[TableName] was reported to have a "DBCOLUMNFLAGS_ISLONG" of 128 at compile time and 0 at run time.
2 - select * from openquery(LinkedServer, 'select * from [postgresDB].[public].[TableName]')
cross-database references are not implemented: [postgresDB].[public].[TableName]
3 - When right clicking on the table and pressing script table to new select window
Cannot get the data of the row from OLE DB provider for linked server (null). Conversion failed because the data value overflowed the data type used by the provider.
To isolate the issue, I did the same thing in MS Access to determine if it was on the postgres side or the SQL side. In MS Access the data is shown beautifully. Therefore I am of the opinion that there's an issue with my linked server in SQL athough I don't know what it can be. I've created the linked server with all the default configurations.
Any help would be appreciated.