Connect ProgreSQL to SSMS via Linked Server

Connect ProgreSQL to SSMS via Linked Server

Postby michaelvanderveeke » Thu 09 Nov 2017 05:54

Hello,
I have to connect to a progresSQL database and when I run a query like SELECT * FROM OPENQUERY(SEQTA, 'select * from public.student') it works, however when I run this query SELECT * FROM OPENQUERY(SEQTA, 'select * from studentplandetailStudentplanfield') I get the following error

OLE DB provider "MSDASQL" for linked server "SEQTA" returned message "[Devart][ODBC][PostgreSQL]relation "studentplandetailstudentplanfield" does not exist".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "SEQTA".

Now the table exists as I can use another ODBC driver and it works.

I have created a document with all of the settings https://www.dropbox.com/s/8gs0n365mxh8k5q/ODBC%20Settings.docx?dl=0

The other issue, which is not a show stopper, is when I run SELECT * FROM seqta.au_nsw_trinityac.[public].[student] I get this error

Msg 7422, Level 16, State 1, Line 4
OLE DB provider "MSDASQL" for linked server "seqta" returned an invalid index definition for table ""au_nsw_trinityac"."public"."student"".

I have spent all day trying to get this to work with no success.

Thanks
Michael
michaelvanderveeke
 
Posts: 2
Joined: Thu 09 Nov 2017 05:47

Re: Connect ProgreSQL to SSMS via Linked Server

Postby michaelvanderveeke » Thu 09 Nov 2017 05:55

Oh and to save time, I have tried the suggestions on this page https://www.devart.com/odbc/salesforce/docs/index.html?troubleshooting_ssms.htm
michaelvanderveeke
 
Posts: 2
Joined: Thu 09 Nov 2017 05:47

Re: Connect ProgreSQL to SSMS via Linked Server

Postby Stellar » Thu 09 Nov 2017 09:59

It's obvious that when the table was being created, its name was quoted (for example, CREATE TABLE "studentplandetailStudentplanfield" ...), therefore, when executing a query, you should specify the table name in quotes. A sample of executing the query:

SELECT * FROM OPENQUERY(SEQTA, 'select * from "studentplandetailStudentplanfield"'
Stellar
Devart Team
 
Posts: 15
Joined: Tue 03 Oct 2017 11:00


Return to ODBC Drivers