 WebProWorld
Dev Forum |
MS SQL troubles
Okay, I am looking for a way to get a database out of MSSQL server(Ithink). I have asked three times in the ticket system, how to access the database so I can export it, or see it at least.
The Data from MySQL can't show up at browser
I have been learning for pHP in few days and I have problem. And the problem make me very confuse. I want to show some data from MySQL into my browser Internet Explorer 6.0. But it can't make it.
intergrate flash into a access database
I wanted to know is there a way to add flash to an access database. On my site we have added two parts, one is where customers can sell cars and the other is where they can part there cars out (like an online junk yard).
|
|
|
|
12.29.04
Navision Database Access Via C/ODBC WebService/ASP.NET Application
 By Boris Makushkin
Navision together with Microsoft Great Plains, Axapta, Solomon, Microsoft CRM and Microsoft RMS are now supported by Microsoft Business Solutions.
Navision has various customization options. Today we will describe a simple case of using the C/ODBC driver. This driver and technology allows you to work with a Native or C/SIDE Navision database. Navision is also available on Microsoft SQL Server - in this case you use traditional Microsoft technologies, such as a OLEDB or MS SQL Server driver to open the ADO.NET connection. Our goal is to help IT departments to support and tune Navision with in-house expertise and skills.
The topic of this article is Navision Attain database access through Webservice, connected to Navision via C/ODBC based Linked Server - the mechanism available in MS SQL Server 2000 and transfer the results to an ASP.NET application. Our goal will be an ASPX page accessing Navision Customers.
Let's begin
1. In our case we will use Navision Attain 3.6 with Navision Database Server, Navision Application Server and Navision Client. These components are installed on Windows XP. You also need to install C/ODBC component form Navision Attain CD.
2. Let's create ODBC DSN for Navision data access. Select Control Panel -> Administrative Tools -> Data Sources (ODBC). Then select System DSN tab and press the Add button. We'll use C/ODBC 32-bit data access driver. We'll name Data Source Name Navision, Connection leave Local. As the database (Database button) select \Program Files\Navision Attain\Client\database.fdb (demo database). Then click Company button - we'll use CRONUS demo company. It is important for C/SIDE correct database access to setup proper options for C/ODBC connection. Press Options button and look at the options available - we'll need Identifiers parameter - it defines identifiers types, which will be transferred to the client application. In order to work correct with MS SQL Server 2000 with C/ODBC source we need to use these type: "a-z,A-Z,0-9,_". Now DNS is done. Let's create Linked Server.
3. Open MS SQL Server Enterprise Manager. Open server tree for the server, which you plan to use, for this server open Security folder and Lined Servers. With right click select New Linked Server in context menu. In the dialog box opened in the Provider Name select Microsoft OLE DB Provider for ODBC Drivers. Let's name our Linked Server NAVISION. In Data Source string enter ODBC DSN name - NAVISION in our case. Linked Server is ready! Let's select tables list and look at the data from Navision Attain database.
4. Next we need to create a small stored procedure for sales data selection. Here is the text of the procedure:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE NavisionCustomers AS
DBCC TRACEON(8765)
SELECT No_, Name, Address, City, Contact FROM OPENQUERY(NAVISION, 'SELECT * FROM Customer')
RETURN
Let's clarify some points here. TRACEON(8765) directive allows us to work with the data of variable length, returned by C/ODBC driver. Without it we can not select Navision tables fields - we will have these errors:
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 command opens linked server and gives it execution request, and returns record set selected. Directives ANSI_NULLS and ANSI_WARNINGS are required - they provide the possibility of the execution for heterogeneous requests. To test the procedure you can give its name in MS SQL Query Analyzer - EXEC NavisionCustomers
Read the Rest of the Article.
About the Author:
Boris Makushkin is senior software developer in Alba Spectrum Technologies – US nation-wide Great Plains, Microsoft CRM customization company, based in Chicago and having locations in multiple states and internationally (www.albaspectrum.com ), he is Unix, SQL, C#.Net, Crystal Reports, Microsoft CRM SDK and Exchange Server SDK developer. You can reach Boris: borism@albaspectrum.com |
