SSMA for Access - How can I figure out exactly what table and column or index these warning messages pertain to?

Question

SSMA for Access - How can I figure out exactly what table and column or index these warning messages pertain to?

A2SS0030 Identifier name might cause problems A2SS0030: Foreign key '{5A15D9A6-64CE-44B3-AD29-21EAF86F0328}' has a name that might cause problems for the Access application to function correctly against SQL Server.
A2SS0030 Identifier name might cause problems A2SS0030: Index '{5A15D9A6-64CE-44B3-AD29-21EAF86F0328}' has a name that might cause problems for the Access application to function correctly against SQL Server.
A2SS0030 Identifier name might cause problems A2SS0030: Foreign key '{9F6178FD-4B30-4727-BA95-0FED975D4D91}' has a name that might cause problems for the Access application to function correctly against SQL Server.
A2SS0030 Identifier name might cause problems A2SS0030: Index '{9F6178FD-4B30-4727-BA95-0FED975D4D91}' has a name that might cause problems for the Access application to function correctly against SQL Server.
A2SS0030 Identifier name might cause problems A2SS0030: Foreign key '{E4FC6A8B-64F9-4E78-8B90-445F2B9E44B1}' has a name that might cause problems for the Access application to function correctly against SQL Server.
A2SS0030 Identifier name might cause problems A2SS0030: Index '{E4FC6A8B-64F9-4E78-8B90-445F2B9E44B1}' has a name that might cause problems for the Access application to function correctly against SQL Server.

It would sure be great if Microsoft would include in messages such as these the table name and identifier/column name.

Bob

Answer 1

I have written it off to database corruption. I will proceed to use SSMA to convert to sql server and then remove the 15 or so erroneous indexes.

Bob

Answer 2

To close on this thread - Bob shared a sample repro with us and after investigation it was concluded that there is no corruption, but just the database setup this way.

First question is where do the GUID-named indexes come from? Every Foreign Key in Access gets an index auto-created for it. These indexes get the same name as the associated Foreign Keys. Access itself hides them, so you don't see them in the Access UI. We will update future versions of SSMA to do the same. Technically SQL Server does not require these indexes to be created for FKs, so we will let SQL Server to decide what is the most efficient way to manage FKs. Ultimately, what you have on the screenshot for tbl_Comments is:

• Indexes
  • {5A15D9... - auto-created index for the FK {5A15D9...
  • NamesID - user-defined index on NameID column
• Keys
  • {5A15D9... - foreign key from tbl_Comments to another table
  • PrimaryKey - primary key

Second question is why are foreign keys named as GUIDs? Database doesn't provide this information, they just were named this way. When you define a relationship in Access it will concatenate both table names and append an increasing integer, if necessary. If you create indexes using ACE/DAO API, then you name your relationship whatever you want. If you don't provide the name, then ACE will name it "Rel<guid>". Since these don't have "Rel" prefix, we assume they were manually created through ACE and were given these GUIDs as names.

With the next SSMA version what you will see is:

• Indexes
  • NamesID
• Keys
  • {5A15D9...
  • PrimaryKey

which is consistent with what you see/have in Access.

Hope this helps.

Answer 3

Mike. thanks. I sent an email to the azcommunity.

Also I was never able to locate " dma.exe.config or dmacmd.exe.config".

Albert Kallal, an Access MVP said he has never seen these errors.

Also the errors refer to the GUID, which I think is different from the original GUID shown in the Access info. I I could never tie it to a Primary Key or Index.

Bob

Answer 4

It would be very interesting if someone from Microsoft could advise how to interpret the error messages.

Bob

Answer 5

After extensive testing, it appears that the issue is table corruption. the only thing that worked was to copy the column definitions to a new table. I ran that with ssma and had no errors (no data).

Bob