ODBC problem with Microsoft Word and Excel

This is a multi-part message in MIME format.

------=_NextPart_000_00BD_01C75CCE.B222EC40
Content-Type: text/plain;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable

I=92ve loaded the most recent ODBC driver off the Postgresql FTP site.

=20

The DBMS is functioning, has other applications (libpq, etc) operating
perfectly, and the =93Test=94 functionality from the =93Data Sources=94 =
setup shows
that I can connect to the database desired when I declare the =
connection.

=20

However, when I attempt to go into either WORD (to use it as a mail =
merge
source) or EXCEL (just for grins to pull data from there) I connect to =
=93ODBC
DSN=94, click =93PostgreSQL30W=94 (the driver that was set up in the =
Data Sources
panel), I immediately get =93Unable to get list of tables from the data
source=94.

=20

What=92s going on?!

=20

-- Karl Denninger HYPERLINK =
"mailto:karl@denninger.net"karl@denninger.net

Diving? See HYPERLINK =
"http://www.scubaforum.org"http://www.scubaforum.org=20

=20


--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date: 3/1/2007
2:43 PM
=20

------=_NextPart_000_00BD_01C75CCE.B222EC40
Content-Type: text/html;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable




charset=3Dwindows-1250">

--

No virus found in this outgoing message.

Checked by AVG Free Edition.

Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date: 3/1/2007 =
2:43 PM

Re: ODBC problem with Microsoft Word and Excel

This is a multi-part message in MIME format.

------=_NextPart_000_017D_01C75CD6.FEADACA0
Content-Type: text/plain;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable

Karl Denninger found that:

> when I attempt to go into either WORD (to use it as a mail merge =
source) or EXCEL (just for grins to pull data from there) I >connect to =
=93ODBC DSN=94, click =93PostgreSQL30W=94 (the driver that was set up in =
the Data Sources panel), I immediately get >=93Unable to get list of =
tables from the data source=94.

This might help: =
http://archives.postgresql.org/pgsql-odbc/2004-12/msg00054.p hp

Regards,
George

------=_NextPart_000_017D_01C75CD6.FEADACA0
Content-Type: text/html;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable



charset=3Dwindows-1250">



background=3D"">

Re: ODBC problem with Microsoft Word and Excel

> This might help: http://archives.postgresql.org/pgsql-odbc/2004-12/msg0=
0054.php


The attached link refers to .udl(s). UDL(s) only work with OLE-DB driver=
s not ODBC drivers,
correct? In this case the posters is trying to get the ODBC driver to wo=
rk correctly.

Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: ODBC problem with Microsoft Word and Excel

Correct.

The part I don't understand is that I enabled logging and it appears to be
returning the table list! Immediately after I attempt the connect from
either EXCEL or WORD I pulled the logfiles.

The ODBC logfile contains the following:

conn=3D02B03A90, PGAPI_DriverConnect( in)=3D'DSN=3DPostgreSQL30W;',
fDriverCompletion=3D3
DSN info:
DSN=3D'PostgreSQL30W',server=3D'genesis.denninger.net',port= 3D'5432',dbase=
=3D'
test',user=3D'karl',passwd=3D'xxxxx'
onlyread=3D'0',protocol=3D'7.4',showoid=3D'0',fakeoidindex=3 D'0',showsystab=
le=3D
'0'
conn_settings=3D'',conn_encoding=3D'OTHER'
translation_dll=3D'',translation_option=3D''
Global Options: Version=3D'08.02.0200', fetch=3D100, socket=3D4096,
unknown_sizes=3D0, m
ax_varchar_size=3D254, max_longvarchar_size=3D8190
disable_optimizer=3D1, ksqo=3D1, unique_index=3D1,
use_declarefetch=3D0
text_as_longvarchar=3D1, unknowns_as_longvarchar=3D0,
bools_as_char=3D
1 NAMEDATALEN=3D64
extra_systable_prefixes=3D'dd_;', conn_settings=3D''
conn_encoding=3D'
OTHER'
[ PostgreSQL version string =3D '8.2.3' ]
[ PostgreSQL version number =3D '8.2' ]
conn=3D02B03A90, query=3D'select oid, typbasetype from pg_type where typnam=
e =3D
'lo''
[ fetched 0 rows ]
[ Large Object oid =3D -999 ]
[ Client encoding =3D 'UTF8' (code =3D 6) ]
conn=3D02B03A90,
PGAPI_DriverConnect(out)=3D'DSN=3DPostgreSQL30W;DATABASE=3Dt est;SERVER
=3Dgenesis.denninger.net;PORT=3D5432;UID=3Dkarl;PWD=3Dxxxxxx xxxxx;SSLmode=
=3Ddisable;Re
adOn
ly=3D0;Protocol=3D7.4-1;FakeOidIndex=3D0;ShowOidColumn=3D0;R owVersioning=3D=
0;ShowSyste
mTab
les=3D0;ConnSettings=3D;Fetch=3D100;Socket=3D4096;UnknownSiz es=3D0;MaxVarch=
arSize=3D254;
MaxL
ongVarcharSize=3D8190;Debug=3D1;CommLog=3D1;Optimizer=3D1;Ks qo=3D1;UseDecla=
reFetch=3D0;T
extA
sLongVarchar=3D1;UnknownsAsLongVarchar=3D0;BoolsAsChar=3D1;P arse=3D0;Cancel=
AsFreeStm
t=3D0;
ExtraSysTablePrefixes=3Ddd_;;LFConversion=3D1;UpdatableCurso rs=3D1;Disallow=
Prematu
re=3D0
;TrueIsMinus1=3D0;BI=3D0;ByteaAsLongVarBinary=3D0;UseServerS idePrepare=3D0;=
LowerCase
Iden
tifier=3D0;XaOpt=3D1'
conn=3D02B03A90, query=3D'select relname, nspname, relkind from
pg_catalog.pg_class
c, pg_catalog.pg_namespace n where relkind in ('r', 'v') and nspname not in
('pg
_catalog', 'information_schema', 'pg_toast', 'pg_temp_1') and n.oid =3D
relnamespa
ce order by nspname, relname'
[ fetched 14 rows ]
conn=3D02B03A90, PGAPI_Disconnect


There are indeed 14 rows (tables), and the debug output says there were 14
rows fetched!

The query is also ok; I ran it by hand on the server, and got the list of
tables cleanly with no diagnostics.

What's going on?

Same error out of either Excel or Word (mailmerge setup)

-- Karl Denninger karl@denninger.net
Diving? See http://www.scubaforum.org=20

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Richard Broersma Jr
Sent: Friday, March 02, 2007 2:40 PM
To: George Weaver; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] ODBC problem with Microsoft Word and Excel

