"Invalid argument."

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » "Invalid argument."

15 posts, Page 1 of 2

1 2 »»

"Invalid argument."

Rate Topic

Display Mode

Topic Options

Author

Message

Nisha-475382

Posted Tuesday, February 05, 2008 11:04 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364

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]"

Post #452008

Nisha-475382

Posted Tuesday, February 05, 2008 11:46 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364

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
http://support.microsoft.com/?id=835416
http://support.microsoft.com/?id=302495

Post #452024

GilaMonster

Posted Tuesday, February 05, 2008 11:53 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 38,084, Visits: 30,378

Can you post the update statement that you're trying to run?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #452027

Nisha-475382

Posted Tuesday, February 05, 2008 11:59 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364

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.

Post #452030

Smendle

Posted Wednesday, February 06, 2008 7:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:34 PM
Points: 39, Visits: 258

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.
http://support.microsoft.com/kb/279334/

Post #452201

Matt Miller (#4)

Posted Wednesday, February 06, 2008 9:01 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:45 PM
Points: 7,002, Visits: 13,999

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?

Post #452285

Nisha-475382

Posted Thursday, February 07, 2008 3:49 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364

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.

Post #452627

Nisha-475382

Posted Thursday, February 07, 2008 3:51 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364

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??

Post #452630

WILLIAM MITCHELL

Posted Thursday, February 07, 2008 8:10 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 1,442, Visits: 2,499

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.

Post #452761

Matt Miller (#4)

Posted Thursday, February 07, 2008 8:55 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:45 PM
Points: 7,002, Visits: 13,999

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?

Post #452796

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions