• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1868
  • Last Modified:

SQL Server Migration Assistant for Access Question

I am using SQL Server Migration Assistant for Access and while migrating tables I am getting following errors (pulled from ssma.log):

Table 1 rises following error:

[Datamigrator: Error] [796/8] [2008-11-06 13:06:13]: Exception: Cannot insert duplicate key row in object 'dbo.E-mail addresses' with unique index 'E-mail addresses$Customer e-mail'.
The statement has been terminated.
 site: Void OnError(System.Data.SqlClient.SqlException, Boolean)
 source: .Net SqlClient Data Provider
 error code: -2146232060
 data: [HelpLink.ProdName] = [Microsoft SQL Server]
 data: [HelpLink.ProdVer] = [09.00.1399]
 data: [HelpLink.EvtSrc] = [MSSQLServer]
 data: [HelpLink.EvtID] = [2601]
 data: [HelpLink.BaseHelpUrl] = [http://go.microsoft.com/fwlink]
 data: [HelpLink.LinkId] = [20476]
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at Microsoft.SSMA.Framework.Access.SqlServer.DataMigration.TableDataMigrator.ProcessNode(IDataMigrationContext context, DataMigrationState state, XNode node)

Table 2 raises following errors:

[Datamigrator: Error] [5248/5] [2008-11-11 11:16:47]: Exception: Column 'Signing up for' does not allow DBNull.Value.
 site: System.Object ConvertValue(System.Object, System.Data.SqlClient._SqlMetaData)
 source: System.Data
   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at Microsoft.SSMA.Framework.Access.SqlServer.DataMigration.TableDataMigrator.ProcessNode(IDataMigrationContext context, DataMigrationState state, XNode node)
[Datamigrator: Error] [5248/5] [2008-11-11 11:17:05]: Error occurred during migration of the current batch.
See the log for the detailed information.
[Datamigrator: Error] [5248/5] [2008-11-11 11:17:05]: Exception: Not a legal OleAut date.
 site: Int64 DoubleDateToTicks(Double)
 source: mscorlib
   at System.DateTime.DoubleDateToTicks(Double value)
   at System.DateTime.FromOADate(Double d)
   at System.Data.OleDb.ColumnBinding.Value_DATE()
   at System.Data.OleDb.ColumnBinding.Value()
   at System.Data.OleDb.OleDbDataReader.GetValue(Int32 ordinal)
   at System.Data.SqlClient.SqlBulkCopy.GetValueFromSourceRow(Int32 columnOrdinal, _SqlMetaData metadata, Int32[] UseSqlValue, Int32 destRowIndex)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at Microsoft.SSMA.Framework.Access.SqlServer.DataMigration.TableDataMigrator.ProcessNode(IDataMigrationContext context, DataMigrationState state, XNode node)

Thanks for all help!
0
itconsultant1
Asked:
itconsultant1
  • 2
1 Solution
 
jmoss111Commented:
Hi itconsultant1,

Table1
It appears that you're trying to insert a duplicate value in a unique column in dbo.E-mailaddresses.

Table2
Column Signing up for does not allow NULL values and my guess is that your trying to insert a NULL value in that column.

Exception: Not a legal OleAut date.
 site: Int64 DoubleDateToTicks(Double) Looks like a date type conversion error.

I think that you data need cleansing with attention paid to what ha to be unique, ensure that all values required are populated, and that dates are truly valid dates.

0
 
jmoss111Commented:
Hi itconsultant1, How's it going debugging the SSMA?

Jim
0
 
itconsultant1Author Commented:
Hi jmoss111, it is going slow, I will do a follow up in 3 days. Thanks for checking in!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now