0

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.

  • Access is a 32bit program. SQL Server is 64bit. Are you sure you have a 64bit ODBC driver installed? – David Browne - Microsoft Feb 10 at 17:11
  • With Access I used the 32 bit and it worked. With SQL I used the 64 bit and it didn't work. I thought maybe I should use the 32 bit on SQL. Would that make sense? – Dassy Feb 10 at 17:13
  • The ODBC driver must match the "bitness" of the program using it. – David Browne - Microsoft Feb 10 at 17:19
  • As far as I'm concerned, it is. What else can be causing the issue? – Dassy Feb 10 at 17:21
  • "However, I get an error when trying to query those tables." Could you edit your question to include the error message(s)? – HardCode Feb 10 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.