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.
![]() |
|
![]() |
|
![]() |
|
![]() |
There are certain pluses and minuses in both cases and... Read More
Whether you are a small consultancy firm, a medium sized... Read More
The Windows Indexing Service provides you with the ability to... Read More
As Mozilla Firefox nears 10% market share, with well over... Read More
Have you noticed WordPerfect is gearing up for a comeback... Read More
How do you run a program on a remote server... Read More
Microsoft Business Solutions Great Plains has many years of successful... Read More
Microsoft Office program is a programming tool called Visual Basic... Read More
Just the thought of a duel-boot scares many people away,... Read More
Adobe Photoshop is one of the world's leading graphics editing... Read More
Midsize business or non-profit organization should decide if one-vendor solution... Read More
Microsoft Business Solutions Great Plains was historically designed to serve... Read More
Combining Microsoft Business Solutions Great Plains ERP with non-Microsoft Business... Read More
eStore Advantage allows front-office applications to communicate with back-office business... Read More
Fortunately one of the most common reasons cited for the... Read More
A few months back I really got sick of my... Read More
Looks like Microsoft Great Plains becomes more and more popular,... Read More
Microsoft Great Plains is main mid-market application from Microsoft Business... Read More
Icons are used everywhere; right from software applications, to internet... Read More
You probably didn't casually invite, or extend a formal attendance... Read More
How would you like to prevent spyware and adware from... Read More
Now that spyware is the single most dangerous threat to... Read More
We live in a post-industrial age where information is the... Read More
1. Replace Dexterity cursor with SQL Stored Procedure Dexterity... Read More
NOTE: Please take time to read on - it may... Read More
Designing without tables by using CSS layouts is fast becoming... Read More
As we could imagine, if you are reading this article... Read More
Pirated software is on the increase and now accounts for... Read More
Spyware and malware are large problems for Internet users today... Read More
Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS)... Read More
A LOT OF UNWANTED FILES.When you uninstall an item of... Read More
Microsoft Business Solutions Small Business Manager is scaled down Great... Read More
The purpose of one of our projects was MS Exchange... Read More
XML Server can be a Web Server that stores the... Read More
And kill the best layout software in the process of... Read More
Healthcare facilities such as clinics, hospitals, and biomedical laboratories can... Read More
SOFTWARE PIRACY We regularly hear reports... Read More
Microsoft Great Plains is one of the Microsoft Business Solutions... Read More
Today's business world is fast-paced. No matter what it is... Read More
Now is the time to look at an alternative to... Read More
Microsoft Business Solutions CRM and IBM Lotus Notes Domino, being... Read More
In today's business world it's all but impossible to escape... Read More
With any good luck and a good amount of hard... Read More
Once upon a time not so long ago, there was... Read More
Spyware is software or hardware installed on a computer without... Read More
Just imagine: you are walking, say, towards your car, and... Read More
Great Plains Software Dynamics, Dynamics C/S+, eEnterprise were written on... Read More
Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS)... Read More
If you have Great Plains Dynamics/eEnterprise (version 6.0 or earlier)... Read More
1. With mapping software you can create a report that... Read More
In this small article we will show you the possible... Read More
Some companies that are in need of fleet management may... Read More
Microsoft Business Solutions CRM is present several years on the... Read More
Microsoft Office program is a programming tool called Visual Basic... Read More
Hi, Guys,I believe a lot of programmers are trying to... Read More
Microsoft Great Plains is becoming more and more popular and... Read More
Microsoft Great Plains serves majority of US based horizontal and... Read More
Microsoft Business Solutions Navision is main ERP application for European,... Read More
Task Manager is a Windows system utility that displays thetasks... Read More
Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics... Read More
Although we don't know whether Microsoft ever envisioned such a... Read More
Following tips help you to learn a software in lesser... Read More
Spyware is like the new technological nuclear weapon on the... Read More
Microsoft Business Solutions ? Great Plains is designed to meet... Read More
While Adobe is the most known maker of PDF tools,... Read More
Microsoft Business Solutions Great Plains was historically designed to serve... Read More
Software |