How does the data get into the Memo field in Access?
Thanks,
Kevin Knaus
-----Original Message-----
From: mmcnary
[mailto:db2udbdba-ezmlmshield-x1300313.[Email address protected]
Sent: Thursday, May 26, 2005 10:22 AM
To: LazyDBA Discussion
Subject: converting Access Memo fields to CLOB in UDB
I have a need to bring over data from a legacy Access database to UDB.
I
have tried a variety of methods to get the data over, many of which were
successful. But I have at least one table with one column that is
defined
with a data type of Memo, that has data in it that is longer than 32k,
so a
CLOB is my only real option. This is purchase order/contract
information,
so I can't just lop off the last bit and go on.
With the db2 table column defined as a CLOB, as a linked table in
Access, it
appears as a 255 byte text field. This copies the data, but truncates
it.
When I define the db2 table as a long varchar, it defines it as a Memo
type.
When I try to do any kind of append query in Access on that column, I
get an
invalid conversion error, CLI102E.
My next step was to try a delimited export file. This got the data out
of
the table just fine, but then another problem surfaced. The problem
lies in
the data itself. It contains CR/LF characters so that it formats nicely
on
the Access application. When I try to do any kind of text/delimited
import,
DB2 interprets those CR/LF characters as new records, which plays hob
with
my data.
Using DTS, I have been able to put the data into SQL Server from the
Access
DB. However, when I try to use DTS to get the data into DB2, I get
'CLI0150E Driver not capable. SQLSTATE=S1C00'. I am having no luck
adding
my db2 database as a linked server to SQL Server, and I can't buy the
federated database tool.
Can this be done, within the limitations I am working under? If not,
then
I can build a business case for purchasing a tool, so I would also
appreciate any input as to which tool would be best.
Thanx,
Mark McNary
NPPC MES Database support
(w) 314-982-1389
(m) 314-497-5850
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page