We just migrated to sql database 2008 and we are having two problems First We have the date issues and the problem of having to create user id both on the dynamic native database and the sql database. The date issue is there any way we could treat the date issue faster as we are having 8 databases to be migrated and when we are trying to restore the database to sql it always give wrong date error due to wrong entries by users. Since not all date are validated by dynamic like the planning date, shipment and other date that allow wrong entries of date. The other problem is that whenever I create a user in dynamics I must also create it on the sql database. There should be a way that whenever a user is created on dynamic it should automatically be created on the sql database
The “Upgrade Toolkit” provides some Objects which also check for invalid dates before migration! SO you don’t have to run all this “try & error” thing!
This little TSQL script (change it where applicable!) would create the required Code to create the SQL Logins & Users on basis of the NAV User table (works only with Database Logins, the defaukt password is “welcome”).
Of course, this only works after migration to SQL. If you need the Logins before migrating the DB, you could easily write some NAV code which creates a similar output-file.
set statistics io off
declare @userid varchar(20), @tsql varchar(1000)
declare user_cur cursor for select [User ID] from Navision.dbo.“User” – change db name!
fetch next from user_cur into @userid
while @@fetch_status = 0 begin
set @tsql =
CREATE LOGIN [’ + @userid + ‘] WITH PASSWORD = ‘‘welcome’’, CHECK_POLICY = OFF
use [Navision] – change db name!
CREATE USER [’ + @userid + ‘] FOR LOGIN [’ + @userid + ']
fetch next from user_cur into @userid
Hope this helps a little.
Thanks for the quick responds for the date issue how do i get to the upgrade tool kit in dynamic.
Right now i am using the test database to run the check. For the user issue where will i place the script and what name will be given to the script for it to run properly or rather where do i attached the script .
Pls, could i bget the script that could be use to transfer the users id to sql database before migration.
This would create a little Codeunit (80006, change ID if applicable) to create the required TSQL (no guarantee, no warrantee, no support - PROVIDED AS IS, USE AT OWN RISK):
OBJECT Codeunit 80006 Create SQL Logins/Users
OutFileName := ‘D:\NAV_SQLLoginsUsers.sql’; // <<< Change Filename here!
IF WindowsLogin.FINDSET THEN
TempLogin.“User ID” := ShortUserID(WindowsLogin.ID);
IF TempLogin.“User ID” <> ‘’ THEN BEGIN
TempLogin.“Windows Login” := TRUE;
TempLogin.“Windows Login ID” := WindowsLogin.ID;
TempLogin.Name := WindowsLogin.Name;
UNTIL WindowsLogin.NEXT = 0;
IF DatabaseLogin.FINDSET THEN
TempLogin.“User ID” := DatabaseLogin.“User ID”;
TempLogin.“Windows Login” := FALSE;
TempLogin.“Windows Login ID” := ‘’;
TempLogin.Name := DatabaseLogin.Name;
UNTIL DatabaseLogin.NEXT = 0;
IF OutFile.CREATE(OutFileName) THEN BEGIN
OutFile.WRITE(’-- Create NAV SQL Logins and Users, ’ + FORMAT(CURRENTDATETIME));
IF TempLogin.FINDSET THEN
IF TempLogin.“Windows Login” THEN
OutFile.WRITE(‘CREATE LOGIN [’ + TempLogin.“Windows Login ID” + ‘] FROM WINDOWS’)
OutFile.WRITE(‘CREATE LOGIN [’ + TempLogin.“User ID” + ‘] WITH PASSWORD = ‘‘welcome’’, CHECK_POLICY = OFF’);
OutFile.WRITE(‘USE ’ + GetCurrentDatabase());
OutFile.WRITE(‘CREATE USER [’ +TempLogin.“User ID” + ‘] FOR LOGIN [’ + TempLogin.“User ID” + ‘]’);
UNTIL TempLogin.NEXT = 0;
ERROR(‘Could not create output-file “%1”.’, OutFileName);
Databases@1000000005 : Record 2000000048;
DatabaseLogin@1000000000 : Record 2000000002;
WindowsLogin@1000000001 : Record 2000000054;
TempLogin@1000000002 : TEMPORARY Record 387;
OutFile@1000000003 : File;
OutFileName@1000000004 : Text;
LOCAL PROCEDURE [ShortUserID@4(VAR](mailto:ShortUserID@4(VAR) UserID@1000 : Text) : Code;
IF STRPOS(UserID,’’) IN [0,STRLEN(UserID)] THEN
IF STRLEN(UserID) <= 20 THEN
EXIT(COPYSTR(UserID,STRPOS(UserID,’’) + 1,20));
PROCEDURE GetCurrentDatabase@1000000001() : Text;
Databases.SETRANGE(“My Database”, TRUE);
EXIT(STRSUBSTNO(’[%1]’, Databases.“Database Name”));
P.S.: Actually this was created on a NAV/SQL DB (SQL 2005 / NAV 4.0.3) - not native. In native NAV the “Database” table might ot exist, thus the function “GetCurrentDatabase” may not work. In this case you should encode the DB name fixed.
Thanks jorg but how do i get to the upgrade tool kit for the date issue.
The Navision objects for the Upgrade Toolket are on the Navision W1 Upgrade Toolkit; SQL Migration.
You need to load the objects from the Migrate.fob. Then follow the instructions. Dependent upon the size of your database this could take some time (hours)…
I have not use the code yet i am still going through it, just trying to be carefull. Pls i need a E-book on dynamic coding or a hand book on coding in dynamic for better understanding and good programming skill. Any assistant will be highly appreciated.
There should be the “Application Designer’s Guide” on the product CD-ROM; and a Dynamics Partner could collect some documents from “Partner Source”.
But maybe you want to check out this one: http://dynamicsuser.net/groups/dynamics_book_reviews/media/p/138360.aspx