Insert Into failing with Serial Column

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

Insert Into failing with Serial Column

Postby floppycow » Fri Jan 07, 2011 10:36 am

We are testing out the PGNP (PGNP-1.3.0.2217) from an SQL 2005 against a PostgresSQL 8.1.13. The testing is going very smoothly for the Update, Delete, and Select commands. The Insert commands work correctly when running against a table that does not have a serial column. We get the follow error when attempting to insert a record to a table that has a serial column (primary key).
SQL Statement:
Code: Select all
INSERT INTO [PGSample].[ittest2].[public].[testtable]
           ([firstname],[lastname])
     VALUES
           ('Thomas','James')

Error Output:
Code: Select all
OLE DB provider "PGNP" for linked server "PGSample" returned message "ERROR:  syntax error at or near "RETURNING"
LINE 1: ...testtable"("firstname","lastname") VALUES ($1,$2) RETURNING ...
                                                             ^
".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "PGNP" for linked server "PGSample" could not INSERT INTO table "[PGSample].[ittest2].[public].[testtable]".

I have tried several variations with the Extended Attributes: SQLSYNTAX.

Tom
-----------
Thomas Burl
floppycow
 
Posts: 1
Joined: Fri Jan 07, 2011 10:22 am

Re: Insert Into failing with Serial Column

Postby Moderator » Sun Jan 09, 2011 10:18 pm

The issue with RETURNING clause occurs because the syntax is available only in Postgres 8.2 and later. We can add support for previous versions of Postgres but it may take couple of weeks.
User avatar
Moderator
Site Admin
 
Posts: 302
Joined: Wed Oct 29, 2008 11:27 pm


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

Who is online

Users browsing this forum: No registered users and 1 guest

cron