Thread: 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.