Problem Upsizing Large Table to SQL Server 2000

[Tony Toews [MVP]]

It will create tables.
Personally I find it better to copy the data first & create the indexes etc.
later especially when dealing with huge amounts of data; I like to pretend
to have a life <g>. scripting the drop / create index & relations &
executing takes far less time than the time involved in waiting for the data
to be processed.

Oh I quite agree. I suggest that at my Random Thoughts on SQL Server
Upsizing from Microsoft Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Well, I downloaded it and it wouldn't have helped, as it is for SQL
2005, and I'm working with 2000.

Ah, that would make sense. And I'd completely forgotten that.
Although I'm sure there's a means of moving the schema to SQL Server
2000.

It was also one of the most
annoying installations I've ever seen, as it first told me I didn't
have the .NET 2.0 CLR installed, but didn't offer to install it. And
then, to top it off, after installing that, it complained I didn't
have the J# runtime installed. Thankfully, it gave me the option of
installing it, so it wasn't *quite* as annoying.
Ahh.

And then it couldn't read secured MDBs, which is just bloody stupid,
so it was a complete waste of my time.

Double ahh

If there's a SQL Server 2000 version of it, I'd like to know about
it, but I couldn't find it in my extensive Googling.

I doubt it very much as the SQL Server group created that tool a short
while, meaning a year or two ago. So they'd use the latest
technologies of course.

Oh, BTW, the Access 2K3 upsizing wizard did a *terrible* job. It
failed to cluster the PK indexes, which resulted in table scans
*snort* on some of my queries! By revising the indexes to match what
the source MDB had had, the query plans were vastly improved and
tons of performance increases resulted.

Yeah, well, you're a smart guy.

Any thoughts on whether tblDonor would be better to cluster on the
name fields (assuming clustering can be done on compound keys that
allow Nulls)? I'm pretty sure the other tables are set up
appropriately.

Umm, I didn't think SQL Server allowed null values in compound keys
which Jet does.

As to the rest of your question I have no idea as I'm not any kind of
SQL Server expert.

On the other hand, performance on tblDonor name lookups as outlined
above is just as fast as it was with Access.

Ah, good.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/