problem with SQL Server Migration Assistant for Access

I'm trying to upsize a client's Access 2003 database to SQL 2005.
I've been trying to use MS's SQL Server Migration Assistant for Access (which
seems to work better than the Access 2007 upsizing wizard), but I've got a few
errors that I'm stumped on.
Out of maybe 40 tables, all copied over and linked, except for issues with 2
tables.
One table showed erors when copying the data, none of the data made it.
Another table copied 95% of the rows, but failed on 5%.
The error log showed this for the table that completely failed:
--
Preparing table theDb.[Job_Budgets]...
Migrating data for the table Job_Budgets from the database theDb...
Error occurred during data migration.
See the log for the detailed information.
Errors: Error occurred during data migration.
See the log for the detailed information.
Data migration complete: 0 rows of 19394 migrated.
--
I've looked at the only .log files created during this time, and none give me
any useful information as to why the data failed to be copied.
There is a primary key, no duplicate records, etc.
How can I determine where the problem lies?
--
Thanks in advance, Les CaudleHello Les,
To isolate the issue, I suggest that you try to imort the table directly by
using Import wizard in management studio. If you still encounter issues,
please provide me with a sample Access database so that I could reproduce
on my side.
Please remove "online" from my displayed email address. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter - yes, that is giving me more info, and I'm able to resolve issues with
one table.
However, on a 2nd table, I'm getting this error msg:
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB
error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)'
returned invalid data for column '[!BulkInsert].Dep1_%'. ".
(SQL Server Import and Export Wizard)
Dep1_% in Access 2003 (access 2000 db format) is a number, field size: double,
required = no, decimal places = 2.
So, somewhere in the table this field has some invalid values.
How can I find those and correct for them? Hard to write a query to locate
something invalid (which should never be there to begin with. You'd think
compact and repair would deal with those values).
Thanks, Les Caudle
On Fri, 26 Oct 2007 05:47:41 GMT, petery@.online.microsoft.com ("Peter
Yang[MSFT]") wrote:
>Hello Les,
>To isolate the issue, I suggest that you try to imort the table directly by
>using Import wizard in management studio. If you still encounter issues,
>please provide me with a sample Access database so that I could reproduce
>on my side.
>Please remove "online" from my displayed email address. Thank you.
>Best Regards,
>Peter Yang
>MCSE2000/2003, MCSA, MCDBA
>Microsoft Online Community Support
>==================================================>Get notification to my posts through email? Please refer to
>http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>ications
><http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
>Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
>where an initial response from the community or a Microsoft Support
>Engineer within 1 business day is acceptable. Please note that each follow
>up response may take approximately 2 business days as the support
>professional working with you may need further investigation to reach the
>most efficient resolution. The offering is not appropriate for situations
>that require urgent, real-time or phone-based interactions or complex
>project analysis and dump analysis issues. Issues of this nature are best
>handled working with a dedicated Microsoft Support Engineer by contacting
>Microsoft Customer Support Services (CSS) at
><http://msdn.microsoft.com/subscriptions/support/default.aspx>.
>==================================================>This posting is provided "AS IS" with no warranties, and confers no rights.|||Less,
The problem is the name of the column. You cannot use % in a column
name.
Robert
On Oct 26, 11:00 am, Les Caudle <DotNetWann...@.newsgroup.nospam>
wrote:
> Peter - yes, that is giving me more info, and I'm able to resolve issues with
> one table.
> However, on a 2nd table, I'm getting this error msg:
> Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
> 0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)'
> returned invalid data for column '[!BulkInsert].Dep1_%'. ".
> (SQL Server Import and Export Wizard)
> Dep1_% in Access 2003 (access 2000 db format) is a number, field size: double,
> required = no, decimal places = 2.
> So, somewhere in the table this field has some invalid values.
> How can I find those and correct for them? Hard to write a query to locate
> something invalid (which should never be there to begin with. You'd think
> compact and repair would deal with those values).
> Thanks, Les Caudle|||Robert - yes, very strange column name, but that actually was not the problem.
Apparently Access, even with compact and repair, will allow invalid values
(garbage) to to stored.
I cleared out the ones I could see by sorting the column asc desc by hand, and
resolved the problem.
One more reason to get my client away from storing his data in Access!
BUT - I wish there were an automated way to located and fix the invalid data in
his database. I found invalid data all over the place, but had to remove it all
by hand.
Regards, Les Caudle
On Fri, 26 Oct 2007 14:32:35 -0700, raibeart <raibeart@.gmail.com> wrote:
>Less,
>The problem is the name of the column. You cannot use % in a column
>name.
>Robert
>On Oct 26, 11:00 am, Les Caudle <DotNetWann...@.newsgroup.nospam>
>wrote:
>> Peter - yes, that is giving me more info, and I'm able to resolve issues with
>> one table.
>> However, on a 2nd table, I'm getting this error msg:
>> Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB
>> error has occurred. Error code: 0x80004005.
>> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
>> 0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)'
>> returned invalid data for column '[!BulkInsert].Dep1_%'. ".
>> (SQL Server Import and Export Wizard)
>> Dep1_% in Access 2003 (access 2000 db format) is a number, field size: double,
>> required = no, decimal places = 2.
>> So, somewhere in the table this field has some invalid values.
>> How can I find those and correct for them? Hard to write a query to locate
>> something invalid (which should never be there to begin with. You'd think
>> compact and repair would deal with those values).
>> Thanks, Les Caudle|||Hello Les,
I think there is no automated method way to clean invalid data currently.
Please rest assured that your feedback on this is routed to the product
team. Thank you.
Best Regards,
Peter Yang
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security