Access an SQL database from native Navision

Cuypz

Hi all,

Is it possible to connect to an SQL server from inside native Navision? At the moment we have Navision filling in a table periodically with the appropriate data to be exported. Another application reads this data via ODBC and imports this into another table in another non-Navision database (on SQL Server).

Since de NODBC driver is not stable enough to suit our needs (unreliable, frequent crashes,...), we would like to know if it's at all possible to write the data directly into the destination SQL table, so that we can skip the export/import procedure alltogether.

Thank you for your time and knowledge.

krzychub83

Yes it is possible to connect to sql server from inside NAV.

ADOCon is an Microsoft ActiveX Data Objects 2.7 Library'.Connection (Automation)
ADORs is an Microsoft ActiveX Data Objects 2.7 Library'.Recordset (Automation)
ADOConStr is an text
SQLStatement is an text

It's just an example:

ContextKB := CONTEXTURL;
ADOConStr := 'Provider=sqloledb;Data Source=' +  COPYSTR(ContextKB,STRPOS(MyContext,'servername') + 11, STRPOS(ContextKB,'&data') - STRPOS(ContextKB,'servername')-11) + ;Initial Catalog=' + COPYSTR(MyContext,STRPOS(MyContext,'&data') + 10, STRPOS(MyContext,'&company') - STRPOS(MyContext,'&data')-10) + ;User Id=SqlNavUSer;Password='+CompanyInf."SQLUser password" + ';';

SQLStatement := 'SELECT * '
 + 'FROM [' + CONVERTSTR(COMPANYNAME, '.', '_') + 'V$Item] ';

CREATE(ADOCon);
CREATE(ADORs);
ADOCon.Open(ADOConStr);
ADORs.Open(SQLStatement, ADOCon, 3);
WHILE NOT ADORs.EOF() DO BEGIN
  INIT;
  EVALUATE(d, FORMAT(ADORs.Fields.Item('No.').Value));
  INSERT;
  ADORs.MoveNext();
END;
ADORs.Close();
ADOCon.Close;

Have a good day

Cuypz

Hi there,

Thanks for your quick reply! I'm trying it as we speak, but I'm having trouble logging in to our SQL Server database, since it is configured to work with Windows Logon. Any suggestions for passing on a username/password that way?


I've got this piece of code so far:

ADOConStr := 'Provider=sqloledb;' +
             'Data Source=SQL SERVER NAMLE;' +
             'Initial Catalog=COMPANY NAME;' +
             'User Id=xxx;' +
             'Password=xxx;';

                      
SQLStatement := 'INSERT INTO test VALUES(''bup'')';

CREATE(ADOCon);
ADOCon.Open(ADOConStr);
ADOCon.Execute(SQLStatement);
ADOCon.Close;

krzychub83

I can not test it now but:
( from : http://msdn.microsoft.com/en-us/library/aa905872(SQL.80).aspx )

Windows NT authentication.
Integrated Security = SSPI

so try:
ADOConStr := 'Provider=sqloledb;' +
'Data Source=SQL SERVER NAMLE;' +
'Initial Catalog=COMPANY NAME;' +
'Integrated Security = SSPI;';
good luck

Cuypz

Yeah, I just discovered it myself I was on my way posting my solution when I saw your reply. Thanks for the effort mate!

For future reference:

ADOConStr := 'Provider=sqloledb;' +
             'Server=SQL SERVER NAME;' +
             'Database=DATABASE NAME;' +
             'Integrated Security=SSPI';

SQLStatement := 'INSERT INTO COMPANY$TABLE(Field1[,Field2][,...]) VALUES(value1[,value2][,...])';

CREATE(ADOCon);
ADOCon.Open(ADOConStr);
ADOCon.Execute(SQLStatement);
ADOCon.Close;