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