Upsizing Wizard and SQl Server 2005

E

Ed Warren

Can the upsizing wizard be made to work with SQL Server 2005?
When I try to upsize to SQL Server 2005, the connection fails. Is there a
workaround? Right now I have figured out 'a way' by upsizing to an instance
of sql server 2000 then detaching and attaching the database. In the very
near future, I will need to drop the Sql 2000 instance and work only with
Sql 2005.

Thanks,

Ed Warren.
 
E

Ed Warren

Thanks, I'm trying to get all this figured out. SSIS sort of works, but
chokes on Access Date/time fields. UGH!!

Ed Warren
 
R

Rick Brandt

Ed said:
Thanks, I'm trying to get all this figured out. SSIS sort of works,
but chokes on Access Date/time fields. UGH!!

Really? We've just installed SS 2005 so I haven't messed with SSIS yet, but
I have used DTS in SS 2000 quite a bit and have not had that problem with
Access tables. I *have* seen DTS reject a date from an ISeries import of
1/1/1900 even though that is a perfectly legitimate date for SS. I was able
to use a script in the DTS transform to get around the problem.

Strangely enough I used IsDate() in an If-Then block and told it to
substitute '1900-01-01' whenever IsDate() failed and then all of the records
came over just fine. So I was effectively telling it to substitute the same
value that it was rejecting and had no problems. Seems as if the DTS tool
has a different notion of what is a valid date then the actual database
engine does.
 
E

Ed Warren

I had imported the same Access Database using DTS some time ago, but have
not taken a close look at the data/time fields to make sure all of them are
valid.

There very well may be a problem with the actual data in the original
database, but when I changed the data type from date/time to text it
imported straight away. To be honest, I'm really new to this and I am
trying to cover a lot of ground at one go. I have not gone back through the
data to verify that all the date fields are 'valid', I do know that at some
of them are <null>.

Thanks for the small glimmer of hope ;>

Ed Warren
 
R

Rick Brandt

Ed said:
I had imported the same Access Database using DTS some time ago, but
have not taken a close look at the data/time fields to make sure all
of them are valid.

There very well may be a problem with the actual data in the original
database, but when I changed the data type from date/time to text it
imported straight away. To be honest, I'm really new to this and I am
trying to cover a lot of ground at one go. I have not gone back
through the data to verify that all the date fields are 'valid', I do
know that at some of them are <null>.

Thanks for the small glimmer of hope ;>

The Date/Time type in Access has a wider range of valid values than the
DateTime in SQL Server. Access can go all the way back to year 100 (I
think) and SS only goes back to the year 1900 (or is it 1901?).
 
E

Ed Warren

I found the little culprit hiding way down deep in the data pile. A 'bad
date' just like you said something like 6/1/256.
Now it works like a charm.

Ed Warren.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top