SQL from NAV - connection string

ScottForsyth

Hello,

We have a NAV application that makes a direct SQL query from the NAV C/AL code. We need to do it to leverage some non-NAV functionality, and it works well except for one thing.

We want to be able to get the NAV server and NAV database dynamically from the C/AL code. Right now we hardcode the connection information within the CodeUnit, but we want to get away from that.

I haven't had any luck yet in finding out how to obtain the database server and name from C/AL, but I assume it must be possible. Any ideas on how to do that?

Thanks,

Scott

Juhl

You can query the service tier settings.

VAR
Name: ServerUserSettings
Type: DotNet
Subtype: Microsoft.Dynamics.Nav.Types.ServerUserSettings.’Microsoft.Dynamics.Nav.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35′

FUNCTION
GetSQLServerName() : Text
{
ServerUserSettings := ServerUserSettings.Instance();
IF FORMAT(ServerUserSettings.DatabaseInstance) <> '' THEN
EXIT(STRSUBSTNO('%1\%2',DatabaseServer,ServerUserSettings.DatabaseInstance));

EXIT(FORMAT(ServerUserSettings.DatabaseInstance));
}

ftornero

What's the NAV version

Regards.

Juhl

You can query the service tier settings.

VAR
Name: ServerUserSettings
Type: DotNet
Subtype: Microsoft.Dynamics.Nav.Types.ServerUserSettings.’Microsoft.Dynamics.Nav.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35′

FUNCTION
GetSQLServerName() : Text
{
ServerUserSettings := ServerUserSettings.Instance();
IF FORMAT(ServerUserSettings.DatabaseInstance) <> '' THEN
EXIT(STRSUBSTNO('%1\%2',DatabaseServer,ServerUserSettings.DatabaseInstance));

EXIT(FORMAT(ServerUserSettings.DatabaseInstance));
}

ScottForsyth

Thanks Juhl! That worked. I support a few different NAV instances, but it works for the current versions (10.0.0.0), and for the older versions I just found the DLL and copied to the add-ins folder and then it's available to reference and use.