February 5, 2008 at 11:04 pm
I'm trying to update an Access table from SQL through a Linked Server. This is part of an import to SQL process. This is the error I get... and I can't figure out what it means!!
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "My_LinkedServer" returned message "Invalid argument.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "My_LinkedServer" could not UPDATE table "[My_LinkedServer]...[My_AccessTable]"
February 5, 2008 at 11:46 pm
FYI
This is something i googled...
3001 - Invalid argument. This one typically happens when clicking on a corrupted record within a table. Or by running a query or form against that record. All the fields show #Error. See Damaged record(s) within tables. Another cause may be missing Access 2000 SR-1 if you are trying to " import data, copy a table, or run an append query "
2/6/2008
See You may receive an "Invalid argument" error message when you run an append query, run a make-table query, or import data in a large Access database file - 302495. Also see You may receive an "Invalid argument" error message when you run an append query in a large Access database file - 835416 which states that your MDB may be approaching the 2 Gb size limit. (Or 1 Gb for Access 97 MDBs.)
My_AccessTable has over 775,500 records, I cant individually locate damaged records.. and the DB is only 500MB.
So I'm guessing its the SR-1 that I need to install.
A few related links if anyone would need it 🙂
http://www.granite.ab.ca/access/corruption/corruptrecords.htm
February 5, 2008 at 11:53 pm
Can you post the update statement that you're trying to run?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2008 at 11:59 pm
UPDATE My_LinkedServer...My_AccessTable
Set ExportedFlag=0
This is just one of the several update queries that throws this error. In every case, its the same table that's causing the problem..
And like I said, this table has over 775,500 records..
Also,it contains memo fields.. if that makes a difference.
February 6, 2008 at 7:32 am
Since your dealing with an ACCESS databases have you tried using the compact and repair function?
Check MSDN for corruption ACCESS Database
Here is what I found that could be useful.
February 6, 2008 at 9:01 am
if you've defined the bit field to be nullable in SQL server, and have in fact allow null values in - Access will have all sorts of trouble with it. Bit fields in SQL map to Booleans in Access, which CANNOT be null, so if it runs into one - it will assume data corruption (since Access wouldn't allow that).
The only way to get around it is to run the query from SQL server to remove all null values (either defaulting it to 0 or 1).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2008 at 3:49 am
UPDATE My_LinkedServer...My_AccessTable
SET ID = @MaxID + T.Rank
FROM #Temp T
WHERE Address = T.a
gives me the same error, Matt!
And I did try the Compact and Repair tool... doesn't help!
There was an invalid date value in one of the date fields (2/2/707) which was preventing me from even reading the entire table (select * from..) so I'm not sure whether it was cuz of this I was unable to repair the Access DB.. it just went into a 'Not Responding' state during read as well as repair!
Now I've rectified the date issue so read isn't a prob... haven't tried repairing thereafter.
I still think it has something to do with the Service Release cuz the same DB and application runs fine on the client m/c... no issues whatsoever... I'm yet to check on the exact client side environment.
February 7, 2008 at 3:51 am
Or do you think it was this very record with date 2/2/707 that was the problem throughout??? Like that's an invalid argument, maybe!
Any thoughts??
February 7, 2008 at 8:10 am
If you have a corrupted memo field, compact & repair cannot fix that.
The usual fix is to locate the bad record, copy all fields from that record except the memo field into a temp table, delete the bad record, and then append from the temp table back to the original table. The contents of the memo field are lost unless you have a backup.
February 7, 2008 at 8:55 am
Question is - could you run that update from within Access itself?
OLE being what it is - there are some provider specific errors that don't get past back correctly. For example - the 32K total queries in an MDB lifetime bug (you can "exhaust" the ability to create queries in an Access MDB file, even if you just build, save, use and delete the queries when you're done).
Also - double-check that you don't have a rogue LDB file out there.
You should still be able to pull most of your data out. Just open another Access file and import what you can. On the table that's trouble - create a link to it, and import the table rows in batches (of 10000 for example). You should be able to get quite a lot of it, and it should tell you where the error is.
The date issue would be a problem, although in all likelihood should have shown up some other way than what you're seeing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2008 at 11:16 am
I just had a weird data problem between Access and SQL Server this week. It involved an ntext data type in a SQL Server table that was linked to an Access database. It looks like a Memo type field to the Access database.
Trying to update the memo field either using a bound form or by opening the table in datasheet mode resulted in an error stating that someone else had modified the data in the record. If I used an update query in Access it worked. Using any bound method - form or datasheet mode - would bring up the error.
I changed the ntext to varchar(255) and it would update properly in both form and datasheet mode. I chaned the column to varchar(1000) and it would again give the same error message on trying to update the Memo column (anything larger than varchar(255) becomes a Memo to Access.
I noticed some other columns in the table that were of a data type the I never use - REAL (becomes a Float in Access). I changed these on the SQL Server table to DEC(15, 4) and then re-linked the table in Access.
Voila! Updates to the memo field then worked with bound forms and datasheet mode.
There seems to be some problem in Access with certain data types being in the same table. I don't know exactly what it is, but this could be a problem that goes in both directions.
Todd Fifield
February 7, 2008 at 10:22 pm
Matt Miller (2/7/2008)
Question is - could you run that update from within Access itself?OLE being what it is - there are some provider specific errors that don't get past back correctly.
Yes, I could read and update in Access directly but not through SQL. What does that mean, now?
But, like I said... this problem persists on my end.. when I log into the client's m/c, everything works fine..
Also - double-check that you don't have a rogue LDB file out there.
What would the log file have to do with this? And how can I fix this?
The date issue would be a problem, although in all likelihood should have shown up some other way than what you're seeing.
Yes, you're right.. the date issue cropped up as "error converting data type DBTYPE_DBTIMESTAMP to datetime".
So now I don't have a corrupted memo field or an invalid date in Access... what next?
And.. thanks a lot guys...
February 7, 2008 at 10:28 pm
WILLIAM MITCHELL (2/7/2008)
If you have a corrupted memo field, compact & repair cannot fix that.The usual fix is to locate the bad record, copy all fields from that record except the memo field into a temp table, delete the bad record, and then append from the temp table back to the original table. The contents of the memo field are lost unless you have a backup.
William, how exactly do I locate a bad memo record when I have exactly a million records here? When I have to read from that table, I'm able to view upto 87,000 records... but thereafter it gives me the Invalid Argument error.. that does not neceassrily mean the corrupted record is the 87,001st record, right?
February 8, 2008 at 7:14 am
Here is one method to locate the bad record - using Access, open the table and loop thru all the records & print all the fields to the Immediate window. If a record or field is corrupted, Access will throw an error, so you would then inspect the immediate window - the last full record printed is the one just before the bad record. This could take quite some time for a million records.
Create a standard module and paste this in:
Sub VerifyTable(MyTable As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
For Each fld In rs.Fields
Debug.Print fld,
Next fld
Debug.Print
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
and then execute from the Immediate Window like this:
VerifyTable "your_table_name_goes_here"
February 8, 2008 at 12:44 pm
LDB file in Access = LOCK file not log file. That where it would track page locks on various things. it goes away when no one is using the database,so having one around continuously is the sign of ghosted/dropped connection that aren't getting cleaned up.
As to your question to William - it very possible that record #87001 is in fact ONE of the problem records. Point is - regardless of what the acutal issue is - you need to get around that row or those rows. If the data is "really" bad - you might not get anything past that row.
It might be the only one with an issue though. Assuming you have some way to do this - I'd exclude that record (say if you have an ID field and you know what that record should be). I've seen stranger things: it just get garbage in a single row and can't move past it. Deleting it or bypassing it allowed everything else to be recovered.
Simply running "delete * from mytable where mytable.id=87001" from an access query screen might be all you need. Just make a copy in case you need to go a plan B through Z.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply