Software informational articles

Navision attain c/odbc gemstone article ? customization case - software


Microsoft Commerce Solutions Navision is main ERP attention for European, Brazilian markets plus it serves vertical segments of the USA market, such as light manufacturing, POS, CRM. As our encounter indicates - Navision implementation requires more customization and tuning in assessment to an added mid-market ERP blend from MBS: Microsoft Great Plains. Our goal is to popularize Microsoft Commerce Solutions food and enlighten IT ancestors to do make to order Navision in-house.

Today, the main topic of this clause is running with native C/SIDE Navision Attain folder via C/ODBC interface, in distinct - Allied Ma?tre d'h?tel concept in Microsoft SQL Head waiter 2000 ecosystem and then Sales account aim in Gemstone Intelligence ver. 10. Let's begin:

1. We will use Navision Attain 3. 6 with Navision Catalog Server, Navision ApplicationServer and Navision Client. These module are installed on Windows XP Professional. To make possible C/ODBC border you need to fit this constituent from Navision Attain CD.

2. Let's build ODBC DSN for association to Navision database: Start -> Be in command of Panel -> Administrative Tools -> Data Sources (ODBC), then button to Arrangement DSN and click Add. We will use C/ODBC 32-bit driver. Give Data Basis Name Navision, Bond - leave it Local. As the folder (Database button) decide on Agenda FilesNavision AttainClientdatabase. fdb (this is demo DB). Then press Ballet company badge - in our case we use CRONUS demo database. Chief environment to endow with apposite association with C/SIDE catalog are adjust options for C/ODBC. Click Options close and look at the options accessible - we will pay consideration to Identifiers check out - this is where you setup identifiers to be transferred to client application. We will use these types: "a-z,A-Z,0-9,_" to make MS SQL Attendant 2000 work accepted with C/ODBC source. We are now done with ODBC DSN. Lets setup Allied Server

3. Open SQL Ma?tre d' Venture Manager. Open aim browser on the left side, choice Collateral and Allied Servers. With right click choice New Connected Ma?tre d'h?tel in the circumstance menu. In the emerging dialog window, in the menu Bringer Name choice Microsoft OLE DB Giver for ODBC Drivers. Let's name our Connected Head waiter as NAVISION. In the Data Find filament type in ODBC DSN name - NAVISION in our case. Connected Head waiter is ready. Cliquey tables list and we now see Navision Attain tables. 4. Now we need to conceive small method for Sales data extraction. Here is the text of the procedure: CREATE Formula NavisionSalesReport AS


SELECT * FROM OPENQUERY(NAVISION, 'SELECT * FROM Sales_Line sls, Consumer cust WHERE sls. Sell_to_Customer_No_ = cust. No_')


To elucidate its text: TRACEON(8765) directive allows functioning with the data fallout of capricious duration returned by C/ODBC driver. If we don't use this directive - we can't pull the consequences from Navision tables - we will get errors, like this:

OLE DB error trace [Non-interface error: Unexpected data distance end to end returned for the column: ProviderName='MSDASQL', TableName='[MSDASQL]', ColumnName='Ship_to_Filter', ExpectedLength='250', ReturnedLength='1']. Server: Msg 7347, Level 16, State 1, Line 1 OLE DB donor 'MSDASQL' returned an unexpected data distance end to end for the fixed-length editorial '[MSDASQL]. Ship_to_Filter'. The estimated data distance end to end is 250, while the returned data chunk is 1.

OPENQUERY assertion opens allied attendant and transfers its SQL command, and in its turn proceeds the consequence set. To test the formula type in this authority in SQL Ma?tre d'h?tel Query Analyzer: EXEC NavisionSalesReport 5. Now - launch Rock Reports, use average Wizard, coin new OLE DB (ADO) correlation with our MS SQL 2000 Head waiter and choice NavisionSalesReport method in the list. As the article fields we can use Description, Quantity, Line_Amount and Discount_Amount fields. You can group by name Name - this is buyer name. Our article is now ready! Happy customizing, implementing and modifying! If you want us to do the job - give us a call 1-866-528-0577 or 1-630-961-5918! help@albaspectrum. com

Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies - USA generally Microsoft CRM, Microsoft Great Plains customization company, portion Chicago, Boston, San Francisco, San Diego, Los Angeles, Houston, Dallas, Atlanta, Miami, Montreal, Toronto, Vancouver, Moscow, Europe and worldwide (http://www. albaspectrum. com), he is Microsoft CRM SDK, Navision, C#, VB. Net, SQL, Oracle, Unix developer.

Developed by:
home | site map © 2018