0

Linked server working with three dot notation, but not with 4 part name in SQL Server.

For example: when I run

SELECT top 1 * 
FROM [POSTGREFGH]...DEPARTMENT

I am getting results, but when I try this:

SELECT top 1 * 
FROM [POSTGREFGH].MyDB.[MyShema].DEPARTMENT

I get this error:

Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "POSTGREFGH". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

My source server is : POSTGRESQL

Where and what was wrong with this?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3583912
  • 1,302
  • 1
  • 17
  • 23
  • 1
    if the text in the post is the error message i think it is self explanatory: A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema -> change provider. if it is not so, please provide some useful information like the involved server software (all sql server?) the driver version and so on. – Paolo Oct 26 '15 at 12:50
  • Is the `POSTGREFGH` server a PostgreSQL server, by any chance? – Matt Gibson Oct 26 '15 at 12:51
  • @Matt Gibson, Yes it is `PostgreSQL` – user3583912 Oct 26 '15 at 12:52
  • And you are 100% sure you use correct db/schema? – Lukasz Szozda Oct 26 '15 at 12:58
  • @lad2025, Yes I am 100% sure, not here but in my actual query. thanks – user3583912 Oct 26 '15 at 12:59
  • See the answer to this question: http://stackoverflow.com/questions/14779212/catastrophic-failure-trying-to-select-from-linked-server – Tab Alleman Oct 26 '15 at 13:12
  • We'll need to know what driver you're using and how you're setting things up. MSDASQL is the ODBC driver for OLEDB, so there's already a few layers here -- can you set the schema, etc. in the ODBC configuration instead of specifying it in the SELECT? With ODBC, you may need a different connection per schema, perhaps... You might need to change to a non-ODBC driver to get the functionality you need, but I don't know much about PostgreSQL drivers. – Matt Gibson Oct 26 '15 at 13:37

0 Answers0