SSMA for Access fails to load table
Trying to convert .accdb table to SQL 2005. Standard table. Nothing fancy. I'm not too familiar with SSMA so it could be something I'm doing. I'm using the wizard that starts when you open SSMA. I get thru the conversion but the loading looks to be what's failing.
See the output below. Why is it doing this and how can I fix it?
See the output below. Why is it doing this and how can I fix it?
Starting conversion...
Analyzing metadata...
Converting table MLPartNo_backend_2009-10-08.[tblPartSpec] ...
Warnings: A2SS0030: Column 'desc' has a name that might cause problems for the Access application to function correctly against SQL Server.
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
Information: A2SS0029: Index name 'CODE NUMBER' was changed during conversion.
Warnings: A2SS0030: Index 'CODE NUMBER' has a name that might cause problems for the Access application to function correctly against SQL Server.
Information: A2SS0029: Index name 'IDMAX' was changed during conversion.
Information: A2SS0029: Index name 'IDMIN' was changed during conversion.
Information: A2SS0029: Index name 'IDNOM' was changed during conversion.
Information: A2SS0029: Index name 'IDTOL' was changed during conversion.
Information: A2SS0029: Index name 'PART NOWire AWG' was changed during conversion.
Warnings: A2SS0030: Index 'PART NOWire AWG' has a name that might cause problems for the Access application to function correctly against SQL Server.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'mlcat' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'customerpn' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'customer' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'code' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'wall' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'idmin' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'idmax' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'idnom' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'idtol' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'odmin' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'odmax' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'odnom' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'odtol' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'wallmin' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'wallmax' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'wallnom' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'walltol' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'lgthin' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'lgthtol' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'color' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'material' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'desc' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'comment' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'engrvw' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'qarvw' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'pull' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'flush' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'dry' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'trim' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'package' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'specinst' was converted to a check constraint.
Information: A2SS0020: New timestamp column 'SSMA_TimeStamp' created.
Conversion finished with 0 errors, 3 warnings, and 39 informational messages.
Loading objects into database...
Analyzing metadata...
Preparing table PARTSPEC.dbo.[tblPartSpec]...
Preparing category Triggers of table PARTSPEC.dbo.[tblPartSpec]...
Preparing category Indexes of table PARTSPEC.dbo.[tblPartSpec]...
Preparing index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$CODE NUMBER]...
Preparing index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDMAX]...
Preparing index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDMIN]...
Preparing index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDNOM]...
Preparing index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDTOL]...
Preparing index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$PART NOWire AWG]...
Starting Phase #0
Synchronizing PARTSPEC.dbo ...
Loading to database new table PARTSPEC.dbo.[tblPartSpec] ...
... sql execution failed
Loading to database new table PARTSPEC.dbo.[tblPartSpec] keys ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$CODE NUMBER] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDMAX] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDMIN] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDNOM] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDTOL] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$PART NOWire AWG] ...
... sql execution failed
Starting Phase #1
Loading to database new table PARTSPEC.dbo.[tblPartSpec] ...
... sql execution failed
Loading to database new table PARTSPEC.dbo.[tblPartSpec] keys ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$CODE NUMBER] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDMAX] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDMIN] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDNOM] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDTOL] ...
... sql execution failed
Loading to database new index PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$PART NOWire AWG] ...
... sql execution failed
Errors: The name "No" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Synchronization error: The name "No" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. On: PARTSPEC.dbo.[tblPartSpec]
Errors: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions.
Synchronization error: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions. On: PARTSPEC.dbo.[tblPartSpec]
Errors: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions.
Synchronization error: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions. On: PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$CODE NUMBER]
Errors: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions.
Synchronization error: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions. On: PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDMAX]
Errors: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions.
Synchronization error: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions. On: PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDMIN]
Errors: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions.
Synchronization error: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions. On: PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDNOM]
Errors: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions.
Synchronization error: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions. On: PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$IDTOL]
Errors: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions.
Synchronization error: Cannot find the object "dbo.tblPartSpec" because it does not exist or you do not have permissions. On: PARTSPEC.dbo.[tblPartSpec].[tblPartSpec$PART NOWire AWG]
Load operation is complete.
Migrating data...
Analyzing metadata...
Data migration complete.
0 table(s) successfully migrated.
0 table(s) partially migrated.
0 table(s) failed to migrate.
Operation canceled.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
no, I don't know where that's coming from....
It's obviously got something to do with tblPartSpec ... check that table, and compare it with the new table built on the SQL Server (i.e. check the column names, datatypes etc).
ASKER
what do you mean with the "new" table on the server? It's not creating the table...
When I open up SSMS and connect to the server there is no table in my DB. Not even just the structure...
When I open up SSMS and connect to the server there is no table in my DB. Not even just the structure...
ASKER
Found the problem - there was not a field named "no", but there was a default value set on one of my fields. The default value = no. I removed that and everything imported successfully.
Good catch! And, good to know ...
ASKER
I thought it could be permissions but I connected as SA and still had the same problem.