I'm trying to move several Access Tables from a moderate sized database to SQL Server 2000. Most of the tables will transfer using the Upgrade Wizard in Access 2003, however, there are a couple of tables that will not transfer. The wizard reports at the end of the upgrad, "table skipped" or "overflow"
I cannot find in the help section why this happens. I would appreciate any help you send my way.
Thanks,
Joe| I'm trying to move several Access Tables from a moderate sized database
| to SQL Server 2000. Most of the tables will transfer using the Upgrade
Wizard
| in Access 2003, however, there are a couple of tables that will not
transfer.
| The wizard reports at the end of the upgrad, "table skipped" or "overflow"
--
Hi Joe,
One known issue that causes the "table skipped" error are column names that
contain symbols such as quotes, commas and slashes. SQL Server doesn't
support all symbols for column names. If you rename such "odd" column
names, the upsize wizard should work again. Also a known issue is:
ACC2000: Tables Not Upsized When Query or Table Name Contains Apostrophe
http://support.microsoft.com/?id=216218
If all else fails, you can use Data Transformation Services (DTS) to export
to SQL Server:
ACC2000: How to Use Data Transformation Services (DTS) to Export Data from
a Microsoft Access Database to a SQL Server Database
http://support.microsoft.com/?id=250616
Hope this helps,
--
Eric Cárdenas
SQL Server support
Showing posts with label overflow. Show all posts
Showing posts with label overflow. Show all posts
Tuesday, March 6, 2012
"Overflow!" error when Upsizing from Access
Hi
I've just installed MSDE 2000 Release A on WinXP Pro. All seems okay, but when I try to upsize an Access Database, I get a message box saying "Overflow!".
Any ideas on what I'm doing wrong? I'm new to SQL Server
Cheers
Dan
It depends on which version of Access you are using -- see
http://support.microsoft.com/default...;en-us;q294407 for
the Access 2002 upsizing whitepaper -- there's a link to the A2000 one
if you're using that.
--Mary
On Sun, 1 Aug 2004 08:37:02 -0700, "Dan Nash" <dan@.musoswire.co.uk>
wrote:
>Hi
>I've just installed MSDE 2000 Release A on WinXP Pro. All seems okay, but when I try to upsize an Access Database, I get a message box saying "Overflow!".
>Any ideas on what I'm doing wrong? I'm new to SQL Server
>Cheers
>
>Dan
|||Mary,
Thanks, am downloading that now. Forgot to mention I'm using Access 2000.
One question though.. when you install, what's the default login you would use? I used the SAPWD switch to create my password, but what's the Login ID for a default instance? May seem like a silly question, but it might be what's causing my problems!
Cheers
Dan
"Mary Chipman" wrote:
> It depends on which version of Access you are using -- see
> http://support.microsoft.com/default...;en-us;q294407 for
> the Access 2002 upsizing whitepaper -- there's a link to the A2000 one
> if you're using that.
> --Mary
> On Sun, 1 Aug 2004 08:37:02 -0700, "Dan Nash" <dan@.musoswire.co.uk>
> wrote:
>
>
|||I'm a little rusty on A2k, but as I recall you have to use a SQL login
and password with the upwiz, not integrated security (the whitepaper
should discuss this). It's a valid login that you create. FWIW,
upgrading to Access 2003 is highly recommended if you're going to keep
an Access FE to a SQLS BE. A2k was released *before* SQLS 2k, and
there have been many improvements and bug fixes since then.
--Mary
On Sun, 1 Aug 2004 13:49:03 -0700, "Dan Nash" <dan@.musoswire.co.uk>
wrote:
[vbcol=seagreen]
>Mary,
>Thanks, am downloading that now. Forgot to mention I'm using Access 2000.
>One question though.. when you install, what's the default login you would use? I used the SAPWD switch to create my password, but what's the Login ID for a default instance? May seem like a silly question, but it might be what's causing my problems!
>Cheers
>
>Dan
>"Mary Chipman" wrote:
I've just installed MSDE 2000 Release A on WinXP Pro. All seems okay, but when I try to upsize an Access Database, I get a message box saying "Overflow!".
Any ideas on what I'm doing wrong? I'm new to SQL Server
Cheers
Dan
It depends on which version of Access you are using -- see
http://support.microsoft.com/default...;en-us;q294407 for
the Access 2002 upsizing whitepaper -- there's a link to the A2000 one
if you're using that.
--Mary
On Sun, 1 Aug 2004 08:37:02 -0700, "Dan Nash" <dan@.musoswire.co.uk>
wrote:
>Hi
>I've just installed MSDE 2000 Release A on WinXP Pro. All seems okay, but when I try to upsize an Access Database, I get a message box saying "Overflow!".
>Any ideas on what I'm doing wrong? I'm new to SQL Server
>Cheers
>
>Dan
|||Mary,
Thanks, am downloading that now. Forgot to mention I'm using Access 2000.
One question though.. when you install, what's the default login you would use? I used the SAPWD switch to create my password, but what's the Login ID for a default instance? May seem like a silly question, but it might be what's causing my problems!
Cheers
Dan
"Mary Chipman" wrote:
> It depends on which version of Access you are using -- see
> http://support.microsoft.com/default...;en-us;q294407 for
> the Access 2002 upsizing whitepaper -- there's a link to the A2000 one
> if you're using that.
> --Mary
> On Sun, 1 Aug 2004 08:37:02 -0700, "Dan Nash" <dan@.musoswire.co.uk>
> wrote:
>
>
|||I'm a little rusty on A2k, but as I recall you have to use a SQL login
and password with the upwiz, not integrated security (the whitepaper
should discuss this). It's a valid login that you create. FWIW,
upgrading to Access 2003 is highly recommended if you're going to keep
an Access FE to a SQLS BE. A2k was released *before* SQLS 2k, and
there have been many improvements and bug fixes since then.
--Mary
On Sun, 1 Aug 2004 13:49:03 -0700, "Dan Nash" <dan@.musoswire.co.uk>
wrote:
[vbcol=seagreen]
>Mary,
>Thanks, am downloading that now. Forgot to mention I'm using Access 2000.
>One question though.. when you install, what's the default login you would use? I used the SAPWD switch to create my password, but what's the Login ID for a default instance? May seem like a silly question, but it might be what's causing my problems!
>Cheers
>
>Dan
>"Mary Chipman" wrote:
Monday, February 13, 2012
"error 220: arithmetic overflow" in publication
I am unable to alter or create a publication that uses a particular
table. If I try to create a publication with this table or if I try
to add or remove a column from my existing publication that uses this
table, i get the error message "error 220: arithmetic overflow error
for datatype tinyint, value = -9."
There are no fields of "tinyint" type in this table. In fact, I've
looked at all the tinyint field values throughout the entire datbase
(system tables included) and none of them have -9 for a value.
I am able to add or remove columns from other articles in this
publication.
Anyone know some things I can check? Thanks.
Can you post the schema of the table here?
Are you from Alberta? I was born there
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<thatoneguyfromab@.gmail.com> wrote in message
news:1173891171.439464.193680@.d57g2000hsg.googlegr oups.com...
>I am unable to alter or create a publication that uses a particular
> table. If I try to create a publication with this table or if I try
> to add or remove a column from my existing publication that uses this
> table, i get the error message "error 220: arithmetic overflow error
> for datatype tinyint, value = -9."
> There are no fields of "tinyint" type in this table. In fact, I've
> looked at all the tinyint field values throughout the entire datbase
> (system tables included) and none of them have -9 for a value.
> I am able to add or remove columns from other articles in this
> publication.
> Anyone know some things I can check? Thanks.
>
|||some names have been changed to protect the innocent, i hope this is
useful to you
no, I've never been to Alberta ;)
FieldTypeLengthPrecision
field1money19
field2nvarchar50
field3nvarchar50
field4nvarchar50
field5money19
field6datetime23
field7money19
field8datetime23
field9float53
field10money19
field11money19
field12money19
field13money19
field14nvarchar50
field15numeric18
field16smallint5
field17smallint5
field18money19
field19money19
field20int10
field21money19
field22nvarchar50
field23money19
field24money19
field25nvarchar50
field26nvarchar50
field27money19
field28money19
field29nvarchar50
field30nvarchar50
field31nvarchar50
field32ntext1073741823
field33datetime23
field34datetime23
field35datetime23
field36ntext1073741823
field37float53
field38nvarchar50
field39ntext1073741823
field40nvarchar50
field41ntext1073741823
field42nvarchar50
field43nvarchar50
field44nvarchar50
field45datetime23
field46nvarchar50
field47nvarchar50
field48nvarchar50
field49ntext1073741823
field50money19
field51datetime23
field52float53
field53money19
field54money19
field55money19
field56money19
field57nvarchar50
field58nvarchar50
field59money19
field60smallint5
field61money19
field62smallint5
field63money19
field64money19
field65money19
field66money19
field67money19
field68datetime23
field69float53
field70money19
field71money19
field72money19
field73money19
field74numeric18
field75numeric18
field76money19
field77smallint5
field78money19
field79smallint5
field80money19
field81money19
field82int10
field83money19
field84numeric18
field85numeric18
field86numeric18
field87money19
field88nvarchar50
field89numeric18
field90nvarchar50
field91ntext1073741823
field92ntext1073741823
field93nvarchar50
field94nvarchar50
field95datetime23
field96nvarchar50
field97ntext1073741823
field98datetime23
field99money19
field100money19
field101money19
field102datetime23
field103float53
field104money19
field105money19
field106money19
field107money19
field108numeric18
field109numeric18
field110money19
field111smallint5
field112money19
field113smallint5
field114money19
field115money19
field116int10
field117money19
field118nvarchar50
field119nvarchar50
field120nvarchar50
field121nvarchar50
field122ntext1073741823
field123ntext1073741823
field124nvarchar50
field125nvarchar50
field126ntext1073741823
field127numeric18
field128money19
field129ntext1073741823
field130datetime23
field131ntext1073741823
field132nvarchar50
field133nvarchar50
field134nvarchar50
field135nvarchar50
field136varchar50
field137datetime23
field138nvarchar50
field139nvarchar50
field140nvarchar50
field141ntext1073741823
field142nvarchar50
field143nvarchar50
field144nvarchar50
field145ntext1073741823
field146nvarchar50
field147ntext1073741823
field148ntext1073741823
field149ntext1073741823
field150ntext1073741823
field151datetime23
field152ntext1073741823
field153datetime23
field154datetime23
field155nvarchar50
field156nvarchar50
field157nvarchar50
field158numeric18
field159nvarchar50
field160nvarchar50
field161ntext1073741823
field162int10
field163bit1
field164text2147483647
field165bit1
field166ntext1073741823
field167nvarchar50
field168nvarchar128
table. If I try to create a publication with this table or if I try
to add or remove a column from my existing publication that uses this
table, i get the error message "error 220: arithmetic overflow error
for datatype tinyint, value = -9."
There are no fields of "tinyint" type in this table. In fact, I've
looked at all the tinyint field values throughout the entire datbase
(system tables included) and none of them have -9 for a value.
I am able to add or remove columns from other articles in this
publication.
Anyone know some things I can check? Thanks.
Can you post the schema of the table here?
Are you from Alberta? I was born there
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<thatoneguyfromab@.gmail.com> wrote in message
news:1173891171.439464.193680@.d57g2000hsg.googlegr oups.com...
>I am unable to alter or create a publication that uses a particular
> table. If I try to create a publication with this table or if I try
> to add or remove a column from my existing publication that uses this
> table, i get the error message "error 220: arithmetic overflow error
> for datatype tinyint, value = -9."
> There are no fields of "tinyint" type in this table. In fact, I've
> looked at all the tinyint field values throughout the entire datbase
> (system tables included) and none of them have -9 for a value.
> I am able to add or remove columns from other articles in this
> publication.
> Anyone know some things I can check? Thanks.
>
|||some names have been changed to protect the innocent, i hope this is
useful to you
no, I've never been to Alberta ;)
FieldTypeLengthPrecision
field1money19
field2nvarchar50
field3nvarchar50
field4nvarchar50
field5money19
field6datetime23
field7money19
field8datetime23
field9float53
field10money19
field11money19
field12money19
field13money19
field14nvarchar50
field15numeric18
field16smallint5
field17smallint5
field18money19
field19money19
field20int10
field21money19
field22nvarchar50
field23money19
field24money19
field25nvarchar50
field26nvarchar50
field27money19
field28money19
field29nvarchar50
field30nvarchar50
field31nvarchar50
field32ntext1073741823
field33datetime23
field34datetime23
field35datetime23
field36ntext1073741823
field37float53
field38nvarchar50
field39ntext1073741823
field40nvarchar50
field41ntext1073741823
field42nvarchar50
field43nvarchar50
field44nvarchar50
field45datetime23
field46nvarchar50
field47nvarchar50
field48nvarchar50
field49ntext1073741823
field50money19
field51datetime23
field52float53
field53money19
field54money19
field55money19
field56money19
field57nvarchar50
field58nvarchar50
field59money19
field60smallint5
field61money19
field62smallint5
field63money19
field64money19
field65money19
field66money19
field67money19
field68datetime23
field69float53
field70money19
field71money19
field72money19
field73money19
field74numeric18
field75numeric18
field76money19
field77smallint5
field78money19
field79smallint5
field80money19
field81money19
field82int10
field83money19
field84numeric18
field85numeric18
field86numeric18
field87money19
field88nvarchar50
field89numeric18
field90nvarchar50
field91ntext1073741823
field92ntext1073741823
field93nvarchar50
field94nvarchar50
field95datetime23
field96nvarchar50
field97ntext1073741823
field98datetime23
field99money19
field100money19
field101money19
field102datetime23
field103float53
field104money19
field105money19
field106money19
field107money19
field108numeric18
field109numeric18
field110money19
field111smallint5
field112money19
field113smallint5
field114money19
field115money19
field116int10
field117money19
field118nvarchar50
field119nvarchar50
field120nvarchar50
field121nvarchar50
field122ntext1073741823
field123ntext1073741823
field124nvarchar50
field125nvarchar50
field126ntext1073741823
field127numeric18
field128money19
field129ntext1073741823
field130datetime23
field131ntext1073741823
field132nvarchar50
field133nvarchar50
field134nvarchar50
field135nvarchar50
field136varchar50
field137datetime23
field138nvarchar50
field139nvarchar50
field140nvarchar50
field141ntext1073741823
field142nvarchar50
field143nvarchar50
field144nvarchar50
field145ntext1073741823
field146nvarchar50
field147ntext1073741823
field148ntext1073741823
field149ntext1073741823
field150ntext1073741823
field151datetime23
field152ntext1073741823
field153datetime23
field154datetime23
field155nvarchar50
field156nvarchar50
field157nvarchar50
field158numeric18
field159nvarchar50
field160nvarchar50
field161ntext1073741823
field162int10
field163bit1
field164text2147483647
field165bit1
field166ntext1073741823
field167nvarchar50
field168nvarchar128
Saturday, February 11, 2012
"Datetime field overflow" w/ MAX in subselect?
Hey guys--hoping you can help with a problematic query. When I run it, I get an error, copied here from the ColdFusion application of which it's a part: "ODBC Error Code = 22008 (Datetime field overflow) [MERANT][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type."
And here's the query:
SELECT *
FROM Stories
WHERE (StoryID IN
(SELECT MAX(PublishTime) AS PublishTime
FROM Stories
WHERE (Fixture_Code IN ('test1', 'test2'))
GROUP BY Fixture_Code))
The subselect works great when run by itself; it's only when it's a subselect that I get type errors. PublishTime is a smalldatetime column. The above query actually runs without an error if my values for WHERE IN are values that don't exist in the the Fixture_Code column anywhere--obviously I get back an empty set, but no error.
In case it's not clear, I'm trying in the subselect to pick the single most recent record for each of a list of fixture codes, so that I can then go back and get full information on each of those stories. If someone can tell me a better way to, essentially, do a query where the result set contains only one row (the most recent) for each of my "fixture codes", I'm open to that too.
Thanks again for any help.The error occurs because you are comparing PublishTime (smalldatetime) from the subselect with StoryID (I assume integer or character). Like compare apples to oranges. You could try this:
SELECT s.*
FROM Stories s,
(
SELECT MAX(PublishTime) AS PublishTime,
Fixture_Code
FROM Stories
GROUP BY Fixture_Code
) x
WHERE s.Fixture_Code = x.Fixture_Code
AND s.PublishTime = x.PublishTimeSo this will return the most recent record for each Fixture_Code.|||Ahh, okay. That makes perfect sense. Your code works great... thanks a lot for the help; as you may have guessed I'm still learning the elusive art of subselects :)|||Although, when I tweak the query to specify only the list of fixtures I want, plus bring in another table join to get a fixture name in addition to the fixture code, I get one row that's duplicated in the results.
SELECT s.*, Fixture_Name
FROM Stories s INNER JOIN
(SELECT MAX(PublishTime) AS PublishTime, Fixture_Code
FROM Stories
GROUP BY Fixture_Code) x
JOIN FixtureCodes ON x.Fixture_Code = FixtureCodes.Fixture_ID
ON s.Fixture_Code = x.Fixture_Code AND s.PublishTime = x.PublishTime AND s.Fixture_Code IN ('test1','test2','test3','test4','test5','test6',' test7','test8')
Very easily taken care of with DISTINCT but if anyone cared to further edify me with the reason that's happening, I'd be grateful :) Thanks again to all.
And here's the query:
SELECT *
FROM Stories
WHERE (StoryID IN
(SELECT MAX(PublishTime) AS PublishTime
FROM Stories
WHERE (Fixture_Code IN ('test1', 'test2'))
GROUP BY Fixture_Code))
The subselect works great when run by itself; it's only when it's a subselect that I get type errors. PublishTime is a smalldatetime column. The above query actually runs without an error if my values for WHERE IN are values that don't exist in the the Fixture_Code column anywhere--obviously I get back an empty set, but no error.
In case it's not clear, I'm trying in the subselect to pick the single most recent record for each of a list of fixture codes, so that I can then go back and get full information on each of those stories. If someone can tell me a better way to, essentially, do a query where the result set contains only one row (the most recent) for each of my "fixture codes", I'm open to that too.
Thanks again for any help.The error occurs because you are comparing PublishTime (smalldatetime) from the subselect with StoryID (I assume integer or character). Like compare apples to oranges. You could try this:
SELECT s.*
FROM Stories s,
(
SELECT MAX(PublishTime) AS PublishTime,
Fixture_Code
FROM Stories
GROUP BY Fixture_Code
) x
WHERE s.Fixture_Code = x.Fixture_Code
AND s.PublishTime = x.PublishTimeSo this will return the most recent record for each Fixture_Code.|||Ahh, okay. That makes perfect sense. Your code works great... thanks a lot for the help; as you may have guessed I'm still learning the elusive art of subselects :)|||Although, when I tweak the query to specify only the list of fixtures I want, plus bring in another table join to get a fixture name in addition to the fixture code, I get one row that's duplicated in the results.
SELECT s.*, Fixture_Name
FROM Stories s INNER JOIN
(SELECT MAX(PublishTime) AS PublishTime, Fixture_Code
FROM Stories
GROUP BY Fixture_Code) x
JOIN FixtureCodes ON x.Fixture_Code = FixtureCodes.Fixture_ID
ON s.Fixture_Code = x.Fixture_Code AND s.PublishTime = x.PublishTime AND s.Fixture_Code IN ('test1','test2','test3','test4','test5','test6',' test7','test8')
Very easily taken care of with DISTINCT but if anyone cared to further edify me with the reason that's happening, I'd be grateful :) Thanks again to all.
Subscribe to:
Posts (Atom)
