Bug???-Importing dbfs with MEMO fields

Bug???-Importing dbfs with MEMO fields

Post by Joel » Sat, 14 Dec 2002 02:14:07



Hello SQL Server Gurus!  I am in serious need of some help
as this is driving me absolutely crazy!!  In the attached
zip is a Foxpro .dbf file.

Can anyone out there successfully do the following:
1)  Import the .dbf into MS SQL Server 2000 (be sure and
save the import as a DTS package).
2)  Run that same import (it is saved as a DTS pkg) via a
SQL Server Agent job using the dtsrun command.

Step 1 above works just fine.  Step 2, the job fails every
time.  Attached is the job's log file showing the error.

Facts:  This only happens to me when the .dbf file has a
memo field in it.  This dtsrun problem was/is not a
problem on SQL Server 7.

If anyone is successful, please let me know how you did
it.  The fact that the manual import works, yet the exact
same import via the SQL Server 2000 job DOES NOT work
seems like a bug to me.  VERY frustrating!

Thanks!!
Joel

Tips for those who want to try:
**You will need to save these 3 files (dbf, cdx, fpt) into
your directory.

**I use dBase 5 as the data source for the import.

**My dtsrun command in my job step looks like this:  
dtsrun /SMyServer /NMyLocalDTSPkg /E
(where "MyServer" is my server name, and "MyLocalDTSPkg"
is my Local DTS Package name)

  Dtsrun_DBFs&LogFiles.zip
1K Download
 
 
 

Bug???-Importing dbfs with MEMO fields

Post by Joel » Sat, 14 Dec 2002 22:50:11


Okay, I have figured out that it has nothing to do with
dtsrun....it has something to do with the Job.  I have run
the dtsrun command via a batch file and it works.  Only
when you run it via a Job does it fail with the following
error.

C:\Program Files\Common Files\System\Mapi\1033
\NT>dtsrun /SMyServer /NMyTMP_MEMO_Pkg /E
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: Create Table [MyDatabase].[dbo].[CHGLOG]
Step
DTSRun OnFinish: Create Table [MyDatabase].[dbo].[CHGLOG]
Step
DTSRun OnStart: Copy Data from CHGLOG to [MyDatabase].
[dbo].[CHGLOG] Step
DTSRun OnError: Copy Data from CHGLOG to [MyDatabase].
[dbo].[CHGLOG] Step, Error = -2147467259 (80004005)
Error string: External table is not in the expected format.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003274
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: -328602519
(EC69EC69)
Error string: External table is not in the expected format.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003274
DTSRun OnFinish: Copy Data from CHGLOG to [MyDatabase].
[dbo].[CHGLOG] Step
DTSRun: Package execution complete.

Quote:>-----Original Message-----
>Hello SQL Server Gurus!  I am in serious need of some
help
>as this is driving me absolutely crazy!!  In the attached
>zip is a Foxpro .dbf file.

>Can anyone out there successfully do the following:
>1)  Import the .dbf into MS SQL Server 2000 (be sure and
>save the import as a DTS package).
>2)  Run that same import (it is saved as a DTS pkg) via a
>SQL Server Agent job using the dtsrun command.

>Step 1 above works just fine.  Step 2, the job fails
every
>time.  Attached is the job's log file showing the error.

>Facts:  This only happens to me when the .dbf file has a
>memo field in it.  This dtsrun problem was/is not a
>problem on SQL Server 7.

>If anyone is successful, please let me know how you did
>it.  The fact that the manual import works, yet the exact
>same import via the SQL Server 2000 job DOES NOT work
>seems like a bug to me.  VERY frustrating!

>Thanks!!
>Joel

>Tips for those who want to try:
>**You will need to save these 3 files (dbf, cdx, fpt)
into
>your directory.

>**I use dBase 5 as the data source for the import.

>**My dtsrun command in my job step looks like this:  
>dtsrun /SMyServer /NMyLocalDTSPkg /E
>(where "MyServer" is my server name, and "MyLocalDTSPkg"
>is my Local DTS Package name)


 
 
 

Bug???-Importing dbfs with MEMO fields

Post by Allan Mitchel » Sat, 14 Dec 2002 23:01:02


the error 80004005 smacks of a permissions thing.  Also the fact that it
will run unchanged interactively and not as a scheduled job.

See this

INF: How to Run a DTS Package as a Scheduled Job
PSS ID Number: Q269074
Article Last Modified on 09-25-2002

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> Okay, I have figured out that it has nothing to do with
> dtsrun....it has something to do with the Job.  I have run
> the dtsrun command via a batch file and it works.  Only
> when you run it via a Job does it fail with the following
> error.

> C:\Program Files\Common Files\System\Mapi\1033
> \NT>dtsrun /SMyServer /NMyTMP_MEMO_Pkg /E
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnStart: Create Table [MyDatabase].[dbo].[CHGLOG]
> Step
> DTSRun OnFinish: Create Table [MyDatabase].[dbo].[CHGLOG]
> Step
> DTSRun OnStart: Copy Data from CHGLOG to [MyDatabase].
> [dbo].[CHGLOG] Step
> DTSRun OnError: Copy Data from CHGLOG to [MyDatabase].
> [dbo].[CHGLOG] Step, Error = -2147467259 (80004005)
> Error string: External table is not in the expected format.
> Error source: Microsoft JET Database Engine
> Help file:
> Help context: 5003274
> Error Detail Records:
> Error: -2147467259 (80004005); Provider Error: -328602519
> (EC69EC69)
> Error string: External table is not in the expected format.
> Error source: Microsoft JET Database Engine
> Help file:
> Help context: 5003274
> DTSRun OnFinish: Copy Data from CHGLOG to [MyDatabase].
> [dbo].[CHGLOG] Step
> DTSRun: Package execution complete.

> >-----Original Message-----
> >Hello SQL Server Gurus!  I am in serious need of some
> help
> >as this is driving me absolutely crazy!!  In the attached
> >zip is a Foxpro .dbf file.

> >Can anyone out there successfully do the following:
> >1)  Import the .dbf into MS SQL Server 2000 (be sure and
> >save the import as a DTS package).
> >2)  Run that same import (it is saved as a DTS pkg) via a
> >SQL Server Agent job using the dtsrun command.

> >Step 1 above works just fine.  Step 2, the job fails
> every
> >time.  Attached is the job's log file showing the error.

> >Facts:  This only happens to me when the .dbf file has a
> >memo field in it.  This dtsrun problem was/is not a
> >problem on SQL Server 7.

> >If anyone is successful, please let me know how you did
> >it.  The fact that the manual import works, yet the exact
> >same import via the SQL Server 2000 job DOES NOT work
> >seems like a bug to me.  VERY frustrating!

> >Thanks!!
> >Joel

> >Tips for those who want to try:
> >**You will need to save these 3 files (dbf, cdx, fpt)
> into
> >your directory.

> >**I use dBase 5 as the data source for the import.

> >**My dtsrun command in my job step looks like this:
> >dtsrun /SMyServer /NMyLocalDTSPkg /E
> >(where "MyServer" is my server name, and "MyLocalDTSPkg"
> >is my Local DTS Package name)

 
 
 

Bug???-Importing dbfs with MEMO fields

Post by SQL Server Development Team [MSFT » Sun, 15 Dec 2002 03:41:44


80004005 is definately a permissions problem.

Cheers!
Don Gill

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

> the error 80004005 smacks of a permissions thing.  Also the fact that it
> will run unchanged interactively and not as a scheduled job.

> See this

> INF: How to Run a DTS Package as a Scheduled Job
> PSS ID Number: Q269074
> Article Last Modified on 09-25-2002

> --

> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org



> > Okay, I have figured out that it has nothing to do with
> > dtsrun....it has something to do with the Job.  I have run
> > the dtsrun command via a batch file and it works.  Only
> > when you run it via a Job does it fail with the following
> > error.

> > C:\Program Files\Common Files\System\Mapi\1033
> > \NT>dtsrun /SMyServer /NMyTMP_MEMO_Pkg /E
> > DTSRun: Loading...
> > DTSRun: Executing...
> > DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> > DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> > DTSRun OnStart: Create Table [MyDatabase].[dbo].[CHGLOG]
> > Step
> > DTSRun OnFinish: Create Table [MyDatabase].[dbo].[CHGLOG]
> > Step
> > DTSRun OnStart: Copy Data from CHGLOG to [MyDatabase].
> > [dbo].[CHGLOG] Step
> > DTSRun OnError: Copy Data from CHGLOG to [MyDatabase].
> > [dbo].[CHGLOG] Step, Error = -2147467259 (80004005)
> > Error string: External table is not in the expected format.
> > Error source: Microsoft JET Database Engine
> > Help file:
> > Help context: 5003274
> > Error Detail Records:
> > Error: -2147467259 (80004005); Provider Error: -328602519
> > (EC69EC69)
> > Error string: External table is not in the expected format.
> > Error source: Microsoft JET Database Engine
> > Help file:
> > Help context: 5003274
> > DTSRun OnFinish: Copy Data from CHGLOG to [MyDatabase].
> > [dbo].[CHGLOG] Step
> > DTSRun: Package execution complete.

> > >-----Original Message-----
> > >Hello SQL Server Gurus!  I am in serious need of some
> > help
> > >as this is driving me absolutely crazy!!  In the attached
> > >zip is a Foxpro .dbf file.

> > >Can anyone out there successfully do the following:
> > >1)  Import the .dbf into MS SQL Server 2000 (be sure and
> > >save the import as a DTS package).
> > >2)  Run that same import (it is saved as a DTS pkg) via a
> > >SQL Server Agent job using the dtsrun command.

> > >Step 1 above works just fine.  Step 2, the job fails
> > every
> > >time.  Attached is the job's log file showing the error.

> > >Facts:  This only happens to me when the .dbf file has a
> > >memo field in it.  This dtsrun problem was/is not a
> > >problem on SQL Server 7.

> > >If anyone is successful, please let me know how you did
> > >it.  The fact that the manual import works, yet the exact
> > >same import via the SQL Server 2000 job DOES NOT work
> > >seems like a bug to me.  VERY frustrating!

> > >Thanks!!
> > >Joel

> > >Tips for those who want to try:
> > >**You will need to save these 3 files (dbf, cdx, fpt)
> > into
> > >your directory.

> > >**I use dBase 5 as the data source for the import.

> > >**My dtsrun command in my job step looks like this:
> > >dtsrun /SMyServer /NMyLocalDTSPkg /E
> > >(where "MyServer" is my server name, and "MyLocalDTSPkg"
> > >is my Local DTS Package name)

 
 
 

Bug???-Importing dbfs with MEMO fields

Post by Joel » Sun, 15 Dec 2002 03:49:28


Received the solution at a different Forum.  My thanks to
Satya!!!

Here's the solution and a link for anyone who may have
similar problems:

Installed a new File DSN (using Microsoft FoxPro VFP
Driver; Free Table Directory option) for each directory
where my DBFs, FPTs, CDXs reside. On the SQL Server 2000
import, I use this new ODBC as my Data Source.

Or see link:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;Q238431

The job does not error out now, and the memo fields are
imported successfully.

Joel

>-----Original Message-----
>the error 80004005 smacks of a permissions thing.  Also
the fact that it
>will run unchanged interactively and not as a scheduled
job.

>See this

>INF: How to Run a DTS Package as a Scheduled Job
>PSS ID Number: Q269074
>Article Last Modified on 09-25-2002

>--

>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org



>> Okay, I have figured out that it has nothing to do with
>> dtsrun....it has something to do with the Job.  I have
run
>> the dtsrun command via a batch file and it works.  Only
>> when you run it via a Job does it fail with the
following
>> error.

>> C:\Program Files\Common Files\System\Mapi\1033
>> \NT>dtsrun /SMyServer /NMyTMP_MEMO_Pkg /E
>> DTSRun: Loading...
>> DTSRun: Executing...
>> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
>> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
>> DTSRun OnStart: Create Table [MyDatabase].[dbo].[CHGLOG]
>> Step
>> DTSRun OnFinish: Create Table [MyDatabase].[dbo].
[CHGLOG]
>> Step
>> DTSRun OnStart: Copy Data from CHGLOG to [MyDatabase].
>> [dbo].[CHGLOG] Step
>> DTSRun OnError: Copy Data from CHGLOG to [MyDatabase].
>> [dbo].[CHGLOG] Step, Error = -2147467259 (80004005)
>> Error string: External table is not in the expected
format.
>> Error source: Microsoft JET Database Engine
>> Help file:
>> Help context: 5003274
>> Error Detail Records:
>> Error: -2147467259 (80004005); Provider Error: -
328602519
>> (EC69EC69)
>> Error string: External table is not in the expected
format.
>> Error source: Microsoft JET Database Engine
>> Help file:
>> Help context: 5003274
>> DTSRun OnFinish: Copy Data from CHGLOG to [MyDatabase].
>> [dbo].[CHGLOG] Step
>> DTSRun: Package execution complete.

>> >-----Original Message-----
>> >Hello SQL Server Gurus!  I am in serious need of some
>> help
>> >as this is driving me absolutely crazy!!  In the
attached
>> >zip is a Foxpro .dbf file.

>> >Can anyone out there successfully do the following:
>> >1)  Import the .dbf into MS SQL Server 2000 (be sure
and
>> >save the import as a DTS package).
>> >2)  Run that same import (it is saved as a DTS pkg)
via a
>> >SQL Server Agent job using the dtsrun command.

>> >Step 1 above works just fine.  Step 2, the job fails
>> every
>> >time.  Attached is the job's log file showing the
error.

>> >Facts:  This only happens to me when the .dbf file has
a
>> >memo field in it.  This dtsrun problem was/is not a
>> >problem on SQL Server 7.

>> >If anyone is successful, please let me know how you did
>> >it.  The fact that the manual import works, yet the
exact
>> >same import via the SQL Server 2000 job DOES NOT work
>> >seems like a bug to me.  VERY frustrating!

>> >Thanks!!
>> >Joel

>> >Tips for those who want to try:
>> >**You will need to save these 3 files (dbf, cdx, fpt)
>> into
>> >your directory.

>> >**I use dBase 5 as the data source for the import.

>> >**My dtsrun command in my job step looks like this:
>> >dtsrun /SMyServer /NMyLocalDTSPkg /E
>> >(where "MyServer" is my server name,
and "MyLocalDTSPkg"
>> >is my Local DTS Package name)

>.

 
 
 

1. Reattaching memos to corrupted DBFs

Sometimes a DBF gets trashed, if the system went down or was shut down
accidentally while the DBF was still open. Usually only the header is
spoiled.
I have a method for bypassing the
header and reading the record area of the DBF as fixed data, creating
comma-delimited values, and appending the values  into
a new DBF with the same structure.
  The trouble is that memo fields are not getting reattached. I've looked at
a DEBUG of a DBF with memos, and there is a binary value in the memo
field which probably points to a record in the FPT (memo file). The
question is, how to salvage that value? or how to recalculate it?
  Is there a technical reference or cookbook that gives the layout of a
Foxpro DBF/FPT?
  I know there are commercial systems that can salvage a DBF, but
I'd just like to know how to do it for myself. There are some orphan
memo files that I need to reattach to their original DBFs. Thanks!

2. Masking Graphics

3. ODBCdirect unable to update Memo fields (bug?)

4. Send Mail (OUTLOOK) by Progress Procedure

5. Interesting bug noticed (BDE3) with tables containing memo fields

6. Outer join

7. MS driver BUG??? memo field in DBaseIII

8. Any ADO gurus around???

9. Interesting bug noticed (BDE3) with tables containing memo fields

10. Memo Field, Date/Time Bug?

11. Bug in PDOXDOS4.0 memo field???

12. Major Bug using MEMO fields in Reports

13. Problem importing Access memo fields