Arthur Fuller
fuller.artful at gmail.com
Sun May 27 07:33:46 CDT 2007
Hi Borge, A timestamp column does not accurately reflect a meaningful date or time. It is simply an incrementing value that reflects relative time as seen from the viewpoint of the database, and has no relationship to a clock in the real world. Its purpose is to identify changed records. Every time a record is changed in any way, its timestamp value, if any, is changed. For this reason it is a lousy candidate for a PK, and in fact for any key, if the data changes fairly often. In general, timestamp columns are not especially useful except in the case of replicated databases. Should you ever wish to obtain the current timestamp value for the database, use SELECT @@DBTS. Regarding your randomly chosen PKs, this can be a little bit tricky to change. IMO, there is not much value in selecting this option when creating a table. However, since it's already done, your problem is how to fix it. This depends on how many related tables there are. I would approach it like this: First off, make a copy of the original database. Now, working with the copy... For Table A: 1. Copy the structure but not the data to a new table A1. 2. Modify the structure: 2a) Change the current PK to an LongInt column and remove the PK setting from this column. 2b) Add a new sequential autonumber column called newPK or whatever. 3. Append the data from the original Table A. For each table related to Table A (let's call it Table B): 1. Add a column called TableA_FK or whatever. 2. Update Table B by joining it to Table A on your original PK/FK, but SET TableB.TableA_FK to the value of TableA.NewPK. The join will keep the rows pointed at their parent rows in TableA. You now have the correct (new) keys from TableA in the new column in TableB. If there are any tables related to TableB, repeat the procedure. Satisfy yourself that the data still hangs together correctly. When you're satisfied, you can remove all the randomPK columns and declare all the NewPK autonumber columns as your new PKs. At this stage you can rename these columns to the names of your original PKs, so any code depending on their names will continue to run correctly. 1. Create a new table with two columns, OldPK and NewPK. Make the former an int and the latter an incrementing identity key. Then append to this table all the OldPKs from Table A. You will now have a two-column table that you can use to map the oldPK to its new equivalent IMO it's a bad idea to name any column beginning with a number, and you've discovered why. I would suggest that the simplest solution might be to add a single character prefix to all such column names. Of course, you'll have to keep track of every one you rename, so that you can use Speed Ferret or Rick Fisher's Find and Replace to change all occurrences of said names in your code. With either product, you can create a list of all target names and their replacement names, and do it all at once. hth, Arthur On 5/27/07, pcs at azizaz.com <pcs at azizaz.com> wrote: > > 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 > >