Linking MSSQL to Postgresq

Hi,

I've been experimenting conecting SQLserver to postgresql using "linked
servers". However I get errors when running a query. I've setup an ODBC
data source to point to the postgresql db. From SQL enterprise manager I
can see the linked server and tables ok.

Using the Borland SQL exlorer everthing works great. When trying to connect
via Query Analyser I receive errors. Below are listed the results and the
ODBC log file from running the same query in SQL explorer and Query
Analyser. BTW everything also works ok from MSaccess, so I guess it could
be something related to the OLE->ODBC interface.

Hope that this is of interest.
Regards,
Ian

Borland SQL explorer - works ok
--------------------
SELECT OID,DATNAME from pg_database

Log file
--------

conn = 50099040, PGAPI_Connect(DSN='PostgreSQL', UID='user', PWD='pass')
Global Options: Version='07.02.0001', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1
extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=50099040, query=' '
conn=50099040, query='set DateStyle to 'ISO''
conn=50099040, query='set geqo to 'OFF''
conn=50099040, query='set ksqo to 'ON''
conn=50099040, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=50099040, query='select version()'
[ fetched 1 rows ]
[ PostgreSQL version string = 'PostgreSQL 7.2.1 on i686-pc-linux-gnu,
compiled by GCC 2.96' ]
[ PostgreSQL version number = '7.2' ]
conn=50099040, query='select pg_client_encoding()'
[ fetched 1 rows ]
[ Client encoding = 'SQL_ASCII' (code = 0) ]
conn=50099040, query='select oid,datname from pg_database
'
[ fetched 3 rows ]
conn=50099040, PGAPI_Disconnect

MS Query Analyser via linked server using OLE->ODBC
--------------
qry1:

select *
from openquery (postgresql,'select oid,datname from pg_database')

Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned an unexpected data length for the
fixed-length column '[MSDASQL].datname'. The expected data length is 32,
while the returned data length is 10.

qry2:
select oid,datname
from postgresql...pg_database

Server: Msg 7318, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned an invalid column definition.

Log file
--------
Global Options: Version='07.02.0001', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1
extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=268792448, query=' '
conn=268792448, query='set DateStyle to 'ISO''
conn=268792448, query='set geqo to 'OFF''
conn=268792448, query='set ksqo to 'ON''
conn=268792448, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=268792448, query='select version()'
[ fetched 1 rows ]
[ PostgreSQL version string = 'PostgreSQL 7.2.1 on i686-pc-linux-gnu,
compiled by GCC 2.96' ]
[ PostgreSQL version number = '7.2' ]
conn=268792448, query='select pg_client_encoding()'
[ fetched 1 rows ]
[ Client encoding = 'SQL_ASCII' (code = 0) ]
conn=268792448,
PGAPI_DriverConnect(out)='DSN=postgresql;DATABASE=template1;SERVER=129.200.201.28;PORT=5432;UID=user;PWD=pass;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=1;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=1;Optimizer=1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0'
CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=1209', errnum=205,
errmsg='Unknown connect option (Get)'
------------------------------------------------------------
henv=268830976, conn=268792448, status=1, num_stmts=16
sock=268830912, stmts=268830832, lobj_type=-999
---------------- Socket Info -------------------------------
socket=1176, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=268803400, buffer_out=268807504
buffer_filled_in=63, buffer_filled_out=0, buffer_read_in=63
conn=268792448, query='select relname, usename, relkind from pg_class,
pg_user where relkind in ('r', 'v') and relname like 'pg\_database' and
usesysid = relowner order by relname'
[ fetched 1 rows ]
conn=268792448, query='select u.usename, c.relname, a.attname, a.atttypid,
t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules from
pg_user u, pg_class c, pg_attribute a, pg_type t where u.usesysid =
c.relowner and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)
and c.relname like 'pg\\_database' order by c.relname, attnum'
[ fetched 9 rows ]
PGAPI_Columns: table='pg_database',field_name='datname',type=19,name='name'
PGAPI_Columns: table='pg_database',field_name='datdba',type=23,name='int4'
PGAPI_Columns: table='pg_database',field_name='encoding',type=23,name='int4'
PGAPI_Columns:
table='pg_database',field_name='datistemplate',type=16,name='bool'
PGAPI_Columns:
table='pg_database',field_name='datallowconn',type=16,name='bool'
PGAPI_Columns:
table='pg_database',field_name='datlastsysoid',type=26,name='oid'
PGAPI_Columns:
table='pg_database',field_name='datvacuumxid',type=28,name='xid'
PGAPI_Columns:
table='pg_database',field_name='datfrozenxid',type=28,name='xid'
PGAPI_Columns: table='pg_database',field_name='datpath',type=25,name='text'
CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=1209', errnum=205,
errmsg='Unknown connect option (Get)'
------------------------------------------------------------
henv=268830976, conn=268792448, status=1, num_stmts=16
sock=268830912, stmts=268830832, lobj_type=-999
---------------- Socket Info -------------------------------
socket=1176, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=268803400, buffer_out=268807504
buffer_filled_in=962, buffer_filled_out=0, buffer_read_in=962
CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=1209', errnum=205,
errmsg='Unknown connect option (Get)'
------------------------------------------------------------
henv=268830976, conn=268792448, status=1, num_stmts=16
sock=268830912, stmts=268830832, lobj_type=-999
---------------- Socket Info -------------------------------
socket=1176, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=268803400, buffer_out=268807504
buffer_filled_in=962, buffer_filled_out=0, buffer_read_in=962
conn=268792448, query='select oid,datname from pg_database'
[ fetched 3 rows ]
CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=1209', errnum=205,
errmsg='Unknown connect option (Get)'
------------------------------------------------------------
henv=268830976, conn=268792448, status=1, num_stmts=16
sock=268830912, stmts=268830832, lobj_type=-999
---------------- Socket Info -------------------------------
socket=1176, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=268803400, buffer_out=268807504
buffer_filled_in=120, buffer_filled_out=0, buffer_read_in=120
conn=268792448, query='select oid,datname from pg_database'
[ fetched 3 rows ]

_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com