Export from Access 2007 to SQL2008

[Arne Garvander]

I am trying to export an Access 2007 table to SQL2008 via ODBC/Sql native
Client.
I get the error message Specifed scale 50 is invalid.
What am I doing wrong?

 

[Albert D. Kallal]

I am trying to export an Access 2007 table to SQL2008 via ODBC/Sql native
Client.

Native client and odbc are two different issues....

When you upsize if you choose linked tables, then that is ODBC.

if you choose client/server, and then you get a sql native access
application (oleDB connection) to sql server.

As mentioned, try both approaches...do they both fail?

I get the error message Specifed scale 50 is invalid.

Is this occurring for all tables you transferring, or just one of the
tables?

As suggested in the other post....do try using linked tables in place of
client/server....

 

[Arne Garvander]

You can try to fly to London, but if the drop you of in Iceland it makes no
sense to make a phone call to London.
I have legacy data in Access which does not export to my new destination,
which is a SQL server instance on a different computer. Some tables export
nicely and some tables refuse to export.
I am now writing my own export program in C# since Access seems broken.

 

[Albert D. Kallal]

You can try to fly to London, but if the drop you of in Iceland it makes
no
sense to make a phone call to London.

Well actually it might. As I mentioned you have two different approaches and
two different type of data objects connections you can use when you transfer
data to SQL server.

And in fact it's important to note that some tables are transferring, and
some are not? (they would help if you've told me which the two type the
connections are using to do these transfers and which ones are working
better or worse). Or, if there is no difference at all here.

I have legacy data in Access which does not export to my new destination,
which is a SQL server instance on a different computer. Some tables export
nicely and some tables refuse to export.

See my other post....

The 1st thing to check in most cases is check for a bad value in the date
field.

If you have a packed decimal column, then consider transferring it as a text
column......

 

[Arne Garvander]

I have problem with datetime columns.
They cause the export to fail.
If I use SQL Server SSIS the data transfer fails on the same column.

 

[Albert D. Kallal]

Don't take my last post as me having lack of patience here and I apologize
if I sounded a bit harsh . We simply had some confusion between what I was
suggesting. I was suggesting to try different methods of transferring, not
to try to build the links to SQL server "before" the transfer has occurred.
Anyway, just not a big deal...

I have problem with datetime columns.
They cause the export to fail.

Excellent, we are now making progress. And, note how my 1st guess was to
check your date columns. And, it turns out this is exactly the information
you come back with now.

So, the 1st thing to check is the date range. What happens in access is
data enter errors someone will enter a birthdates like:

10/10/199

They wanted to type in 1999, but miss the last digit. The above then results
in

10/10/199

The above year is the year 0199 (199 AD!!).

Keep in mind that access can support dates from 1 Jan 100 to 31 Dec 9999. A
SQL Server datetime field accepts dates from 1 Jan 1753 to 31 Dec 9999. (sql
server smalldatetime field accepts dates from 1 Jan 1900 to 6 Jun 2079).

So, it not likely the fact of date + time field, it simply the date part is
out of range here...

If your dates are supposed to be recent dates, then simply run a query to
find anything outside of a recent time period.

I would run a query that finds anything prior to 01/01/1900. Do you get any
dates prior to this date? If you only get 5, or 6, then correct them by
editing. If you have "lots" of these dates, then either clear them out, or
look at the records and decide what you want to do. We could just execute a
update query to change all dates prior to 1900 to 01/01/1900 for example.
what suggest is best here will depend on the data you have.

And, again, now do the same thing for dates in the future. Again decide what
you want to do with these records. You could simply build two update
queries, or perhaps you even delete these out of date range rows since you
not be able to tell what year they were supposed to be.

And, it not clear how many tables you have...if you have lots of tables,
then perhaps a bit of code to prompt you for the table name, and then that
code runs the two above queries would reduce the work load here....

So, check the date ranges you have...

 

[Arne Garvander]

Albert,
This query revealed one problem
SELECT Inventory.Number, Inventory.EventDate
FROM Inventory
WHERE Inventory.EventDate Is not Null and eventDate < #01/01/1900#;

I still get the error message Specifed scale 80 is invalid.
What does that mean?

 

[Albert D. Kallal]

Albert,
This query revealed one problem
SELECT Inventory.Number, Inventory.EventDate
FROM Inventory
WHERE Inventory.EventDate Is not Null and eventDate < #01/01/1900#;

Ok, so you have some bad data. You have to delete those records, or simply
execute a update query to remove the bad date values. Or, perhaps you want
to delete the records. Only you can guess/know which is the best choice
here, but you do need to fix those bad dates....

I still get the error message Specifed scale 80 is invalid.
What does that mean?

As mentioned, that likely has to do with a decimal field in ms-access. Do
you have any decimal fields in your table?
Scale "80" sounds like some corruption, or bad setting in your table. The
max possible scale value in access would be about 25-28. and in sql server,
it would be about 38. (the scale value is the number of decimals allowed in
that column).

Check all the number fields in that table. You want to look at any number
column that has the field size set as Decimal. what is the settings...are
they reasonable?

