Обсуждение: Datatype misrepresentation DTS with SQL Server
(I sincerely apologize if this is has been covered here - I didn't have much luck searching the archives)
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. PostgreSQL doesn't like it.
The integer/int4 datatype doesn't cause this problem, but I will be inserting more rows than the int4 datatype can count (I want a bigint primary key).
I don't know any way around this problem, and DTS is kind of a requirement for the project.
Thanks in advance for any help you can provide.
-MW
"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.
regards, tom lane
Thanks, Tom. That looks promising, but unfortunately I don't have the
tools to recompile. I guess there's no magic configuration file that
would allow me to override this built-in type-mapping, eh?
Any chance someone else has run into this problem found a workaround
that doesn't involve customizing the code? I'd be satisfied with
something kludgy, as long as it could be reliably automated. <g>
-MW
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, February 08, 2002 1:08 PM
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.
regards, tom lane
> -----Original Message----- > From: Matt Wedgwood [mailto:mwedgwood@TONYSANCHEZ.com] > Sent: 08 February 2002 20:02 > To: Tom Lane > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Datatype misrepresentation DTS with SQL Server > > > Thanks, Tom. That looks promising, but unfortunately I don't > have the tools to recompile. I guess there's no magic > configuration file that would allow me to override this > built-in type-mapping, eh? > > Any chance someone else has run into this problem found a > workaround that doesn't involve customizing the code? I'd be > satisfied with something kludgy, as long as it could be > reliably automated. <g> > Hacked version sent privately for testing... If it works I'll add a note to the ODBC website about it. Regards, Dave.
> -----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.
The code change (SQL_CHAR -> SQL_BIGINT) worked! Thanks for the help. -MW -----Original Message----- From: Dave Page [mailto:dpage@vale-housing.co.uk] Sent: Saturday, February 09, 2002 5:14 AM To: Matt Wedgwood; Tom Lane Cc: pgsql-odbc@postgresql.org Subject: RE: [ODBC] Datatype misrepresentation DTS with SQL Server > -----Original Message----- > From: Matt Wedgwood [mailto:mwedgwood@TONYSANCHEZ.com] > Sent: 08 February 2002 20:02 > To: Tom Lane > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Datatype misrepresentation DTS with SQL Server > > > Thanks, Tom. That looks promising, but unfortunately I don't > have the tools to recompile. I guess there's no magic > configuration file that would allow me to override this > built-in type-mapping, eh? > > Any chance someone else has run into this problem found a > workaround that doesn't involve customizing the code? I'd be > satisfied with something kludgy, as long as it could be > reliably automated. <g> > Hacked version sent privately for testing... If it works I'll add a note to the ODBC website about it. Regards, Dave.