1 Reply Latest reply on Jan 3, 2018 12:57 AM by stephen.faghy@commagility.com

    How to link DxDatabook to SAP R/3 Database for SQL ODBC queries

    stephen.faghy@commagility.com
    stephen.faghy@commagility.com Dec 20, 2017 5:13 AM

      I'm trying to get DxDatabook to view existing data fields store on SAP, by setting up an ODBC connection to use SQL queries. I'm using the 32 bit version of the ODBC Data Source Administrator as both the Mentor and SAP software are 32 bit.

      I'm also trying to login with as specific username and password that has been created to get read-only access.

      But when I attempt to make the connection I get this message from the Microsoft SQL Server Login

      "Connection failed:     SQLState: '28000'     SQL Server Error:18456     [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '****'

       

      However, if I try a similar set-up from an Excel spreadsheet (Get External Data) the username and password work, and I can then query SAP.

       

      Has anyone had this problem, and is there a solution?

      • 147 Views
      • Tags:
        • 1. Re: How to link DxDatabook to SAP R/3 Database for SQL ODBC queries
          stephen.faghy@commagility.com
          stephen.faghy@commagility.com Jan 3, 2018 12:57 AM (in response to stephen.faghy@commagility.com)

          Connection problem solved -  it was just an incomplete password. Doh!

           

          I'm still having problems with linking though. I can now see the SAP database from the DxDatabook and add particular SAP tables into DxDatabook libraries. I can view and arrange visibility (configure) of single tables.

          Now I want to link two tables horizontally (different table structures, but with a common field used to link them). The Mentor Help manual page "Arranging Library tables" shows exactly the situation I have - one table for parts, one for manufacturer, and a manuf ID in both to be the link.

          The configuration editor allows me to add multiple SAP tables to the DxDatabook library and I can see both table structures and arrange visibility of the fields I want, but when I close the editor I get a variety of error messages like "Invalid column name in Table1", "Invalid column name in Field2",

          "The link table does not contain links", "Incorrect syntax near 'FROM'." and so on. The library then displays the column headings that I configured, but shows no data.

           

          The Help page mentioned above tells me I can do it - but it doesn't tell me HOW to do it.

           

          I cannot see any mechanism that allows me to specify which field is the link.

          If I was using SQL queries, then my query would look like this :-

           

          SELECT T0.[ItemCode], T0.[FirmCode], T1.[FirmName] FROM OITM T0 INNER JOIN OMRC T1 ON T0.[FirmCode] = T1.[FirmCode]

           

          where T0 is the parts table (OITM) and T1 is the manufs table (OMRC). The link is "ON T0.[FirmCode] = T1.[FirmCode]".

           

          Anyone got any suggestions ?