Here is an annoying MSSQL server problem I just ran into. I am doing a data conversion for a client and I was given an Access DB and as an intermediary step, needed to get it into SQL server. There is a handy (snicker) import tool for doing just that. What should have been a few mouse clicks turned into a few hours of dueling.
As the import routine ran, it would consistantly fail at ‘Prepare to Execute’. The error message was:
- Prepare for Execute (Error)
Messages
Error 0xc0202009: {3EF444DD-70C9-4473-8294-BA4FFDD26927}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - Person_Medical_Practice_Pref" (4918) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)
No problem, it seems to be a connection problem. I’ll just verify the connection and run it again.
Failed again? Maybe I need to set a DB password and that will fix it
Ok, I can’t set a password unless I close the Access DB, open it again, select Open Exclusive, and Open. No problem, let me just do that and set the password. Verify the connection again… Verified. Perfect, now let me import the tables again…
Failed.
Same error
Maybe if I yell at it a few times it will work. Ok, maybe yelling doesn’t help… same error.
A few pages into a google search I find out there is a 64 table limit on importing.. Thank you guys/girls at SQL Junkies!
So, apart from the catharsis of writing this post, i’ll feel a lot better if posting this on the net helps someone else.
P.S. I am still scratching my head at the arbitrary 64 table limit.