[dba-SQLServer] Upsizing Access data into SQL2005 using SSMA

pcs at azizaz.com pcs at azizaz.com
Sun May 27 01:41:19 CDT 2007


Hi Group,
I am doing my second upgrade of an Access Db to 2005SQL.

In this second upgrade project I've used the SQL Server 
Migration Assistant (SSMA) to pull the tables and data 
across from an Access BE rather than using the Upsizing 
Wizard in Access 2003.

I am sitting with a few questions that I'd appreciate if 
someone in the group would give their answer / comments to.

1.) TimeStamp column
In some recommended settings I came across for using the 
Upsizing Wizard in Access2003, it was suggested to not 
create the TimeStamp column. In our first project we didn't 
create the column, and the resulting access application with 
a SQL2005 BE is functioning very well in a terminal server 
environment with 60 users.

The SSMA has created a TimeStamp column in every table 
migrated.
My question is, what is the impact of this. 
Is the column necessary? 
There obviously is nothing in our existing code that make 
use of the TimeStamp column. 
And it appears - from our experience with the first 
migration - that SLQ2005 does not require the DateStamp 
column to keep track of whether a row has been updated or 
not. How is the DateStamp column utilized? Only by the 
developer through code, or does SQLServer make use of it 
internally if the column is available?

2. PK Autonumber - increment random
Some of the Access tables have a PK defined as a random 
AutoNumber. SQL does not like the negative numbers in the PK 
column, and as a consequence migrates the field as a unique 
PK but does not set the Identity property of the Column.

What is the best way of handling this?

So far, I am thinking that I just have to create / generate 
new PK sequential autonumbers and deploy these as new FK in 
all relevant tables - before migrating.
Do you agree?

3. Column Names
Some column names starting with a number - f.ex. 96FEB is 
not liked by the SSMA; it throws the following 
message: "Column '96Feb' has a name that might cause 
problems for the Access application to function correctly 
against SQL Server". 
Do I need to be concerned re column names starting with a 
number??

Thanks,
Regards
Borge Hansen




More information about the dba-SQLServer mailing list