Update not working ?!?

Report PGNP bugs.

Update not working ?!?

Postby Chira » Fri Mar 11, 2011 9:17 am

Hy,
I'm testing version 1.30 and when I tried to update a field I get this error:
OLE DB provider "PGNP" for linked server "PostgreSql" returned message "Row cannot be located. Make sure the table has primary key.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "PGNP" for linked server "PostgreSql" could not UPDATE table "[PostgreSql].[xxxx].[public].[table]".

I have chek that the table has a primary key and it has
Chira
 
Posts: 4
Joined: Fri Mar 11, 2011 9:06 am

Re: Update not working ?!?

Postby Moderator » Sun Mar 13, 2011 11:39 am

Would you please send DDL schema to the forum (or to support at pgoledb dot com)? What is the number of rows in the table? Are you using trial build, what is the build number?

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

Re: Update not working ?!?

Postby Chira » Sun Mar 13, 2011 11:27 pm

CREATE TABLE table1
(
id serial NOT NULL,
column1 character varying(50),
column2 boolean DEFAULT true,
CONSTRAINT tipdestinatie_pkey PRIMARY KEY (idtipdestinatie)
)

The table has 3 rows.
this is on a Postgresql v 8.1.22 on i686 redhat linux

I'm trying to update that table from a SQL Server 2008 x64 on Windows 2008 R2 using a trial build v1.3.0.2222

UPDATE OPENQUERY (LinkedServer, 'SELECT column1 FROM db.public.table1 where id=1')
SET column1 = 'cursuri'
Chira
 
Posts: 4
Joined: Fri Mar 11, 2011 9:06 am

Re: Update not working ?!?

Postby Moderator » Mon Mar 14, 2011 9:41 pm

The issue occurred because OPENQUERY (LinkedServer, 'SELECT column1 FROM db.public.table1 where id=1') results in a rowset without a primary key.

Please use the following alternative syntax:
Code: Select all
  UPDATE LinkedServer.db.public.table1
  SET column1 = 'cursuri'
  WHERE id=1
User avatar
Moderator
Site Admin
 
Posts: 301
Joined: Wed Oct 29, 2008 11:27 pm

Re: Update not working ?!?

Postby Chira » Mon Mar 21, 2011 2:14 am

It's working
but for the boolean column I get the same message

UPDATE LinkedServer.db.public.table1
SET column2 = 0
WHERE id=1
"Row cannot be located. Make sure the table has primary key."

How can I update a boolean column ?

Tks
Chira
 
Posts: 4
Joined: Fri Mar 11, 2011 9:06 am

Re: Update not working ?!?

Postby Moderator » Mon Mar 21, 2011 7:21 am

We have not been able to reproduce the error.

Can you reproduce the error each time?

Could you restart the SQL Server and see if the error still reproducible?
User avatar
Moderator
Site Admin
 
Posts: 301
Joined: Wed Oct 29, 2008 11:27 pm

Re: Update not working ?!?

Postby Chira » Mon Mar 21, 2011 11:06 pm

I've restart the SQL Server but Í get the same error. I get the error each time, I've tried several times
The Postgresql version is v 8.1.22 on i686 redhat linux
Chira
 
Posts: 4
Joined: Fri Mar 11, 2011 9:06 am

Re: Update not working ?!?

Postby Moderator » Tue Mar 22, 2011 10:43 pm

We are still not able to reproduce the issue yet. We are trying to reproduce with different numbers of columns and rows. If you could provide any additional details on how to reproduce it, it would be great. Have you tried with a different version of Postgresql (e.g. 8.4)?
User avatar
Moderator
Site Admin
 
Posts: 301
Joined: Wed Oct 29, 2008 11:27 pm


Return to Report Bugs

Who is online

Users browsing this forum: No registered users and 1 guest

cron