I wrote a solution to log blocking in NAV with the help of Microsoft SQL Native Client. I wanted to be able to use this solution for a client but then ran into a problem with the SQL Native Client. There are three versions out there and the connection string must include the version installed on the client machine.
The first step is to find the “Program Files” folder
[code] LOCAL PROCEDURE GetProgramFilesPath@1200050010() ProgramFilesPath : Text[1024];
VAR
EnviormentPath@1200050000 : Code[50];
BEGIN
IF ISCLEAR(SystemShell) THEN
CREATE(SystemShell);
EnviormentPath := ‘PROCESS’;
SystemEnviroment := SystemShell.Environment(EnviormentPath);
ProgramFilesPath := SystemEnviroment.Item(‘ProgramW6432’);
IF ProgramFilesPath = ” THEN
ProgramFilesPath := SystemEnviroment.Item(‘ProgramFiles’);
END;[/code]and the System32 folder[code] LOCAL PROCEDURE GetSystemRootPath@1100408000() SystemRootPath : Text[1024];
VAR
EnviormentPath@1200050000 : Code[50];
BEGIN
IF ISCLEAR(SystemShell) THEN
CREATE(SystemShell);
EnviormentPath := ‘PROCESS’;
SystemEnviroment := SystemShell.Environment(EnviormentPath);
SystemRootPath := SystemEnviroment.Item(‘SystemRoot’) + ‘\System32’;
END;[/code]
Then I use the EXISTS function to check for the client version.
[code] LOCAL PROCEDURE OpenConnection@1200050006(ConnectAsUserID@1200050003 : Text[30];ConnactAsPassword@1200050002 : Text[30]);
VAR
MyServer@1200050001 : Record 2000000047;
MyDatabase@1200050000 : Record 2000000048;
ClientVersion@1100408000 : Text[30];
ProgramFilesPath@1100408001 : Text[50];
SystemRootPath@1100408002 : Text[50];
BEGIN
IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);
IF ISCLEAR(ADORecordset) THEN
CREATE(ADORecordset);
IF ISCLEAR(ADOStream) THEN
CREATE(ADOStream);
IF ADOConnection.State = 1 THEN
EXIT;
IF ISSERVICETIER THEN BEGIN
IF ISCLEAR(DomDoc) THEN
CREATE(DomDoc);
DomDoc.load(APPLICATIONPATH + ‘CustomSettings.config’);
DomNode := DomDoc.selectSingleNode(‘//appSettings/add[@key=”DatabaseServer”]’);
MyServerName := DomNode.attributes.item(1).text;
DomNode := DomDoc.selectSingleNode(‘//appSettings/add[@key=”DatabaseName”]’);
MyDatabaseName := DomNode.attributes.item(1).text;
END ELSE BEGIN
MyServer.SETRANGE("My Server",TRUE);
MyServer.FINDFIRST;
MyServerName := MyServer."Server Name";
MyDatabase.SETRANGE("My Database",TRUE);
MyDatabase.FINDFIRST;
MyDatabaseName := MyDatabase."Database Name";
END;
ProgramFilesPath := GetProgramFilesPath;
CASE TRUE OF
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\90\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI’;
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\100\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI10’;
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\110\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI11’;
ELSE
BEGIN
SystemRootPath := GetSystemRootPath;
CASE TRUE OF
EXISTS(SystemRootPath + ‘\sqlncli.dll’):
ClientVersion := ‘SQLNCLI’;
EXISTS(SystemRootPath + ‘\sqlncli10.dll’):
ClientVersion := ‘SQLNCLI10’;
EXISTS(SystemRootPath + ‘\sqlncli11.dll’):
ClientVersion := ‘SQLNCLI11’;
ELSE
ERROR(Text002);
END;
END;
END;
IF ConnectAsUserID <> ” THEN
ADOConnection.Open(
STRSUBSTNO(‘Provider=%5;Server=%1;Database=%2;Uid=%3;Pwd=%4;’,
MyServerName,MyDatabaseName,ConnectAsUserID,ConnactAsPassword,ClientVersion))
ELSE
ADOConnection.Open(
STRSUBSTNO(‘Provider=%3;Server=%1;Database=%2;Trusted_Connection=yes;’,
MyServerName,MyDatabaseName,ClientVersion));
ADOConnection.CommandTimeout(0);
END;[/code]
where Text002@1100408002 : TextConst ‘ENU=Microsoft SQL Native Client not found;ISL=Microsoft SQL Native Client finnst ekki.’;