Thread: MSSQL To PostgreSQL Via DTS
Does anyone have any advice on copying columns of data type text or image from Microsoft SQL 2000 to Postgre? I have set the ODBC driver to treat text as Long VarChar, and if I bump my max Long VarChar size up – I get one of two errors:
MSSQL Error: Data for source column 1 ('textfield') is too large for the specified buffer size.
or
MSSQL Error: Query-based insertion or updating of BLOB values is not supported.
And I can’t seem to make any sense of the cause of these errors. I sent a previous request to this list last Thursday with some mylog dumps… was wondering if there’s anything more I can provide to this list to help find a solution or lead to a possible bug.
Thanks,
Daniel Holm
Daniel Holm wrote:
> Does anyone have any advice on copying columns of data type text or
> image from Microsoft SQL 2000 to Postgre? I have set the ODBC driver to
> treat text as Long VarChar, and if I bump my max Long VarChar size up -
> I get one of two errors:
>
>
>
> MSSQL Error: Data for source column 1 ('textfield') is too large for the
> specified buffer size.
>
> or
>
> MSSQL Error: Query-based insertion or updating of BLOB values is not
> supported.
Please try psqlODBC 07.03.0260(Experimental Enhanced Branch) at
http://pgfoundry.org/projects/psqlodbc/ .
regards,
Hiroshi Inoue
No luck with the experimental driver either, 2 rows with text fields,
sizes: 80804 and 63456 bytes.
Default settings on ODBC+ driver (max length 8000 or so) result in:
> MSSQL Error: Data for source column 1 ('textfield') is too large for
the
> specified buffer size.
After modifying max length, I get various errors such as catastrophic
failure, a serious error occurred while connecting to the provider,
provider not initialized, etc. I cant get anything to work on the ODBC
driver now. I will reboot the machine and try it again later this
evening.
-----Original Message-----
From: Hiroshi Inoue [mailto:inoue@tpf.co.jp]
Sent: Wednesday, March 08, 2006 8:30 AM
To: Daniel Holm
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] MSSQL To PostgreSQL Via DTS
Daniel Holm wrote:
> Does anyone have any advice on copying columns of data type text or
> image from Microsoft SQL 2000 to Postgre? I have set the ODBC driver
to
> treat text as Long VarChar, and if I bump my max Long VarChar size up
-
> I get one of two errors:
>
>
>
> MSSQL Error: Data for source column 1 ('textfield') is too large for
the
> specified buffer size.
>
> or
>
> MSSQL Error: Query-based insertion or updating of BLOB values is not
> supported.
Please try psqlODBC 07.03.0260(Experimental Enhanced Branch) at
http://pgfoundry.org/projects/psqlodbc/ .
regards,
Hiroshi Inoue
Daniel Holm wrote:
> No luck with the experimental driver either, 2 rows with text fields,
> sizes: 80804 and 63456 bytes.
> Default settings on ODBC+ driver (max length 8000 or so) result in:
>
>>MSSQL Error: Data for source column 1 ('textfield') is too large for
>
> the
>
>>specified buffer size.
>
>
> After modifying max length, I get various errors
Were you able to connect to the database before the modification using
the experimantal driver ? Are you using the different DSN from the
one which uses the official driver ?
> such as catastrophic
> failure, a serious error occurred while connecting to the provider,
> provider not initialized, etc. I cant get anything to work on the ODBC
> driver now. I will reboot the machine and try it again later this
> evening.
Please replace the dll psqlodbc35w.dll by the one at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
and send the mylog output to me.
regards,
Hiroshi Inoue
>Daniel Holm wrote:
>> Does anyone have any advice on copying columns of data type text or
>> image from Microsoft SQL 2000 to Postgre? I have set the ODBC driver
to
>> treat text as Long VarChar, and if I bump my max Long VarChar size up
-
>> I get one of two errors:
>>
>>
>>
>> MSSQL Error: Data for source column 1 ('textfield') is too large for
the
>> specified buffer size.
>>
>> or
>>
>> MSSQL Error: Query-based insertion or updating of BLOB values is not
>> supported.
>
>Please try psqlODBC 07.03.0260(Experimental Enhanced Branch) at
> http://pgfoundry.org/projects/psqlodbc/ .
>
>regards,
>Hiroshi Inoue
Attached are three zip files:
mylog_3476 - trial run 1, regular installed experimental driver
Previous call was not SQLPutData or SQLParamData
mylog_5140 - trial run 2, replaced experimental DLL with yours
Code execution exception: EXCEPTION_ACCESS_VIOLATION
mylog_6056 - trial run 3, removed experimental driver, rebooted, moved
your DLL into /system/ and ran the registry file.
Code execution exception: EXCEPTION_ACCESS_VIOLATION
Any ideas?
Attachment
Hi Daniel, Thanks for your report. I seem to have found the cause of the exception. Please retry the dll at http://www.geocities.jp/inocchichichi/psqlodbc/index.html. regards, Hiroshi Inoue Daniel Holm wrote: >>Daniel Holm wrote: >> >>>Does anyone have any advice on copying columns of data type text or >>>image from Microsoft SQL 2000 to Postgre? I have set the ODBC driver > > to > >>>treat text as Long VarChar, and if I bump my max Long VarChar size up > > - > >>>I get one of two errors: >>> >>> >>> >>>MSSQL Error: Data for source column 1 ('textfield') is too large for > > the > >>>specified buffer size. >>> >>>or >>> >>>MSSQL Error: Query-based insertion or updating of BLOB values is not >>>supported. >> >>Please try psqlODBC 07.03.0260(Experimental Enhanced Branch) at >> http://pgfoundry.org/projects/psqlodbc/ . >> >>regards, >>Hiroshi Inoue > > > Attached are three zip files: > mylog_3476 - trial run 1, regular installed experimental driver > Previous call was not SQLPutData or SQLParamData > > mylog_5140 - trial run 2, replaced experimental DLL with yours > Code execution exception: EXCEPTION_ACCESS_VIOLATION > > mylog_6056 - trial run 3, removed experimental driver, rebooted, moved > your DLL into /system/ and ran the registry file. > Code execution exception: EXCEPTION_ACCESS_VIOLATION > > Any ideas?
Attached is the new mylog and psqlodbc log for the 07.03.0261 DLL. I received the same EXCEPTION_ACCESS_VIOLATION :( This DLL seems to work fine with regular data types (varchar, int) Thanks for your help so far, -Daniel Holm -----Original Message----- Hi Daniel, Thanks for your report. I seem to have found the cause of the exception. Please retry the dll at http://www.geocities.jp/inocchichichi/psqlodbc/index.html. regards, Hiroshi Inoue Daniel Holm wrote: >>Daniel Holm wrote: >> >>>Does anyone have any advice on copying columns of data type text or >>>image from Microsoft SQL 2000 to Postgre? I have set the ODBC driver > > to > >>>treat text as Long VarChar, and if I bump my max Long VarChar size up > > - > >>>I get one of two errors: >>> >>> >>> >>>MSSQL Error: Data for source column 1 ('textfield') is too large for > > the > >>>specified buffer size. >>> >>>or >>> >>>MSSQL Error: Query-based insertion or updating of BLOB values is not >>>supported. >> >>Please try psqlODBC 07.03.0260(Experimental Enhanced Branch) at >> http://pgfoundry.org/projects/psqlodbc/ . >> >>regards, >>Hiroshi Inoue > > > Attached are three zip files: > mylog_3476 - trial run 1, regular installed experimental driver > Previous call was not SQLPutData or SQLParamData > > mylog_5140 - trial run 2, replaced experimental DLL with yours > Code execution exception: EXCEPTION_ACCESS_VIOLATION > > mylog_6056 - trial run 3, removed experimental driver, rebooted, moved > your DLL into /system/ and ran the registry file. > Code execution exception: EXCEPTION_ACCESS_VIOLATION > > Any ideas?
Attachment
From: "Daniel Holm" > Attached is the new mylog and psqlodbc log for the 07.03.0261 DLL. > > I received the same EXCEPTION_ACCESS_VIOLATION :( > > This DLL seems to work fine with regular data types (varchar, int) Though it is necessary to pursue, does the following definition help you? CREATE DOMAIN lo AS int4; Regards, Hiroshi Saito
Yes, that did the trick for using this driver for my "Text" columns. Thanks! Do you have any idea on how I can transport MSSQL type Image (just bytes internally) to a bytea column in Postgre? I'm willing to change types around. Thanks, Daniel Holm -----Original Message----- From: Hiroshi Saito [mailto:saito@inetrt.skcapi.co.jp] -----Original Message----- From: "Daniel Holm" > Attached is the new mylog and psqlodbc log for the 07.03.0261 DLL. > > I received the same EXCEPTION_ACCESS_VIOLATION :( > > This DLL seems to work fine with regular data types (varchar, int) Though it is necessary to pursue, does the following definition help you? CREATE DOMAIN lo AS int4; Regards, Hiroshi Saito
Daniel Holm wrote: > Yes, that did the trick for using this driver for my "Text" columns. Hmm, it seems strange. LO has nothing to do with the text field. Could you send me the mylog output when the driver works ? > Thanks! > > Do you have any idea on how I can transport MSSQL type Image (just bytes > internally) to a bytea column in Postgre? I'm willing to change types > around. You can turn on the *bytea as LO* option. regards, Hiroshi Inoue
Hiroshi Inoue wrote: >Hmm, it seems strange. LO has nothing to do with the text field. >Could you send me the mylog output when the driver works ? It's 500K, you can download at http://www.interworksinc.com/daniel/mylog_2288.zip >You can turn on the *bytea as LO* option. Yes, that's working great! Thank you. -Daniel Holm
Daniel Holm wrote: > Hiroshi Inoue wrote: > >>Hmm, it seems strange. LO has nothing to do with the text field. >>Could you send me the mylog output when the driver works ? > > > It's 500K, you can download at > http://www.interworksinc.com/daniel/mylog_2288.zip There seems no strangeness in the log and the existence of lo type seems irrelevant to the result. regards, Hiroshi Inoue