D
David W. Fenton
I've upsized the back ends of a few relatively small apps to SQL
Server in the past and had no problems whatsoever with the process.
Today I was testing the upsizing of the biggest back end any of my
clients have (it's c. 400MBs compacted). Most of it went OK, but the
two largest tables had major problems. One of the tables has nearly
600K records and the other nearly 400K. The larger table wouldn't
upsize at all and was skipped. The second one upsized, but omitted
importing the data.
So, I created the first table structure alone and then tried to run
append queries to append the data. The first attempt crapped out
with "Overflow," so I assumed some records were screwed up, and did
the 50:50 split, using TOP 50% to append the first half of the
table, and then the second half of the table failed. Then I split
that half, and so forth, until I got to the last 1/4 of the table,
which gave me the overflow error no matter how I parsed it. I
started appending 10K records at a time, and that worked for a
while, until it failed, and then I started appending in 1K chunks,
and then in chunks of 500 and then 100. This worked sporadically.
Eventually, I wrote code to append 100 records at a time, and
appended everything that would work.
I ended up short about 12K records, and can't figure out why.
I then created a brand-new table with those records and tried to
upsize it, and got the same result.
Now, so far as I can see, all the data types are exactly correct
(that was my first assumption, that something was the wrong data
type), and there are no memo fields in either of the tables. Given
that I recreated the table (actually more than once) and compacted
several times (which takes excruciatingly long with a file that
large), I can't think what kind of corruption would cause this.
So, I'm stuck with 12K records that aren't going in and can't figure
out why. Any ideas?
And, oh, BTW -- I did the upsizing with A2K3, even though the app
runs in A2K. This is because the A2K upsizing wizard can't handle
SQL Server 2000, and gives, wait for it, an OVERFLOW error. This was
somewhat inconvenient as the server had only A2K installed, so I had
to use A2K3 from my laptop. I tried both, but in both versions the
problematic records gave the overflow error.
The other table that was empty, I just ran one append and it caused
no problems at all.
BTW, there was no DRI on either of these tables when all this
happened, so it's not a failure there (if it had been, all the
records in the larger table would have been rejected, since they
were supposed to be children of the second table, which I only later
discovered was entirely empty).
Any ideas on this?
What am I missing in terms of possibilities here? Remember that I
was able to upsize a temp table created from a MAKETABLE query
containing the problematic records with no problem, but then
couldn't append from that table into the destination table (using
either Access or Enterprise Manager's query builder).
Help?
Server in the past and had no problems whatsoever with the process.
Today I was testing the upsizing of the biggest back end any of my
clients have (it's c. 400MBs compacted). Most of it went OK, but the
two largest tables had major problems. One of the tables has nearly
600K records and the other nearly 400K. The larger table wouldn't
upsize at all and was skipped. The second one upsized, but omitted
importing the data.
So, I created the first table structure alone and then tried to run
append queries to append the data. The first attempt crapped out
with "Overflow," so I assumed some records were screwed up, and did
the 50:50 split, using TOP 50% to append the first half of the
table, and then the second half of the table failed. Then I split
that half, and so forth, until I got to the last 1/4 of the table,
which gave me the overflow error no matter how I parsed it. I
started appending 10K records at a time, and that worked for a
while, until it failed, and then I started appending in 1K chunks,
and then in chunks of 500 and then 100. This worked sporadically.
Eventually, I wrote code to append 100 records at a time, and
appended everything that would work.
I ended up short about 12K records, and can't figure out why.
I then created a brand-new table with those records and tried to
upsize it, and got the same result.
Now, so far as I can see, all the data types are exactly correct
(that was my first assumption, that something was the wrong data
type), and there are no memo fields in either of the tables. Given
that I recreated the table (actually more than once) and compacted
several times (which takes excruciatingly long with a file that
large), I can't think what kind of corruption would cause this.
So, I'm stuck with 12K records that aren't going in and can't figure
out why. Any ideas?
And, oh, BTW -- I did the upsizing with A2K3, even though the app
runs in A2K. This is because the A2K upsizing wizard can't handle
SQL Server 2000, and gives, wait for it, an OVERFLOW error. This was
somewhat inconvenient as the server had only A2K installed, so I had
to use A2K3 from my laptop. I tried both, but in both versions the
problematic records gave the overflow error.
The other table that was empty, I just ran one append and it caused
no problems at all.
BTW, there was no DRI on either of these tables when all this
happened, so it's not a failure there (if it had been, all the
records in the larger table would have been rejected, since they
were supposed to be children of the second table, which I only later
discovered was entirely empty).
Any ideas on this?
What am I missing in terms of possibilities here? Remember that I
was able to upsize a temp table created from a MAKETABLE query
containing the problematic records with no problem, but then
couldn't append from that table into the destination table (using
either Access or Enterprise Manager's query builder).
Help?
) ran an