Unable to insert into an empty table on PG.

[jwrascoe]

Hi Everyone,

I am new to this product and have found that I am unable to insert into a empty PG table.. but if I just put 1 record in (via postgres) all works fine.

Here is my simple table... on PG

CREATE TABLE voices
(
voice_id serial NOT NULL,
name text NOT NULL,
CONSTRAINT voices_pkey PRIMARY KEY (voice_id ),
CONSTRAINT voices_name_key UNIQUE (name )
)

Then if I run this statement via SQL Server.. it fails only if the target table is empty.
insert into [devx05]...voices (name) values ('Inserted from SQL Server')

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "PGNP" for linked server "devx05" reported an error. Provider caused a server fault in an external process.
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "PGNP" for linked server "devx05" could not INSERT INTO table "[devx05]...[voices]". Unknown provider error.

I am running on the trial that I downloaded today...
Also.. I run SQL Server x64 linked to PostgreSQL 9.1.3 on amd64-portbld-freebsd8.3, compiled by cc (GCC) 4.2.2 20070831 prerelease [FreeBSD], 64-bit

Any idea why? Everything else seems to work fine.. but I will be testing more.

Thanks for your help.

Jim

[jwrascoe]

Update:

I downloaded in the archive section... and all works just fine!
PGNP-1.3.0.2256.exe 2554241 11815 Download
The latest release of PostgreSQL OLEDB Provider for 32 and 64-bit applications.

It must be due to the code thats in the trial download.

Think I am all set.

Jim

[Moderator]

Just wanted to mention that setting "Allow inprocess" property to 1 resolves this and several other issues when working with linked servers.

Thank you!