Microsoft Business Solutions Navision is main ERP application for European, Brazilian markets plus it serves vertical segments of the USA market, such as light manufacturing, POS, CRM. As our experience indicates ? Navision implementation requires more customization and tuning in comparison to another mid-market ERP solution from MBS: Microsoft Great Plains. Our goal is to popularize Microsoft Business Solutions products and enlighten IT people to do customize Navision in-house.
Today, the main topic of this article is working with native C/SIDE Navision Attain database via C/ODBC interface, in particular ? Linked Server creation in Microsoft SQL Server 2000 environment and then Sales report design in Crystal Reports ver. 10. Let's begin:
1. We will use Navision Attain 3.6 with Navision Database Server, Navision ApplicationServer and Navision Client. These component are installed on Windows XP Professional. To enable C/ODBC interface you need to install this component from Navision Attain CD.
2. Let's create ODBC DSN for connection to Navision database: Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC), then switch to System DSN and click Add. We will use C/ODBC 32-bit driver. Give Data Source Name Navision, Connection ? leave it Local. As the database (Database button) select Program FilesNavision AttainClientdatabase.fdb (this is demo DB). Then press Company button ? in our case we use CRONUS demo database. Important conditions to provide proper connection with C/SIDE database are correct options for C/ODBC. Click Options button and look at the options available ? we will pay attention to Identifiers screen ? 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 Server 2000 work correct with C/ODBC source. We are now done with ODBC DSN. Lets setup Linked Server
3. Open SQL Server Enterprise Manager. Open object browser on the left side, select Security and Linked Servers. With right click select New Linked Server in the context menu. In the emerging dialog window, in the menu Provider Name select Microsoft OLE DB Provider for ODBC Drivers. Let's name our Linked Server as NAVISION. In the Data Source string type in ODBC DSN name ? NAVISION in our case. Linked Server is ready. Select tables list and we now see Navision Attain tables. 4. Now we need to create small procedure for Sales data extraction. Here is the text of the procedure: CREATE PROCEDURE NavisionSalesReport AS
DBCC TRACEON(8765)
SELECT * FROM OPENQUERY(NAVISION, 'SELECT * FROM Sales_Line sls, Customer cust WHERE sls.Sell_to_Customer_No_ = cust.No_')
RETURN
GO
To clarify its text: TRACEON(8765) directive allows working with the data results of variable length returned by C/ODBC driver. If we don't use this directive ? we can't pull the results from Navision tables ? we will get errors, like this:
OLE DB error trace [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, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[MSDASQL].Ship_to_Filter'. The expected data length is 250, while the returned data length is 1.
OPENQUERY statement opens linked server and transfers its SQL command, and in its turn returns the result set. To test the procedure type in this command in SQL Server Query Analyzer: EXEC NavisionSalesReport 5. Now ? launch Crystal Reports, use standard Wizard, create new OLE DB (ADO) connection with our MS SQL 2000 Server and select NavisionSalesReport procedure in the list. As the report fields we can use Description, Quantity, Line_Amount and Discount_Amount fields. You can group by name Name ? this is customer name. Our report 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 nationwide Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, Boston, San Francisco, San Diego, Los Angeles, Houston, Dallas, Atlanta, Miami, Montreal, Toronto, Vancouver, Moscow, Europe and internationally (http://www.albaspectrum.com), he is Microsoft CRM SDK, Navision, C#, VB.Net, SQL, Oracle, Unix developer.
![]() |
|
![]() |
|
![]() |
|
![]() |
It would be easy to think, like most people apparently... Read More
We are in a transition phase in the Managerial Administration... Read More
Since technology changes so quickly, it is hard to begin... Read More
The purpose of one of our projects was MS Exchange... Read More
While Adobe is the most known maker of PDF tools,... Read More
Just stress testing one of the latest Linux distributions. Been... Read More
You would like to protect your documents, wouldn't you? Reasons... Read More
For a windows user like me, just can watch with... Read More
Document Manager and Version HistoryIn previous articles I have discussed... Read More
Corporate ERP/MRP selection might be tough one, especially considering very... Read More
Looks like Microsoft Great Plains becomes more and more popular,... Read More
1. With mapping software you can create a report that... Read More
Although we don't know whether Microsoft ever envisioned such a... Read More
So let's begin crunching down these 300 images using Adobe... Read More
Spyware is software or hardware installed on a computer without... Read More
DBxtra is a powerful query and reporting tool that hides... Read More
Microsoft Great Plains serves the wide spectrum of horizontal markets.... Read More
When Great Plains Software was designing and developing Great Plains... Read More
Anti-virus software is used to find, remove or fix files... Read More
Microsoft Business Solutions CRM is web-based CRM application, deploying all... Read More
If you're online using a dialup Internet connection, you'll probably... Read More
Lotus Domino/Notes ? Microsoft Great Plains tandem as ERP with... Read More
Navision Software was purchased by Microsoft and now it is... Read More
Great Plains Fixed Assets Management module is a robust tool... Read More
There are several kinds of software piracy. The bottom line... Read More
Language development computer: Computer-based method for aiding language development seems... Read More
This article will not attempt to advocate the use of... Read More
Make-or-Break Factors in Success and ProfitabilityFor quick printers, estimating can... Read More
What is Interactive Mapping?Interactive mapping is a visual display medium... Read More
Every day millions of new web documents emerge on the... Read More
Our hosted solution allow you to run your own search... Read More
Great Plains Integration Manager scripting and translation - overview for... Read More
What is Software?Software is a set of instruction written to... Read More
As of now - Great Plains Dynamics/eEnterprise is transformed/renamed into... Read More
Stop the Runaway MouseWhat's the "runaway mouse?" You've seen it...you... Read More
After seeing many people complain about their weak Internet security... Read More
With the advent of 'Service Pack 2' for Windows XP... Read More
While paper labeling CDs and DVDs may appear to be... Read More
Finally, you have some time to personalize your desktop with... Read More
Buying accounting software is a major investment. It's an important... Read More
Microsoft CRM customization techniques are very diversified and based on... Read More
Just the thought of a duel-boot scares many people away,... Read More
I provide, here clear explanations and a count of function... Read More
Imagine something that follows you home and sets itself up... Read More
I suggest that you do not spend a lot of... Read More
Microsoft Retail Management (RMS) and Microsoft Great Plains are retail... Read More
Microsoft Great Plains is one of the Microsoft Business Solutions... Read More
Need software to record your voice, streaming audio or musical... Read More
Rapid Application Development (RAD) is a software development methodology. In... Read More
The US House of Representatives has recently passed the "Spy... Read More
You turn on your computer, and it doesn't look quite... Read More
You probably didn't casually invite, or extend a formal attendance... Read More
Microsoft Business Solutions Great Plains was historically designed to serve... Read More
In today's business world it's all but impossible to escape... Read More
If you feel intimidated when someone tries to teach you... Read More
Microsoft Business Solutions Great Plains has full-featured manufacturing set of... Read More
Stealing company information used to be the specialty of spies... Read More
An integral part of any quality CRM system is lead... Read More
Assertion facility is added in J2SE 1.4. In order to... Read More
As you probably know, when Microsoft purchased Great Plains Software... Read More
While I was preparing some personal background information for a... Read More
Following tips help you to learn a software in lesser... Read More
Many reasons made GBM a unanimous choice for experts, one... Read More
Are Spreadsheets Robbing your Enterprise of Competitive Advantage?'90% of "average"... Read More
If you've been using MySQL database to store your important... Read More
Microsoft Business Solutions Great Plains as new ERP for multinational... Read More
Software |