How to access Postgres data from SQL Server (linked servers)

Question

I have the following situation:

DB Server 1 is Sql Server 2008 and hosts database A with transaction records DB Server 2 is Postgres and hosts database B with records of prices

Server2 is added as a linked server to server1.

I need to run a query where for each transaction record on server1 I need to check a number of prices from server2.

Ideally it would look like this (run on server1):

select t1.clientid, t1.item, t1.price, t1.time, server2.databaseX.myfunction(t1.item, t1.time) from transactions t1 where whatever

I toyed with openquery but i run into issues trying to embed parameters into dynamic sql.

Is there a convenient way to run this sort of query across these two servers?

I can't export relevant data from server2 to server1 because the amount of it is huge. I could do it the other way around but then I have to return everything back to server1. How would this be practically done if it need to run every time a user requests it from Reporting Server (running on server1)?

Thanks.

Answer 1

I'd recommend connecting to server2 in client application and then performing a second query in it. It will be IMHO easier.

Answer 2

OPENQUERY is definitely a way to do it; what issues are you having with dynamic SQL?

Perhaps create a temp table in server 1, import a filtered subset of the PostgreSQL data using a parameterized query into that temp table, and then join onto your temp table in your final SQL query.