If you don't have any decimal number type columns, then this is good.
Perhaps this issue may well go away by fixing the dates as per our previous
post. (it takes 2 seconds to check if you have some decimal columns).

However, if you do have some decimal columns, check the total number of
digits. Access decimal columns can have up to 28 digits. The scale value
for that column says how many places will be used out of the 28 for columns
after the decimal point. However, sql server supports about 38 digits...so,
it not sure why that column is causing you trouble (if in fact you have any
decimal columns). However, a scale of 80 is just way out of a legal range.

I don't see why these data column types don't upsize, but then again we not
determined even if we do have any decimal columns here yet...you have to
check...

Hopefully you don't have any decimal columns. I would fix your date
column(s) first. Once you done that, then see if the msg goes away. Since
other tables without a date column are moving ok to sql server, then after
fixing your date column(s) there is a good chance the upsizing wizard will
work and you can move your data.

If you do have one decimal column, build an append query, and create a new
test table with that column omitted. See if that table then upsized. That
should tells which column is bad.

I would also do a compact and repair on the access table BEFORE you attempt
an upsize, as that scale value of 80 is just out of range for both access
and sql server.

 

[Arne Garvander]

Albert,
We are narrowing in on some problems. The only numbers I have are long
integers and integers. I have no decimals. The only column that is 80 is of
type text.
I have only two records with bad dates. I can fix those manually.

 

[Albert D. Kallal]

Albert,
We are narrowing in on some problems. The only numbers I have are long
integers and integers. I have no decimals. The only column that is 80 is
of
type text.
I have only two records with bad dates. I can fix those manually.

Ok, so fixing the bad dates sill causes the scale 80 other error? Correct?

Since we removed the bad dates, the next thing then would be to build a make
take query in access that sends the 1st 5 records to a NEW table

Now, try upsizing that NEW table with only the 5 records in it. And, of
course lets use the link table option. Don't use (or bother) the
client/server option in the upsizing wizard - that is likely to cause
problems with sql 2008.

So, just Upsize the one table you just made via the make table query with
those 5 records (just put a condition in the make table query to limit the
records you transfer.

Does this one test table with 5 records upsize? (how many records are in the
original table?). If the 5 records works, then delete the table, and then go
back to your above "make table" query..and send the 1st half of the original
table to this table, and try upsizing again. if it don't work, then try half
of that data again. (or if it does work, try double the amount. You should
only have to do this about 3-4 times to narrow down which record(s) have bad
data....(assuming that this upsize fails at all, or even works at all). If
the upsize don't work for 5 records, then it something with the table
structure. If the upsize works with the 5 records, then it is bad data that
causing the upsize to fail...

 

[Arne Garvander]

Albert,
Thanks for trying so hard.
Upsizing wizard still does not accept Sql 2008. I tried two diferent SQL
server drivers.
Export to ODBC gives the same error message, with a 5 record table, about
specified scale 80 is invalid.
Do you have a SQL server express database that you have tried with?
Do you mind passing you phone number for a small conversation?
The SQL server import wizard finally worked for an Access database after I
fixed my date problem.

 

[Albert D. Kallal]

Albert,
Thanks for trying so hard.
Upsizing wizard still does not accept Sql 2008.

Ok, so here what we have:

Upsizing wizard can't be used at all. Access complains

If we try to export a table, then access DOES allow this
(and thus after you exported, then you have to link the table).
(this can be done from inside of ms-access). However, you
must use an existing database already created on SQL 2008.

I tried two diferent SQL
server drivers.
Export to ODBC gives the same error message, with a 5 record table, about
specified scale 80 is invalid.

I can now reproduce the above, but ONLY for the New native driver.
So, if you create DSN and choose the "SQL server" driver, then I am able to
upload the table without problems at all.

In other words, if you choose the SQL native client, then you WILL get that
scale issue.

Do you have a SQL server express database that you have tried with?

In 2008 the upsizing wizards simply don't work.
However, I find that the "external data" tab, and then choose export and the
choose odbc option does work. And, you can also link to sql server tables
using the import options.

The only "got ya" is you have to create the data base BEFORE you attempt
this. So, just use the
Express studio tools to create a database BEFORE you do this. Then create a
new DSN during the export (or linking) process inside of ms-access.

Note that anything with a date column fails *unless* you use the "older"
original ODBC driver. that driver works fine for me to link + export (but
upsize does not work).

So for the two drivers we have:

SQL Server --
upsize tools don't work, but export and linking of tables does work
(you MUST create the sql database before you do this)

SQL Server Native Client 10.0 --

upsize tools don't work, and exporting a table with dates does not
work...

However, if you imported the tables with sql server tools, or used
"SQL server" above to export the data file, then you CAN USE this
driver to link to that table even with dates..

So, really, the best solution here is to avoid the Native Client ODBC
driver. You simply can't use the up-size tools from access with 2008.
There is some compatibility options for sql 2008, but I not tried them
as of yet...

So, as per above if you use the old driver (SQL Server), then you can link,
and even export tables from inside of ms-access (even with dates) to sql
server 2008.

 

[Arne Garvander]

Albert,
Thanks for your thorough investigation.