> This might help:
http://archives.postgresql.org/pgsql-odbc/2004-12/msg00054.p hp


The attached link refers to .udl(s). UDL(s) only work with OLE-DB drivers
not ODBC drivers,
correct? In this case the posters is trying to get the ODBC driver to work
correctly.

Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org


%SPAMBLOCK-SYS: Matched [hub.org+], message ok


--=20
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date: 3/1/2007
2:43 PM

--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date: 3/1/2007
2:43 PM
=20



%SPAMBLOCK-SYS: Matched [@yahoo.com+], message ok

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: ODBC problem with Microsoft Word and Excel

But Richard Broersma Jr noted:

> The attached link refers to .udl(s). UDL(s) only work with OLE-DB drivers
> not ODBC drivers,
> correct? In this case the posters is trying to get the ODBC driver to
> work correctly.

I don't disagree that it would be desirable to be able to get Office
products to "play nice" with the PostgreSQL ODBC driver (or vice versa), if
that's the OP's objective. On the other hand, if his objective is to pull
data into Word or Excel for the purposes he mentioned (the ends rather than
the means), the .udl approach works.

Regards,
George



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: ODBC problem with Microsoft Word and Excel

I remember having problems with this. What I did was to move my query to
a pqsql function, and then pass Word the ODBC DSN with a SQL query of
"SELECT * FROM mypgfunction(parameter)". I also ran into a problem where
Word would see the column names correctly, and the row count would be
correct, but all the data fields were blank, I switched to using the
ANSI driver and that resolved it. Take a look at the following VBA code
snipet:

Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim oMM As Word.mailmerge

'Start Word
Set oWord =3D CreateObject("Word.Application")
oWord.Visible =3D True

' create new document
Set oMM =3D oWord.Documents.Add.mailmerge
End If

oMM.MainDocumentType =3D wdFormLetters
oMM.OpenDataSource Name:=3D"", Connection:=3D"DSN=3DDSNName",
SQLStatement:=3D"SELECT * FROM mypgfunction(stuff)"

oMM.ShowWizard 1
oMM.EditMainDocument

Karl Denninger wrote:
>
> I=92ve loaded the most recent ODBC driver off the Postgresql FTP site.
>
> =20
>
> The DBMS is functioning, has other applications (libpq, etc) operating=20
> perfectly, and the =93Test=94 functionality from the =93Data Sources=94=
setup=20
> shows that I can connect to the database desired when I declare the=20
> connection.
>
> =20
>
> However, when I attempt to go into either WORD (to use it as a mail=20
> merge source) or EXCEL (just for grins to pull data from there) I=20
> connect to =93ODBC DSN=94, click =93PostgreSQL30W=94 (the driver that w=
as set=20
> up in the Data Sources panel), I immediately get =93Unable to get list=20
> of tables from the data source=94.
>
> =20
>
> What=92s going on?!
>
> =20
>
> -- Karl Denninger karl@denninger.net
>
> Diving? See http://www.scubaforum.org
>
> =20
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date:=20
> 3/1/2007 2:43 PM
>



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

