Re: SQL Server Upgrade wizard

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I have used three methods. The data transfer fails on the datetime column.
--
Arne Garvander
(I program VB.Net for fun and C# to get paid. When get paid, I laugh all the
way to the bank.)


"Albert D. Kallal" wrote:

"Arne Garvander" <ArneGarvander@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9230618-1027-4640-92EB-E574B1652E03@xxxxxxxxxxxxxxxx
Albert,
Thanks for trying, but you don't seem to understand my problem.
I have legacy data in Access. I need to copy my data to SQL server BEFORE
I
can link to SQL server. Exporting data from Access is error prone.

I understand your problem perfectly.

What I'm saying when you use the upsizing wizard if the upsizing wizard
works will link tables for you after!!!

Of course if the upsizing wizard fails, then it's ***not*** going to create
that table link for you! However keep in mind that you have two methods of
transferring data to SQL server, and I'm simply saying to try both of the
methods. Those two methods use different technologies BEFORE the table
linking occurs. Ok...got it now?

That's why I asked if your tried both methods of upsizing. One method uses
the native oleDB database connection, and the other method uses an ODBC
connection and driver. So, there is two methods of transferring data and
upsizing data to SQL server here. Sometimes choosing one method of the other
will produce better results.

This occurs *****B E F O R E ***** the table link occurs!

So, NO, I am NOT asking you to link the data. I'm asking you did you try
both methods of upsizing and was there ***ANY*** difference at all between
the two methods of transferring and upsizing data to SQL server?

There was (is) a possibility that one of the methods might of work for you
and therefore would save you some programming and having to write some code
to scrub the data before you transferred that data.

ok, so I shall assume that you tried both methods of upsizing data (you
tried oleDB methoed, and you tried the odbc method).

Now that we've determined you tried both methods, then we're gonna have to
do a little bit of hunting here and use some alternate methods to fix this
problem.

The NEXT thing I would look at is corrupted date data. In fact simply bring
up the access table in question in view mode and do a sort on a date column.
You can then scroll down to the very first date values. You often find 2 or
3 bad date values that are well outside the range that SQL server allows
(and in 9 out ot 10 times you can also recognize that the few date values
are completely outside of what dates are typical for your application). The
the upsizing tools crap out and are **very** sensitive to bad date values.
So now that that's the next area you should concentrate on.

So, check your date value or date ranges. In my case I avoided having to
write some data scrubbing code because I found only about five records that
were Obviously outside of a reasonable date value. I simply edited those 4
bad data values and then my transfer (upsize) to sql server worked just
fine.

So, little bit of patience and thinking on your part can result in a lot of
fruit and a lot of progress on this issue. Even if the above idea of
sorting the date collum and looking for a few bad records does not help,
that's the first area I would concentrate on in terms of some scrubbing code
to allow the transfer.

The other area is to look at if your using a a packed decimal field in
access....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx



.

Relevant Pages

  • Re: SQL Server Upgrade wizard
    ... I need to copy my data to SQL server BEFORE ... transferring data to SQL server, and I'm simply saying to try both of the ... the two methods of transferring and upsizing data to SQL server? ... that's the first area I would concentrate on in terms of some scrubbing code ...
    (microsoft.public.access.externaldata)
  • Re: Problem with using BETWEEN for date range...
    ... In SQL Server because we don't have a DATE data type we need to store dates ... Rich coming from the guy who doesn't use the standard format himself. ... clearly trade data with that tradetime? ... because there is a DATETIME column which has a time on it. ...
    (comp.databases.ms-sqlserver)
  • RE: Security in DMZ
    ... When get paid, I laugh all the ... "Arne Garvander" wrote: ... I had to create two identically local accounts on the SQL server and DMZ ...
    (microsoft.public.sqlserver.security)
  • Re: Cannot update identity column
    ... course to learn something about SQL Server! ... Using IDENTITY (the proper term for an "autonumber" column). ... a DATETIME column to represent the date ... The short term fix is to use DBCC CHECKIDENT to change the identity ...
    (microsoft.public.sqlserver.programming)
  • Re: converting SQLTable to Excel Sheet
    ... open it the datetime column and int columns are not getting recognised. ... and Excel is not likely to understand that. ... I don't think you should export to an .xls file. ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)