November 10, 2006 at 9:48 am
Hi there,
I have an issue whereby i cannot import data from an external db using the DBISAM 3 ODBC driver - this is the driver i use to connect to my external database.
My first problem is that DTS hangs when i simply copy the table. The table is created but that data never arrives - i have left this running over night and the status remains at "Running(0)". It looks like it is pumping the data, but nothing is actually happening. The problem appeared to be with the memo fields. They were being created as "Text" data types, so i changed these fields to VARCHAR 8000 and this overcame the "hanging" issue, but gave me a new head ache.
Now with the varchars i get an error "cannot create a row of size 9374 which is greater than the allowable maximum of 8060", so i then used a query to import the data instead of copying the table. The query contained a where clause to only include the varchar fields if they were under 8000 in length - ie.
WHERE LENGTH("defchgs"."CurrentValue") <= 8000
OR
LENGTH("defchgs"."OriginalValue") <= 8000
THis still gave me the same error - HELP!!!
I've tried changing the regedt32 as per other posts on this site to no avail. I also modified the "Maximum ROw Size" to 65000 + in "Disconnected Edit".
Now i have run out of ideas, so any imput would be most welcome! Firstly why using Text data type the package hangs and also why i my where clause is not working for varchars?????
Thanks,
James
November 13, 2006 at 8:00 am
This was removed by the editor as SPAM
November 13, 2006 at 9:54 am
ANY thoughts? I didn't post this earlier because it isn't a fully formed idea but it could be there is some kind of implicit conversion being attempted behind the scenes (though why that should error... I don't know).
Your query syntax for the DTS is a little confusing. Are you using the DATALENGTH function for the text columns?
SELECT textfield=CONVERT(varchar(8000),textfield) FROM texttable WHERE DATALENGTH(textfield)<=8000
It might be something specific to your driver because I can't seem to reproduce the error. (Though you did mention that it successfully translated the memo type into a text field)
November 13, 2006 at 10:07 am
Thanks guys,
I used this DBISAM supported syntax and it appears to do the trick:
SELECT SUBSTRING("defchgs"."OriginalValue" FROM 1 FOR 8000) AS OriginalValue FROM Table
-James
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply