SEO

Hi all,

it seems as if ODBC provider doesn't work correctly in conjunction with MSDTC.
Transaction are handled correctly using pgadmin or an ODBC based client (e.g. WinSQL).

When running statements from MS SQL Server then in case of a rollback transaction a COMMIT is being forwarded to postgres (see log file below).

Any help is welcome.

TIA
KJ

example:
/* Commands run from SQL Server */
set XACT_ABORT ON
begin transaction
insert into OPENQUERY(LPGA,'select * from test.test where 1=0')
values (3,convert(varchar(20), getdate(),121),datepart(ms,getdate()))
select @@trancount, * from OPENQUERY(LPGA,'select * from test.test')
rollback tran
select @@trancount, * from OPENQUERY(LPGA,'select * from test.test')


/* Corrensponding pglog */
2007-04-26 15:44:16 LOG: statement: select oid, typbasetype from pg_type where typname = 'lo'
2007-04-26 15:44:16 LOG: statement: show max_identifier_length
2007-04-26 15:44:16 LOG: statement: select * from test.test where 1=0
2007-04-26 15:44:16 LOG: statement: select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid = 18288) inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum
2007-04-26 15:44:16 LOG: statement: select * from test.test
2007-04-26 15:44:16 LOG: statement: select * from test.test
2007-04-26 15:44:16 LOG: statement: select relhasoids, c.oid from pg_class c, pg_namespace n where relname = 'test' and nspname = 'test' and c.relnamespace = n.oid
2007-04-26 15:44:16 LOG: statement: select a.attname, a.atttypid from pg_index i, pg_attribute a where indrelid=18288 and indnatts=1 and indisunique and indexprs is null and indpred is null and i.indrelid = a.attrelid and a.attnum=i.indkey[0] and attnotnull and atttypid in (23, 26)
2007-04-26 15:44:16 LOG: statement: select * , "ctid", "a" from test.test where 1=0
2007-04-26 15:44:17 LOG: statement: BEGIN;insert into "test"."test" ("a", "b", "c") values (3, E'2007-04-26 15:44:17.', '0.000') returning ctid
2007-04-26 15:44:17 LOG: statement: select * , "ctid", "a" from test.test where ctid = '(0,4)'
2007-04-26 15:44:17 LOG: statement: COMMIT
2007-04-26 15:44:17 LOG: statement: select * from test.test
2007-04-26 15:44:17 LOG: statement: select * from test.test

--
"Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

KJ wrote:
> Hi all,
>
> it seems as if ODBC provider doesn't work correctly in conjunction with MSDTC.
> Transaction are handled correctly using pgadmin or an ODBC based client (e.g. WinSQL).
>
> When running statements from MS SQL Server then in case of a rollback transaction a COMMIT is being forwarded to postgres (see log file below).
>
> Any help is welcome.

Could you send me the Mylog output ?

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

I wrote:
> KJ wrote:
>> Hi all,
>>
>> it seems as if ODBC provider doesn't work correctly in conjunction with MSDTC.
>> Transaction are handled correctly using pgadmin or an ODBC based client (e.g. WinSQL).
>>
>> When running statements from MS SQL Server then in case of a rollback transaction a COMMIT is being forwarded to postgres (see log file below).
>>
>> Any help is welcome.
>
> Could you send me the Mylog output ?

Could you try first
begin distributed transaction
instead of
begin transaction
?

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster