Thread: pgsql ODBC 8.1.200 MSSQL Migration Errors
I am converting a MS SQL 2000 (SP3) database over to PostgreSQL 8.1
using the latest ODBC driver (08.01.0200). I am using a DTS package in
SQL server to copy the data over, but I have a table with a "Text"
column, and the largest piece of data is 220,843 bytes.
The first issue I encountered, each row with my Text column over close
to 9,200 bytes, would print the following:
Data for source column 6 ('MyTextField') is too large for the
specified buffer size.
Searching through the list, I saw to uncheck the "Text as LongVarChar"
option, I tried this, but received the same error for every row over
1,278 bytes, strange.
I reset "Text as LongVarChar" to true, then bumped up the size of Max
LongVarChar from 8,190 to 32,000. This actually worked for every row
over 32,000 bytes, progress!
Next, when I set size of "Max LongVarChar" to 225,000 bytes, I receive
the same error for rows over around 30,000 bytes, strange again.
If I bump it up further to 230,000 bytes, every row fails with the
error:
Query-based insertion or updating of BLOB values is not supported.
It also looks like it was unable to allocate proper memory and is
pulling garbage data from somewhere for every row.
Does anyone have any ideas?
In addition to this, I have a table with a column of type "Image" going
into column of type "bytea", and appear to have a similar problem,
though I haven't pinpointed the exact size it starts complaining at.
In addition to those errors, I receive:
Insert error, column 3 ('imagefile', DBTYPE_BYTES), status 2: Error
converting value.
Thanks in advance!
-Daniel
> I am converting a MS SQL 2000 (SP3) database over to PostgreSQL 8.1 > using the latest ODBC driver (08.01.0200). I am using a DTS package in > SQL server to copy the data over, but I have a table with a "Text" > column, and the largest piece of data is 220,843 bytes. > > Searching through the list, I saw to uncheck the "Text as LongVarChar" > option, I tried this, but received the same error for every row over > 1,278 bytes, strange. If you uncheck it the Text is VarChar (smaller than LongVarChar). > I reset "Text as LongVarChar" to true, then bumped up the size of Max > LongVarChar from 8,190 to 32,000. This actually worked for every row > over 32,000 bytes, progress! > > Next, when I set size of "Max LongVarChar" to 225,000 bytes, I receive > the same error for rows over around 30,000 bytes, strange again. > > If I bump it up further to 230,000 bytes, every row fails with the > error: > Query-based insertion or updating of BLOB values is not supported. > It also looks like it was unable to allocate proper memory and is > pulling garbage data from somewhere for every row. > > Does anyone have any ideas? Could you post mylog outputs for this three situations? It would be better if you try it with max 2 rows (it results in smaller mylog). Regards, Luf
Is there a max file size, or max # of attachments, I sent 4 .log files yesterday, it totaled around 200K, and I havent seen the message come through. Is there a delay on the list? -----Original Message----- From: Ludek Finstrle [mailto:luf@pzkagis.cz] Sent: Wednesday, March 01, 2006 1:59 AM To: Daniel Holm Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] pgsql ODBC 8.1.200 MSSQL Migration Errors > I am converting a MS SQL 2000 (SP3) database over to PostgreSQL 8.1 > using the latest ODBC driver (08.01.0200). I am using a DTS package in > SQL server to copy the data over, but I have a table with a "Text" > column, and the largest piece of data is 220,843 bytes. > > Searching through the list, I saw to uncheck the "Text as LongVarChar" > option, I tried this, but received the same error for every row over > 1,278 bytes, strange. If you uncheck it the Text is VarChar (smaller than LongVarChar). > I reset "Text as LongVarChar" to true, then bumped up the size of Max > LongVarChar from 8,190 to 32,000. This actually worked for every row > over 32,000 bytes, progress! > > Next, when I set size of "Max LongVarChar" to 225,000 bytes, I receive > the same error for rows over around 30,000 bytes, strange again. > > If I bump it up further to 230,000 bytes, every row fails with the > error: > Query-based insertion or updating of BLOB values is not supported. > It also looks like it was unable to allocate proper memory and is > pulling garbage data from somewhere for every row. > > Does anyone have any ideas? Could you post mylog outputs for this three situations? It would be better if you try it with max 2 rows (it results in smaller mylog). Regards, Luf
"Daniel Holm" <Daniel.Holm@interworksinc.com> writes:
> Is there a max file size, or max # of attachments, I sent 4 .log files
> yesterday, it totaled around 200K, and I havent seen the message come
> through. Is there a delay on the list?
AFAIK there's only a delay if you're not subscribed. There's a max
message size (on the order of 20K IIRC), but I think majordomo will
just instantly bounce any oversized message. In either case you
should have gotten a response message saying what happened. You might
want to check your subscription settings to see if the responses are
turned off for some reason.
regards, tom lane
Trying this again, with the attachments zipped up.
Row1: 80804 bytes
Row2: 63456 bytes
Attached are the results for the following 4 runs:
mylog_4172.log
Unknown Sizes: Maximum
Text as LongVarChar: Yes
Max LongVarChar: 225000
MSSQL Error: Data for source column 1 ('textfield') is too large for the
specified buffer size.
mylog_3988.log
Unknown Sizes: Maximum
Text as LongVarChar: Yes
Max LongVarChar: 230000
MSSQL Error: Query-based insertion or updating of BLOB values is not
supported.
mylog_3988_2.log
Unknown Sizes: Maximum
Text as LongVarChar: Yes
Max LongVarChar: 64000
MSSQL Error: Query-based insertion or updating of BLOB values is not
supported.
mylog_5968.log
Unknown Sizes: Maximum
Text as LongVarChar: Yes
Max LongVarChar: 32000
MSSQL Error: Data for source column 1 ('textfield') is too large for the
specified buffer size.
I tried with 64,000, but it came back with the BLOB error... so bizarre.
Thanks for your help!
-Daniel
-----Original Message-----
From: Ludek Finstrle [mailto:luf@pzkagis.cz]
Sent: Wednesday, March 01, 2006 1:59 AM
> I am converting a MS SQL 2000 (SP3) database over to PostgreSQL 8.1
> using the latest ODBC driver (08.01.0200). I am using a DTS package in
> SQL server to copy the data over, but I have a table with a "Text"
> column, and the largest piece of data is 220,843 bytes.
>
> Searching through the list, I saw to uncheck the "Text as LongVarChar"
> option, I tried this, but received the same error for every row over
> 1,278 bytes, strange.
If you uncheck it the Text is VarChar (smaller than LongVarChar).
> I reset "Text as LongVarChar" to true, then bumped up the size of Max
> LongVarChar from 8,190 to 32,000. This actually worked for every row
> over 32,000 bytes, progress!
>
> Next, when I set size of "Max LongVarChar" to 225,000 bytes, I receive
> the same error for rows over around 30,000 bytes, strange again.
>
> If I bump it up further to 230,000 bytes, every row fails with the
> error:
> Query-based insertion or updating of BLOB values is not supported.
> It also looks like it was unable to allocate proper memory and is
> pulling garbage data from somewhere for every row.
>
> Does anyone have any ideas?
Could you post mylog outputs for this three situations? It would
be better if you try it with max 2 rows (it results in smaller mylog).
Regards,
Luf
Attachment
Apologies if this is largely a Visual Studio 2005 question. I'm posting it here because I assume my problem is caused by the interaction between psqlODBC and Visual Studio. I'm using psqlODBC 8.01.02.00. When using the graphical Query Builder tool, you can specify a filter for your query. For example, if you specify the filter for table column x as " = sometext' then you get: WHERE x = 'sometext' in your query. All well and good if the column is of type text, but if the column is of type Boolean I can't figure out how to get the damn thing to accept my filter. For example: " = 't'" " = t" " = true" etc... all don't work, saying that the data is of the wrong type. Ok, so you're wondering why I don't just write my query the good old fashioned way? The problem is if the Query Builder doesn't like your query, it spits the dummy when it tries to generate the FillBy and GetBy queries needed to use VS 2005 TableAdaptors. The only workaround I've found so far is to create a dummy function that always returns true and use that in the filter, eg. " = alwaysTrue(12345)" Any ideas on how to get it to accept my filter as boolean so I don't have to use this workaround? Regards, Patrick
Actually, the Query Builder seems to be almost completely broken with version 8.01.02.00. In previous versions I could use it to define simple filters etc.. With 8.01.02.00 I can only use the Query Builder to generate a bare-bones SELECT/INSERT/... I found I can then close the generate wizard and make a more advanced query by hand in the properties window without everything breaking. Walking on tip-toes though! Patrick PS. Sorry, it's probably not so much the fault of psqlODBC developers as the result of having to fit the Microsoft mould... > Apologies if this is largely a Visual Studio 2005 question. I'm > posting it here because I assume my problem is caused by the > interaction between psqlODBC and Visual Studio. I'm using psqlODBC > 8.01.02.00. > > When using the graphical Query Builder tool, you can specify a filter > for your query. For example, if you specify the filter for table > column x as " = sometext' then you get: > WHERE x = 'sometext' > in your query. > > All well and good if the column is of type text, but if the column is > of type Boolean I can't figure out how to get the damn thing to accept > my filter. For example: > " = 't'" > " = t" > " = true" > etc... > all don't work, saying that the data is of the wrong type. > > Ok, so you're wondering why I don't just write my query the good old > fashioned way? The problem is if the Query Builder doesn't like your > query, it spits the dummy when it tries to generate the FillBy and > GetBy queries needed to use VS 2005 TableAdaptors. > > The only workaround I've found so far is to create a dummy function > that always returns true and use that in the filter, eg. > " = alwaysTrue(12345)" > > Any ideas on how to get it to accept my filter as boolean so I don't > have to use this workaround? > > Regards, > > Patrick > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match