 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / November 2005Upsizing Wizard and SQl Server 2005
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. > 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 [quoted text clipped - 4 lines] > > Thanks, I would use SSIS/DTS on the server to import the tables and data instead of using the upsize tool. You have a lot more control over the process and going in that direction eliminates your compatability issue.  SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Thanks, I'm trying to get all this figured out. SSIS sort of works, but chokes on Access Date/time fields. UGH!! 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 [quoted text clipped - 8 lines] > of using the upsize tool. You have a lot more control over the process > and going in that direction eliminates your compatability issue. > 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. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com 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 >> Thanks, I'm trying to get all this figured out. SSIS sort of works, >> but chokes on Access Date/time fields. UGH!! [quoted text clipped - 15 lines] > has a different notion of what is a valid date then the actual database > engine does. > 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 [quoted text clipped - 8 lines] > > 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?). SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com 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. >> 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 [quoted text clipped - 12 lines] > 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?). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.