jwcolby
jwcolby at colbyconsulting.com
Sun May 27 06:52:27 CDT 2007
>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. Yes, however... You also have to modify the child table(s) as well. Anytime you mess with the PKs you have to mess with the corresponding FKs. >Do I need to be concerned re column names starting with a number?? This is one of those "best practices" kind of thing. Objects should not ever be names starting with a number or special character. Yes, it can be done and yes, it is a bad idea, for precisely this reason. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of pcs at azizaz.com Sent: Sunday, May 27, 2007 2:41 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Upsizing Access data into SQL2005 using SSMA 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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com