[ad_1]
Microsoft Enterprise Options Navision is most important ERP software for European, Brazilian markets plus it serves vertical segments of the USA market, resembling mild manufacturing, POS, CRM. As our expertise signifies – Navision implementation requires extra customization and tuning compared to one other mid-market ERP answer from MBS: Microsoft Nice Plains. Our aim is to popularize Microsoft Enterprise Options merchandise and enlighten IT individuals to do customise Navision in-house.
Immediately, the principle subject of this text is working with native C/SIDE Navision Attain database through C/ODBC interface, particularly – Linked Server creation in Microsoft SQL Server 2000 setting after which Gross sales report design in Crystal Reviews ver. 10. Let’s start:
1. We are going to use Navision Attain 3.6 with Navision Database Server, Navision ApplicationServer and Navision Shopper. These element are put in on Home windows XP Skilled. To allow C/ODBC interface you have to set up this element from Navision Attain CD.
2. Let’s create ODBC DSN for connection to Navision database: Begin -> Management Panel -> Administrative Instruments -> Information Sources (ODBC), then swap to System DSN and click on Add. We are going to use C/ODBC 32-bit driver. Give Information Supply Identify Navision, Connection – go away it Native. Because the database (Database button) choose Program FilesNavision AttainClientdatabase.fdb (that is demo DB). Then press Firm button – in our case we use CRONUS demo database. Essential situations to offer correct reference to C/SIDE database are right choices for C/ODBC. Click on Choices button and have a look at the choices obtainable – we are going to take note of Identifiers display screen – that is the place you setup identifiers to be transferred to shopper software. We are going to use these sorts: “a-z,A-Z,0-9,_” to make MS SQL Server 2000 work right with C/ODBC supply. We at the moment are achieved with ODBC DSN. Lets setup Linked Server
3. Open SQL Server Enterprise Supervisor. Open object browser on the left facet, choose Safety and Linked Servers. With proper click on choose New Linked Server within the context menu. Within the rising dialog window, within the menu Supplier Identify choose Microsoft OLE DB Supplier for ODBC Drivers. Let’s identify our Linked Server as NAVISION. Within the Information Supply string sort in ODBC DSN identify – NAVISION in our case. Linked Server is prepared. Choose tables checklist and we now see Navision Attain tables.
4. Now we have to create small process for Gross sales information extraction. Right here is the textual content of the process:
CREATE PROCEDURE NavisionSalesReport AS
DBCC TRACEON(8765)
SELECT * FROM OPENQUERY(NAVISION, ‘SELECT * FROM Sales_Line sls, Buyer cust WHERE sls.Sell_to_Customer_No_ = cust.No_’)
RETURN
GO
To make clear its textual content: TRACEON(8765) directive permits working with the info outcomes of variable size returned by C/ODBC driver. If we do not use this directive – we won’t pull the outcomes from Navision tables – we are going to get errors, like this:
OLE DB error hint [Non-interface error: Unexpected data length returned for the column: ProviderName=’MSDASQL’, TableName='[MSDASQL]’, ColumnName=’Ship_to_Filter’, ExpectedLength=’250′, ReturnedLength=’1′].
Server: Msg 7347, Degree 16, State 1, Line 1
OLE DB supplier ‘MSDASQL’ returned an sudden information size for the fixed-length column ‘[MSDASQL].Ship_to_Filter’. The anticipated information size is 250, whereas the returned information size is 1.
OPENQUERY assertion opens linked server and transfers its SQL command, and in its flip returns the end result set. To check the process sort on this command in SQL Server Question Analyzer: EXEC NavisionSalesReport
5. Now – launch Crystal Reviews, use customary Wizard, create new OLE DB (ADO) reference to our MS SQL 2000 Server and choose NavisionSalesReport process within the checklist. Because the report fields we are able to use Description, Amount, Line_Amount and Discount_Amount fields. You may group by identify Identify – that is buyer identify. Our report is now prepared!
Blissful customizing, implementing and modifying! If you’d like us to do the job – give us a name 1-866-528-0577 or 1-630-961-5918! assist@albaspectrum.com
[ad_2]
Source by Boris Makushkin
