Upsizing wizard - common gotchas?

[mkrausnick]

After making sure all tables had a primary key, I ran the Upsizing wizard on a small (14 tables, 10mb) access database. It ran through with no problems, and all the reports seem to work. No errors, other than the anomaly that the report shows the primary keys with "Duplicates OK" whereas the SQL database shows NO NULLS on keys indicated as primary keys.

I'm always suspicious when nothing goes wrong. Are there any common gotchas I should check for? I realize this is a general question with not much information, but any thoughts would be appreciated.

Thanks,

Mike Krausnick
Dublin, California

 

[lameid]

I'd say typically there are no gotchas on tables. Pretty straight forward... Unless you do something like use a SQL keyword as a fieldname (annoyed with myself on that one).

Queries on the otherhand... Anything that is NOT Transact SQL compatible will bomb. For example using IIF, NZ and Isnull won't work even though they can be replaced with Case (When Then Else End), Isnull and Case (When Then Else End) with Null comparison. User defined functions will also bomb (maybe not if they mirror transact functions and have the same name).

That's it in a nutshell.

While I'm talking about SQL, the Dateadd function in MS-SQL is the same as Jet SQL except that the interval is not a string. That one bit me for a little while.

 

[mkrausnick]

Thanks for the query tip. I'll check all the queries for valid t-sql. The app doesn't have any UDFs.

Mike Krausnick
Dublin, California

 

[lameid]

Allow me to clarify something. The upsizing wizard will let you know if any queries fail. It is just more likely you would see an error there than on tables.

 

[Pampers]

I think the yes/no variable from access can cause problems when converted... (something with triple state?) make sure the bin-variable in sql has (default)values. You get a message like "Someone changed this record..bla"

Pampers
Keeping it simple can be complicated

 

[mkrausnick]

Thanks lameid - that saves me a lot of eye strain wading through SQL code.

Except now I have to go test all binary check boxes to make sure they work - Thanks Pampers! I heard about the binary field issue from someone else too, so that's confirmed.

See I knew it wasn't that simple!

Mike Krausnick
Dublin, California