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.
![]() |
|
![]() |
|
![]() |
|
![]() |
Well, even if the combination might look very unusual, we... Read More
If you are in the market for new staffing software,... Read More
If you have Microsoft Great Plains and support it for... Read More
It's all about turn times in the eMedia industry! The... Read More
Whether you need to close a sale, gather end-user feedback,... Read More
Great Plains Software Dynamics, Dynamics C/S+, eEnterprise were written on... Read More
Crystal Reports is the most flexible tool on the market... Read More
TikiWiki is open source software - it is written in... Read More
While I was in college, if you would have asked... Read More
Healthcare facilities such as clinics, hospitals, and biomedical laboratories can... Read More
Trying to figure out a stream in banning one email... Read More
There are several kinds of software piracy. The bottom line... Read More
Microsoft CRM is new player on the CRM software... Read More
Handling character strings in Java is supported through two final... Read More
MS CRM is very close to document workflow automation, including... Read More
Microsoft Great Plains has substantial mid-market share in the USA... Read More
Following tips help you to learn a software in lesser... Read More
Microsoft Great Plains serves majority of US based horizontal and... Read More
So let's begin crunching down these 300 images using Adobe... Read More
Most computer users use spreadsheets software such as Microsoft Excel... Read More
Sometimes your PC will start acting strange for no apparent... Read More
Fundraising software lets you connect with donors in a way... Read More
Many reasons made GBM a unanimous choice for experts, one... Read More
The major reason I recommend getting your hands on real... Read More
Spyware is software or hardware installed on a computer without... Read More
Microsoft Retail Management (RMS) and Microsoft Great Plains are retail... Read More
Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More
Microsoft Outlook is one of the most widely used software... Read More
Language development computer: Computer-based method for aiding language development seems... Read More
In 2004 Oracle, Inc. made its new step toward J2EE... Read More
You probably didn't casually invite, or extend a formal attendance... Read More
Domino server is a buffer between the operation system and... Read More
SOFTWARE PIRACY We regularly hear reports... Read More
Task Manager is a Windows system utility that displays thetasks... Read More
Just the thought of a duel-boot scares many people away,... Read More
How many steps does it take you to locate and... Read More
Microsoft PowerPoint has dramatically changed the way in which academic... Read More
Are you a whiz at calculating financial information? Not the... Read More
Today's business world is fast-paced. No matter what it is... Read More
Whether you need to close a sale, gather end-user feedback,... Read More
It's not very often I get excited about a software... Read More
We would like to give you pluses and minuses of... Read More
Microsoft CRM is CRM answer from Microsoft Business Solutions.The whole... Read More
In our small article we'll consider Microsoft Business Solutions Great... Read More
You might think you don't need a firewall... Read More
In this short FAQ style article we would like to... Read More
Microsoft Business Solutions Great Plains has substantial market share among... Read More
Microsoft Business Solutions CRM and IBM Lotus Notes Domino, being... Read More
.Net Framework is a platform or development environment to seamlessly... Read More
Designing without tables by using CSS layouts is fast becoming... Read More
Does Microsoft care for WebmastersIt's always been a problem with... Read More
Microsoft Business Solutions Great Plains is very good fit for... Read More
Using professional icons in your application or website can bring... Read More
In 2004 Oracle, Inc. made its new step toward J2EE... Read More
SyncUp, a file synchronizer is designed to assist the home... Read More
Crystal Reports is the most flexible tool on the market... Read More
Well, even if the combination might look very unusual, we... Read More
Microsoft Business Solutions Great Plains has substantial market share and... Read More
Java has come along a long way. Many would agree... Read More
TCO (Total Cost Ownership) is the buzzword in... Read More
In part III of this ISDN primer, we learned that... Read More
Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More
When you visit department stores and see that majority of... Read More
Microsoft CRM and IBM Lotus Notes Domino seem to be... Read More
Every day millions of new web documents emerge on the... Read More
If you've been using MySQL database to store your important... Read More
Software |