Querying a PostGres View from SQL Server

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Querying a PostGres View from SQL Server

Postby mradams » Wed Jun 11, 2014 9:33 am

I have created three views on my company's postgres server that I would like to query using the OLEDB adapter from SQL Server via linked server. I tried using an OPENQUERY statement to reference the view, but I get an undefined table message. (see below)

SELECT *
FROM OPENQUERY (BSI,'SELECT * FROM public."BSI_AccountContainer"')

Can someone help with my syntax to see if that is an issue?
mradams
 
Posts: 2
Joined: Wed Jun 11, 2014 9:30 am

Re: Querying a PostGres View from SQL Server

Postby Moderator » Mon Jun 16, 2014 5:21 pm

We were unable to reproduce the issue. Could you upgrade to the latest version, and see if you can reproduce the issue?

If the issue is still reproducible with the latest build, could you e-mail detailed steps to our Support address?

Thank you
User avatar
Moderator
Site Admin
 
Posts: 305
Joined: Wed Oct 29, 2008 11:27 pm

Re: Querying a PostGres View from SQL Server

Postby mradams » Tue Jun 17, 2014 8:18 am

Actually, I found out what I was doing wrong...it appears the provider utilizes views just like tables when referencing them via linked server.

By using this sort of syntax, I am able to pull the data from the views with no issue:

SELECT *
FROM "BSI"."production"."public"."BSI_AccountContainer"

Thanks
mradams
 
Posts: 2
Joined: Wed Jun 11, 2014 9:30 am


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 1 guest

cron