Re: ODBC problem with Microsoft Word and Excel

This is a multi-part message in MIME format.

------=_NextPart_000_0042_01C75F07.6AE68310
Content-Type: text/plain;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable

Original Message From "Karl Denninger"=20

>The part I don't understand is that I enabled logging and it appears to =
be
>returning the table list! Immediately after I attempt the connect from
>either EXCEL or WORD I pulled the logfiles.

I don't think the problem resides on the PostgreSQL side of things but =
the Office side. From my experience and from what I can gather Office =
needs a separate file (.udl .odc, etc) in order to display the results =
properly.

The discussion here might provide some further insight (first topic - =
Subject: Re: Mail Merge to SQL Server without using an external ODC):

http://www.microsoft.com/communities/newsgroups/list/en-us/d efault.aspx?q=
uery=3Ddsn+less&dg=3Dmicrosoft.public.word.mailmerge.fields& cat=3Den_us_e=
e88ce09-9e34-4b0c-8aba-2e17252d39bc&lang=3Den&cr=3Dus&pt=3D& catlist=3D&dg=
list=3D&ptlist=3D&exp=3D&sloc=3Den-us

HTH
George

"When you see a new trail, or a footprint you do not know,
follow it to the point of knowing."
- Uncheedah, Grandmother of Ohiyesa

------=_NextPart_000_0042_01C75F07.6AE68310
Content-Type: text/html;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable



charset=3Dwindows-1250">

Re: ODBC problem with Microsoft Word and Excel

This is a multi-part message in MIME format.

------=_NextPart_000_0254_01C75F08.FD8075E0
Content-Type: text/plain;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable

Interesting.

=20

If I create a blank =93.UDL=94 file I can then stick in that and it =
appears to
work, but its SEVERELY limited.

=20

I can=92t get at anything from Word EXCEPT for the single database that =
I
defined in the SQL setup in =93Data Sources=94 =96 and I can only have =
one on the
machine! This is a potential problem of course…… there=92s a lot =
more than
one database on the SQL server=85..

=20

But =96 it DOES appear to work for mail merge this way =96 its just =
kludgy as
heck.

=20

-- Karl Denninger HYPERLINK =
"mailto:karl@denninger.net"karl@denninger.net

Diving? See HYPERLINK =
"http://www.scubaforum.org"http://www.scubaforum.org=20

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of George Weaver
Sent: Monday, March 05, 2007 9:20 AM
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] ODBC problem with Microsoft Word and Excel

=20

Original Message From "Karl Denninger"=20

>The part I don't understand is that I enabled logging and it appears to =
be
>returning the table list! Immediately after I attempt the connect from
>either EXCEL or WORD I pulled the logfiles.

=20

I don't think the problem resides on the PostgreSQL side of things but =
the
Office side. From my experience and from what I can gather Office needs =
a
separate file (.udl .odc, etc) in order to display the results properly.

=20

The discussion here might provide some further insight (first topic -
Subject: Re: Mail Merge to SQL Server without using an external ODC):

=20

HYPERLINK
"http://www.microsoft.com/communities/newsgroups/list/en-us/ default.aspx?=
que
ry=3Ddsn+less&dg=3Dmicrosoft.public.word.mailmerge.fields&ca t=3Den_us_ee8=
8ce09-9e3
4-4b0c-8aba-2e17252d39bc&lang=3Den&cr=3Dus&pt=3D&catlist=3D& dglist=3D&ptl=
ist=3D&exp=3D&slo
c=3Den-us"http://www.microsoft.com/communities/newsgroups/li st/en-us/defa=
ult.a
spx?query=3Ddsn+less&dg=3Dmicrosoft.public.word.mailmerge.fi elds&cat=3Den=
_us_ee88c
e09-9e34-4b0c-8aba-2e17252d39bc&lang=3Den&cr=3Dus&pt=3D&catl ist=3D&dglist=
=3D&ptlist=3D&e
xp=3D&sloc=3Den-us

=20

HTH

George

=20

"When you see a new trail, or a footprint you do not know,
follow it to the point of knowing."
- Uncheedah, Grandmother of Ohiyesa

=20


--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.7/710 - Release Date: 3/4/2007
1:58 PM
=20

------=_NextPart_000_0254_01C75F08.FD8075E0
Content-Type: text/html;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable





charset=3Dwindows-1250">

--

No virus found in this outgoing message.

Checked by AVG Free Edition.

Version: 7.5.446 / Virus Database: 268.18.7/710 - Release Date: 3/4/2007 =
1:58 PM

Re: ODBC problem with Microsoft Word and Excel

Karl Denninger wrote:

> I=92ve loaded the most recent ODBC driver off the Postgresql FTP site.

Could you try the snapshot driver at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

regards,
Hiroshi Inoue


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly