Access, DTS and Sql Server Corruption 
Author Message
 Access, DTS and Sql Server Corruption

I had an "interesting" experience this week which I descrive below.
I'm very disturbed that it was so hard to nail down, and how
unconcerned Sql Server seemed with the problem. This problem occurred
with 3 tables, but I'll describe it in relation to only one of them.

I transferred a table from Access 97 (SP2b) to Sql Server (Desktop,
SP2) containing 652 rows.
The table looked fine in Access (652 rows), and also in Sql Server
when opened from within Enterprise Manager (652 rows), as in:

Col1(Integer)(PK)    Col2(Integer)   Col3(VarChar(50))
1                              1                      Descr1
2                              1                      Descr2
3                              2                      Descr3
4                              3                      Descr4

But, using Query Analyzer,
  Select * From MyTable
returned 668 rows (652 rows reported in the results panel), as in:

Col1              Col2             Col3
1                    1                  Descr1
2                    1                  Descr2
3                    2                  Descr3
     BOS_AWARD    1        2      School award
4                    3                  Descr4

Select Col1, and Select Col2 both worked correctly, but Select Col3
produced a similar problem.

That 4th bogus line actually belongs in a separate Access table -
nothing whatever to do with this table.

The Access table was originally imported from a fixed-length text
file. I then remembered that I've discovered a long-term bug in Access
(2, 97 and 2000) where small fixed-length text files imported into
Access do not always arrive with all rows intact. I re-imported the
file, this time using comma-delimited, then dropped and rebuilt the
Sql Server table, then reran my DTS package. Now everything works.

How can EM produce a different result to QA? If the table was corrupt,
why did Sql Server not complain (I apply all constraints, and define
all indexes, after the data has been transferred into Sql Server). I
can very easily reproduce this problem to anyone interested.

--
Regards,

Paul Thornett



Wed, 04 Dec 2002 03:00:00 GMT
 Access, DTS and Sql Server Corruption

Paul,

My guess is that the "extra row" actually belonged to the row above. That above row
had (for some reason) a CRLF in its data for the Col3 column. That should be
verifiable using a suitable combination of ISQL.EXE /OSQL.EXE, a hexeditor and
searching (SELECT) for rows that contains 10 and/or 13.

--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.

Quote:

> I had an "interesting" experience this week which I descrive below.
> I'm very disturbed that it was so hard to nail down, and how
> unconcerned Sql Server seemed with the problem. This problem occurred
> with 3 tables, but I'll describe it in relation to only one of them.

> I transferred a table from Access 97 (SP2b) to Sql Server (Desktop,
> SP2) containing 652 rows.
> The table looked fine in Access (652 rows), and also in Sql Server
> when opened from within Enterprise Manager (652 rows), as in:

> Col1(Integer)(PK)    Col2(Integer)   Col3(VarChar(50))
> 1                              1                      Descr1
> 2                              1                      Descr2
> 3                              2                      Descr3
> 4                              3                      Descr4

> But, using Query Analyzer,
>   Select * From MyTable
> returned 668 rows (652 rows reported in the results panel), as in:

> Col1              Col2             Col3
> 1                    1                  Descr1
> 2                    1                  Descr2
> 3                    2                  Descr3
>      BOS_AWARD    1        2      School award
> 4                    3                  Descr4

> Select Col1, and Select Col2 both worked correctly, but Select Col3
> produced a similar problem.

> That 4th bogus line actually belongs in a separate Access table -
> nothing whatever to do with this table.

> The Access table was originally imported from a fixed-length text
> file. I then remembered that I've discovered a long-term bug in Access
> (2, 97 and 2000) where small fixed-length text files imported into
> Access do not always arrive with all rows intact. I re-imported the
> file, this time using comma-delimited, then dropped and rebuilt the
> Sql Server table, then reran my DTS package. Now everything works.

> How can EM produce a different result to QA? If the table was corrupt,
> why did Sql Server not complain (I apply all constraints, and define
> all indexes, after the data has been transferred into Sql Server). I
> can very easily reproduce this problem to anyone interested.

> --
> Regards,

> Paul Thornett



Fri, 06 Dec 2002 03:00:00 GMT
 Access, DTS and Sql Server Corruption

Quote:
> My guess is that the "extra row" actually belonged to the row above.
That above row
> had (for some reason) a CRLF in its data for the Col3 column. That
should be
> verifiable using a suitable combination of ISQL.EXE /OSQL.EXE, a
hexeditor and
> searching (SELECT) for rows that contains 10 and/or 13.

Your explanation sounds very good - I should have thought of that
myself, but didn't <g>! "For some reason" is undoubtedly a corruption
within Access.


Fri, 06 Dec 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. DAO 2.5 / Access 2.0 / VB 4.0 / NT Server Access MDB corruption problem

2. DTS SQL Server 7 vs DTS SQL Server 2000

3. Convert MS Access import spec into SQL Server DTS package

4. DTS : problem creating one from Access to SQL Server

5. DTS Problem/ Import table from Access to SQL Server

6. DTS from Access to SQL Server

7. DTS from ACCESS to SQL Server

8. Mimic MS Access Import with SQL Server DTS

9. DTS Problem: Access to SQL Server

10. DTS, SQL Server, Access, VB

11. DTS Access to SQL Server 7 conversion

12. Importing Access data to SQL Server with DTS


 
Powered by phpBB® Forum Software