Skip site navigation (1) Skip section navigation (2)

Linking MSSQL to Postgresq

From: "Ian Crick" <id_crick(at)hotmail(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Linking MSSQL to Postgresq
Date: 2002-08-22 14:40:40
Message-ID: F18830kkZIhTrBoe2U8000001b7@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-odbc
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


pgsql-odbc by date

Next:From: Tulk SteveDate: 2002-08-22 20:19:22
Subject: Re: ODBC with Visio - what's wrong with my connection string?
Previous:From: Antti ToivanenDate: 2002-08-21 09:54:11
Subject: VS: Problem with Access 2002 and Postgres ODBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group