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
Hi!
Date Issue:
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!
User Issue:
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
go
declare @userid varchar(20), @tsql varchar(1000)
declare user_cur cursor for select [User ID] from Navision.dbo.“User” – change db name!
open user_cur
fetch next from user_cur into @userid
while @@fetch_status = 0 begin
set @tsql =
’
USE [master]
GO
CREATE LOGIN [’ + @userid + ‘] WITH PASSWORD = ‘‘welcome’’, CHECK_POLICY = OFF
GO
use [Navision] – change db name!
GO
CREATE USER [’ + @userid + ‘] FOR LOGIN [’ + @userid + ']
GO
’
print @tsql
fetch next from user_cur into @userid
end
close user_cur
deallocate user_cur
Hope this helps a little.
Regards,
Jörg
Hi,
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 .
Gbenga
Hi,
Pls, could i bget the script that could be use to transfer the users id to sql database before migration.
Gbenga
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
{
OBJECT-PROPERTIES
{
Date=20.07.09;
Time=12:00:00;
Modified=Yes;
Version List=SSI/TEMP;
}
PROPERTIES
{
OnRun=BEGIN
OutFileName := ‘D:\NAV_SQLLoginsUsers.sql’; // <<< Change Filename here!
IF WindowsLogin.FINDSET THEN
REPEAT
TempLogin.INIT;
WindowsLogin.CALCFIELDS(ID,Name);
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;
TempLogin.INSERT;
END;
UNTIL WindowsLogin.NEXT = 0;
IF DatabaseLogin.FINDSET THEN
REPEAT
TempLogin.INIT;
TempLogin.“User ID” := DatabaseLogin.“User ID”;
TempLogin.“Windows Login” := FALSE;
TempLogin.“Windows Login ID” := ‘’;
TempLogin.Name := DatabaseLogin.Name;
TempLogin.INSERT;
UNTIL DatabaseLogin.NEXT = 0;
OutFile.WRITEMODE(TRUE);
OutFile.TEXTMODE(TRUE);
OutFile.QUERYREPLACE(TRUE);
IF OutFile.CREATE(OutFileName) THEN BEGIN
OutFile.WRITE(’-- Create NAV SQL Logins and Users, ’ + FORMAT(CURRENTDATETIME));
OutFile.WRITE(’’);
IF TempLogin.FINDSET THEN
REPEAT
OutFile.WRITE(‘USE [master]’);
OutFile.WRITE(‘GO’);
IF TempLogin.“Windows Login” THEN
OutFile.WRITE(‘CREATE LOGIN [’ + TempLogin.“Windows Login ID” + ‘] FROM WINDOWS’)
ELSE
OutFile.WRITE(‘CREATE LOGIN [’ + TempLogin.“User ID” + ‘] WITH PASSWORD = ‘‘welcome’’, CHECK_POLICY = OFF’);
OutFile.WRITE(‘GO’);
OutFile.WRITE(‘USE ’ + GetCurrentDatabase());
OutFile.WRITE(‘GO’);
OutFile.WRITE(‘CREATE USER [’ +TempLogin.“User ID” + ‘] FOR LOGIN [’ + TempLogin.“User ID” + ‘]’);
OutFile.WRITE(‘GO’);
OutFile.WRITE(’’);
UNTIL TempLogin.NEXT = 0;
OutFile.CLOSE;
END ELSE
ERROR(‘Could not create output-file “%1”.’, OutFileName);
END;
}
CODE
{
VAR
Databases@1000000005 : Record 2000000048;
DatabaseLogin@1000000000 : Record 2000000002;
WindowsLogin@1000000001 : Record 2000000054;
TempLogin@1000000002 : TEMPORARY Record 387;
OutFile@1000000003 : File;
OutFileName@1000000004 : Text[1024];
LOCAL PROCEDURE [ShortUserID@4(VAR](mailto:ShortUserID@4(VAR) UserID@1000 : Text[100]) : Code[20];
BEGIN
IF STRPOS(UserID,’’) IN [0,STRLEN(UserID)] THEN
IF STRLEN(UserID) <= 20 THEN
EXIT(UserID)
ELSE
EXIT(’’)
ELSE
EXIT(COPYSTR(UserID,STRPOS(UserID,’’) + 1,20));
END;
PROCEDURE GetCurrentDatabase@1000000001() : Text[250];
BEGIN
Databases.SETRANGE(“My Database”, TRUE);
Databases.FINDFIRST;
EXIT(STRSUBSTNO(’[%1]’, Databases.“Database Name”));
END;
BEGIN
END.
}
}
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.
Gbenga
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)…
Thanks Jorg,
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.
Thanks.
Gbenga
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
Regards,
Jörg