Re: Datatype misrepresentation DTS with SQL Server

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 08 February 2002 19:08
> To: Matt Wedgwood
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Datatype misrepresentation DTS with SQL Server
>
>
> "Matt Wedgwood" <mwedgwood@TONYSANCHEZ.com> writes:
> > I'm trying to use the "Import and Export Data" (DTS) tool included
> > with MS SQL Server to push data to a PostgreSQL 7.1.3
> database via an
> > ODBC connection. Everything works great, except if I try to use the
> > bigint/int8 data type for columns on the PostgreSQL side. DTS
> > incorrectly sees the int8 datatype as "char" and therefore ties to
> > shove a space-padded representation of the integer into the table.
>
> You could try hacking the source code of the PG ODBC driver.
> I note this in pgtypes.c:
>
>             /* Change this to SQL_BIGINT for ODBC v3 bjm 2001-01-23 */
>         case PG_TYPE_INT8:
>             return SQL_CHAR;
>
> Don't know whether DTS will actually recognize SQL_BIGINT,
> but it's worth a try.  SQL_NUMERIC could be worth trying too,
> if BIGINT fails.

We discussed this in some detail last year - Bruce's comment (and the code)
is technically correct. Int8 should return SQL_CHAR for ODBC < v3 and
SQL_BIGINT for >= v3.

My guess is that DTS is assuming that the driver is v3 compliant, at least
wrt the datatypes. As Tom suggests, try changing the driver to return
SQL_BIGINT, thought this will definitely break other int8/bigint code based
on MS ADO (including pgAdmin if you use it) - not a major issue if you don't
have any!

Regards, Dave.