3.70A (GB) Migration to MS SQL issues

Hi, We’re in the middle of a test migration of our in-house 3.70A (GB, no hotfixes) to MS SQL 2000 and seem to be experiencing a couple of issues: Synchronize destroys users login details on MS SQL -------------------------------------------------- For the purposes of this test we’ve removed all the DB and Windows login details from Navision, leaving only the MS SQL authentication to worry about. When a ‘Synchronize’ is run there appears to be a tendency for Navision to destroy the priveleges in MS SQL for the current user. This can’t be avoided when the synchronize is automatically called after a restore. G/L Account Schedules - formulas calculating incorrect values ------------------------------------------------------------- More seriously, the G/L account schedules are reporting completely different values in some cases. As an example: 20 Posting 1410…1440 30 Posting 1110…1140 40 Posting 1210…1240 180 Formula 20…40 So line 180 should be the sum of the totals from lines 20, 30, 40 – but it’s not - in this case it’s out by a million or more. If we create a new schedule containing only the above it works fine. If I replace ‘20…40’ with ‘20|30|40’ it also works fine. On another schedule we get the error: “You have entered an illegal or nonexistent row number.”. As far as I can tell collation should be ‘Windows’ for all tables. Any help greatly appreciated, Nick.

I’ve bottomed the account schedules problem myself… the default SQL datatype for CODE fields was affecting sort order. It’s easily resolved by pre-zeroing the account schedule line numbers where necessary. For anybody else out there struggling with this you should check your G/L Accounts and VAT Statement line numbers too. I’m still having real difficuly with authentication though, I tried adding a windows login to Navision and locked everybody out of the system including myself.

Resolved it myself. I hadn’t understood the ‘Synchronize’ between Navision and MSSQL is one way (Navision->MSSQL) and it’s fairly ruthless. We had specified permissions at the DB level rather than at server level (This server supports a number of in-house apps. so it’s fairly tightly locked down). As there were no windows logins specifed in Navision the synchronize was trying to remove all user privileges from the DB. By ensuring I had valid ‘Windows logins’ with appropriate permissions in the native DB prior to migration (And removing our old DB-only logins) the synchronize had something to synchronize.