Access an SQL database from native Navision

Cuypz
Cuypz Member Posts: 56
edited 2008-10-29 in NAV/Navision Classic Client
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.

Comments

  • krzychub83
    krzychub83 Member Posts: 120
    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
    Cuypz Member Posts: 56
    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
    krzychub83 Member Posts: 120
    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
    Cuypz Member Posts: 56
    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;

Categories