Linked server sample using SQL Server 2005

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

Re: Linked server sample using SQL Server 2005

Postby Moderator » Sun Sep 13, 2009 9:41 pm

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

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

Re: Linked server sample using SQL Server 2005

Postby Info@consolidata.co.uk » Thu Mar 04, 2010 8:41 am

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

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 ???????? :!: :cry:

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
Info@consolidata.co.uk
 
Posts: 4
Joined: Mon Feb 15, 2010 2:28 am

Re: Linked server sample using SQL Server 2005

Postby Moderator » Thu Mar 04, 2010 8:13 pm

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?
User avatar
Moderator
Site Admin
 
Posts: 314
Joined: Wed Oct 29, 2008 11:27 pm

Re: Linked server sample using SQL Server 2005

Postby Info@consolidata.co.uk » Mon Mar 08, 2010 8:34 am

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
Info@consolidata.co.uk
 
Posts: 4
Joined: Mon Feb 15, 2010 2:28 am

Re: Linked server sample using SQL Server 2005

Postby Moderator » Mon Mar 08, 2010 11:25 pm

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
User avatar
Moderator
Site Admin
 
Posts: 314
Joined: Wed Oct 29, 2008 11:27 pm

Re: Linked server sample using SQL Server 2005

Postby Info@consolidata.co.uk » Thu May 27, 2010 5:38 am

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
Info@consolidata.co.uk
 
Posts: 4
Joined: Mon Feb 15, 2010 2:28 am

Re: Linked server sample using SQL Server 2005

Postby Moderator » Mon May 31, 2010 6:16 pm

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

I was unable to reproduce the issue.
User avatar
Moderator
Site Admin
 
Posts: 314
Joined: Wed Oct 29, 2008 11:27 pm

Re: Linked server sample using SQL Server 2005

Postby Info@consolidata.co.uk » Thu Jun 03, 2010 2:47 am

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
Info@consolidata.co.uk
 
Posts: 4
Joined: Mon Feb 15, 2010 2:28 am

Re: Linked server sample using SQL Server 2005

Postby Moderator » Thu Jun 03, 2010 8:01 am

Please send Postgres table DDL. Any additional information that will help reproduce the issue is appreciated.
User avatar
Moderator
Site Admin
 
Posts: 314
Joined: Wed Oct 29, 2008 11:27 pm

Previous

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

Who is online

Users browsing this forum: No registered users and 2 guests