Toad World® Forums

SQL Server to Access conversion wrong, how to fix?

So I am trying to convert an SQL Server 2005 model to an Microsoft Access 2003 model. After going through the wizard things look fairly right except one for one thing. None of my Identity columns from my SQL Server are AutoNumber columns in access.

Can anyone help me with how to specify in the data conversion settings that if a column is an integer AND an identity column, it needs to be an AutoNumber column in access?

Thanks very much,
Tom

Hi,

model conversion helps you to convert basic objects and datatypes. Identity/AutoNumber settings cannot be automatically converted.

Regards,

Vaclav

Ok, how does that solve my problem though? How does the SQL Server -> Oracle port work then? How does it know to create sequences for SQL Server identity columns??

I guess I’m looking for a way to script this but is this possible? Kinda seems like a bug in the conversion to me.

Hi Tom,

Toad Data Modeler cannot parse all objects and convert SQL syntax from Oracle to Microsoft SQL Server or Microsoft Access or any other database automatically - this is too difficult task because people can write SQL in various ways and there is no parser and conversion method that could do everything correctly. That’s why we can convert basic objects and convert data types, but functions, stored procedures etc. cannot be converted automatically.

In version 3.5 the following improvements occured:

  • Conversion from Oracle to MS SQL (and MS SQL to Oracle): Automatic conversion of Identity in MS SQL to Sequence in Oracle (and back).
  • Conversion from Oracle to MySQL (and back): Sequence in Oracle are converted to Autoincrement in MySQL (and back).
  • Conversion from MS SQL to MySQL (and back): During conversion of Identity (MS SQL) to Autoincrement (MySQL) and back the new IdentitySeed/Initial Autoincrement is taken into consideration. (In previous version, only conversion between checkbox
    Identity and Autoincrement was possible.)

All the features are in the product and are not in form of a macro/script because it is difficult to enhance the Sync & Convert Wizard. Too many items are being taken into consideration during the conversion.

In any case, I created new enhancement request: CR#91299.

Regards,

Vaclav