> A developer who handled converting my Access DB to > SQL Server for a client told me I had to rewrite all my > INSERT queries to use SELECT MAX(ID), because there > was no equivalent to AUTONUMBER in SQL Server. Eh?
Hmmmm. That sounds like a warning sign. Identity is the SQL Server equivalent to autonumber in SQL Server. > Why not carry on using AUTONUMBER, and convert > that field to IDENTITY if upsizing? Ordinarily I'd say that was what you should do (see below) The trouble comes in when you have to pull the number out again, with the proviso that you can't use a stored proc, a trigger or the @@identity value (which I have heard argued here is also vulnerable to the same problem as is the MAX method, for much the same reasons). >> Why do Fusebox advise using the MAX_ID tag for and the >> 2-step INSERT for "portability" reasons - is this only when >> converting to non-MS stuff like Oracle or Sybase? Steve's >> documentation for the tag says: > > "Because autonumber datatypes are not easily transferable > from database to database." I downloaded that tag, and in the readme.txt there's an example routine in there that will retry submissions until it submits a record with a unique key value, thereby resolving the multiple user issue. A very interesting idea. Like the author says, it does appear to be pretty slick, and if its as foolproof as presented would be a neat way to make a completely portable primary key value using a simple, universal integer datatype. If you can't use stored procs, then maybe this tag, coupled to that retry routine, *is* the best way to go. --------------------------------------- Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc., www.mysecretbase.com --------------------------------------- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
