Linked server sample using SQL Server 2005

[Moderator]

Jason,
I haven't been able to reproduce the issue. Would you please also send PGNP version, OS version, MSSQL version?

Thank you!

[Info@consolidata.co.uk]

Hi This seems to work fine for me following the steps given (thanks )

i now have a query issue

I did a count on a table in the postgresAdmin the returned close to a million rows
the same query using open query does not run at all. the count errors

so i did a select query

query
SELECT customer_id
FROM
OPENQUERY (PG_ClientDB, 'SELECT customer_id FROM customers;
')

Error
Cannot process the object "SELECT customer_id FROM customers;
". The OLE DB provider "MSDASQL" for linked server "PG_ClientDB" indicates that either the object has no columns or the current user does not have permissions on that object.

strange error behavior from here because if i add ' LIMIT 400000' (or upto that value) This returns the select query back with the 400000 rows

any thing higher and it fails

Then wen i check the catalog folder for my pg database it has vanished 3 minutes later i then comes back ????????

is there a limitation on the odbc driver or using Open Query causing this to happen ?

Thanks in advance it would be really great to get this solved

Rgds

Dwain

[Moderator]

It can be caused by a SQL Server buffer or smth related to the Linked Server implementation.

Could you try different syntax for the query (called 4-part identifiers):
SELECT customer_id FROM PG_ClientDB...customers

What is the customer_id type (and the table schema)? Do you run 32-bit SQL Server?

[Info@consolidata.co.uk]

HI


I tried using this 4 part but still get an error

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

This is quite frustraighting as i thought that a linkserver would be able handle this

but i actually think its a bug with the ODBC provider (driver) any queries on tables over 400,000 return an error.

(Moderator) Could you try a count or a select query and see if you have the same results

Thanks

[Moderator]

Hi Dwain,
I have tried both 4-part identifier and OPENQUERY syntax for selecting customer_id column from my own customers test table:
SELECT * FROM OPENQUERY(PGNP_SAMPLE, 'SELECT customer_id FROM customers');
SELECT customer_id FROM PGNP_SAMPLE...customers;

And both worked well (customer_id was declared as int, and number of row was over 2 million).

Would you send customers table definition and the PGNP provider version you used?

Thank you,
Konstantin

[Info@consolidata.co.uk]

Hi

sorry its taken me so long to reply to this as i was just writing a blog and thought id look at the issues i had with setting up a SQL to PGSQL database

Question:
Your saying that you can do a select on a table over 400,000 records using Sql Server Linked serever to PG Database ?

I resolved this issue with doing a loop round in ssis with a offset varible as doing the same query in ssis caused it to fail

maybe it the temp db setting causing the timeout how do you configure this in Sql for linkedservers

[Moderator]

Yes, it should work with 400K or more rows. Would you send the table DDL?

I was unable to reproduce the issue.

[Info@consolidata.co.uk]

Hi

do you required the DDL From PG or sql linked server setup

i know this stupid but its difficult to point point what is failing here

[Moderator]

Please send Postgres table DDL. Any additional information that will help reproduce the issue is appreciated.