DBLink from SQL Server to postgresql: get sequence nextval

Question

I've got a SQL Server 2008 that needs to connect to a POSTGRESQL 9.0 server.

One of the tasks that i need is to get the next value of a sequence over the POSTGRESQL

Example 1 :

execute ('select nextval( ''cadastro.pessoa_seq'' )' ) at POSTGRESQL ;

error:

Msg 7215, Level 17, State 1, Line 1 Could not execute statement on remote server 'POSTGRESQL'.*

Example 2 :

select * from openquery( POSTGRESQL ,'select nextval( ''cadastro.pessoa_seq'' )')

error:

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "POSTGRESQL" reported an error. The provider reported an unexpected catastrophic failure. Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "POSTGRESQL".*

None of these options works!

Is there any good alternative ?

Thank you in advance

Answer 1

dealing with ODBC sources and postgres I have to do this:

nextval(''cadastro.pessoa_seq''::regclass) for it to work 

Answer 2

Perhaps your Postgresql connection is readonly , have you tried to execute an update/insert request ?

nextval update the sequence , try:

currval(''cadastro.pessoa_seq''::regclass)