With ADO you can execute SQL Scripts and Stored Procedures from Navision.
To use ADO in Navision, you have to use the automation “Microsoft ActiveX Data Objects 2.8 Library”
Lets take a closer on how to make a SQL connection and perform a SQL Query in Navision.
First you have to make a connection to the SQL Server:
ConnectionString := 'PROVIDER=SQLOLEDB;SERVER=SQLServer;DATABASE=SQLDatabase;UID=SQLUserID;PWD=SQLPwd';
where ADOConnection is defined as 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
Now you have a open connection to the SQL Server and are ready to execute scripts.
SQLString := 'SELECT [Name],[Customer No_] as CustomerNo ' +
'FROM [Customer] WHERE [Customer No_] < 100'; ADORecordSet := ADOConnection.Execute(SQLString,RecordsAffected,RSOption);
Where ADORecordSet is defined as "Microsoft ActiveX Data Objects 2.8 Library'.Recordset", RecordsAffected returns the number of records fetched and RSOption sets how the provider should evaluate the commandtext parameter (SQLString).
Now that the query has been performed, the query result is returned into a the ADO RecordSet. Which now can be parsed.
TmpTable.Name := ADORecordSet.Fields.Item('Name').Value;
TmpTable."No." := ADORecordSet.Fields.Item('CustomerNo').Value;
When you are done using the ADORecordSet and the ADOconnetion, you have to close them: