0

I've been trying to insert data from one Access table to a linked SQL Server table using the following SQL statement from this question, modified for my purposes - but I keep getting 'Overflow' as an error message:

INSERT INTO dbo_tblGageActivity(strGageID, strCustJobNum, datDateEntered, datTimeEntered)
SELECT [Gage ID] as GageID, [Customer Job#] as JobNum, [Date] as DateEntered, 
[Time Entered] as TimeEntered FROM TblInstTrak;

I've tried this a number of ways, all resulting in the 'overflow' error. I must be missing something, but for the life of me, I don't know what. Is >100,000 records just too much for one Insert subquery to handle?

-- Edited 01/25/2011 @ 1540 hours --

The data types and sizes of the fields are as follows:

TblInstTrak          Type:Size     Required     dbo_tblGageActivity          Type:Size      Required
----------------------------------------------------------------------------------------------------
Gage ID              Text:50       True         strGageID                    Text:50        True
Customer Job#        Text:50       False        strCustJobNum                Text:50        True
Date                 Date/Time:8   False        datDateEntered               Date/Time:8    True
Time Entered         Date/Time:8   False        datTimeEntered               Date/Time:8    True

-- Edited 01/26/2011 @ 1355 hours --

Alright - I've been banging my head on this for a few days (before I came to SO with it, and the day it's been here) and the ONLY differences I can come up with is that the Date Entered field on the local app side is formatted to 'm/d/yyyy' and the Time Entered field on the local app side is formatted to 'Long Time' - neither of these fields in the SQL Server table have any format set.

For performance reasons, I am starting to wonder if there would be a better way to pull all the current data now to get the data into the table - then only pull updates from the local app into the mirror SQL Server copy. No data is entered into the SQL Server side copy, it's only used for performance for my test application - the only data that changes is on the local application side. Would this approach be easier than wiping the SQL Server database table and repopulating it every time I want to update it?

-- Edited 01/28/2011 @ 1236 hours --

After finding some dates from around the Roman Empire days (thanks David Fenton for pointing me onto this) I modified my import SQL statement to the following:

INSERT INTO dbo_tblGageActivity(strGageID, strCustJobNum, datDateEntered, datTimeEntered)
SELECT [Gage ID] as GageID, [Customer Job#] as JobNum, [Date] as DateEntered, 
[Time Entered] as TimeEntered FROM TblInstTrak WHERE [Date] >=1/1/1900;

I got a couple really great answers, and I can only chose one answer (mental anguish) so please don't get pissed - this really REALLY helped me learn.

Community
  • 1
  • 1
Comrad_Durandal
  • 651
  • 2
  • 10
  • 23
  • Are you able to insert any rows at all? – Matthew Jan 25 '11 at 21:39
  • 2
    What are the datatypes of your source and destination columns? – Joe Stefanelli Jan 25 '11 at 21:39
  • Nope, it works for about two minutes - then throws the overflow error - and nothing is the result in the destination table (dbo_tblGageActivity) – Comrad_Durandal Jan 25 '11 at 21:40
  • I don't think > 100,000 records should be a problem. I was able to do it for 8 Million records without any problem... – Chandu Jan 25 '11 at 21:41
  • If you change you `SELECT` to only return the `TOP 10` or something small... do the inserts process? – Matthew Jan 25 '11 at 21:41
  • @ Matthew PK - Yes, SELECT TOP 5000 runs fine. – Comrad_Durandal Jan 25 '11 at 22:01
  • The data type summary is all right. But could you elaborate on what the actual types for date/time values are on the SQL Server side? Could it be that they are `smalldatetime` while you are trying to insert some values out of this type's range? – Andriy M Jan 26 '11 at 10:54
  • @ Andriy M - The 'Time Entered' field has a Long Time format set - other than that - it's identical to the TimeEntered field on the SQL Server side. – Comrad_Durandal Jan 26 '11 at 16:41
  • @Comrad_Durandal just a shot in the dark here... what if you `SELECT TOP x` where x is some value larger than the total rows you expect? – Matthew Jan 26 '11 at 19:30
  • Access formats are for display only, and have no effect whatsoever on data storage. – David-W-Fenton Jan 26 '11 at 22:58

3 Answers3

3

The overflow occurs because one or more of the values in the rows selected does not fit into the table you're trying to insert. In other words, in the TblInstTrak you have values that do not fit into their respective columns in dbo_tblGageActivity. Compare the definitions of the two tables and make sure the destination table columns are wide enough to accommodate the data from your source table.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • The problem is that I did - the columns in both are identical. The SQL Server table has a unique ID column which the original table did not. If I limit the SELECT subquery - it works, if I don't - it barfs. – Comrad_Durandal Jan 25 '11 at 22:31
1

If you have linked it using OLEDB provider, there is a property for the maximum insert commit size See more details here: http://www.bidn.com/blogs/ShawnHarrison/ssis/803/maximum-insert-commit-size

Matthew
  • 10,244
  • 5
  • 49
  • 104
1

Do you have date values in Access that are outside the range of the SQL Server date/time field? That's a very, very common issue I encounter when upsizing old Access databases to SQL Server -- lots of finger errors with dates that were actually during the late Roman Empire!

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • I went through the source data - and there are four items that have 'wonky dates', 1/4/111, 5/4/111, 12/1/804, 6/1/822. A few have dates in the early 20th century, but shouldn't it just import them as is - as they were valid in the local database? – Comrad_Durandal Jan 26 '11 at 23:22
  • 1
    @Comrad_Durandal: The lower date bound for `smalldatetime` is 1/1/1900, for `datetime` it's 1/1/1753. As you can see, your 'wonky dates' fit in with neither of the two. So they are very likely the cause of your problem. – Andriy M Jan 27 '11 at 06:54
  • @ Andriy M - How do I prevent the import of rows like these, while being able to import the rows that are normal? – Comrad_Durandal Jan 27 '11 at 16:35
  • 1
    I usually edit the problem data to fix the bad dates. In most cases with the data I've worked with, it's possible to figure out the intended date from other data within the record or from records created about the same time. – David-W-Fenton Jan 28 '11 at 23:25