I need to be able to insert and update records in a table in a Navision Financials 3.70 database from a trigger in SQL Server 2000 (SP3a). The only way I can see to do that is to have Navision as a linked server. The problem is that I can’t get that to work. Any help would be GREATLY appreciated! I have Navision set up as an ODBC connection on a development machine. This ODBC connection is set up as a linked server in SQL Server. Right now, I’m just trying to do a SELECT statement and am having problems even getting that to work. I have tried the following in a query analyzer window: SELECT * FROM OpenQuery(Navision, ‘SELECT Name, Description, “Default Column Layout”, “Analysis View Name” FROM “Acc_ Schedule Name”’) But this gives me the following error: Server: Msg 7347, Level 16, State 1, Line 4 OLE DB provider ‘MSDASQL’ returned an unexpected data length for the fixed-length column ‘[MSDASQL].Name’. The expected data length is 11, while the returned data length is 8. OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName=‘MSDASQL’, TableName=’[MSDASQL]’, ColumnName=‘Name’, ExpectedLength=‘11’, ReturnedLength=‘8’]. I have no control over the schema on the Navision server. I believe the problem is a conformance issue with the Navision ODBC driver. I executed an ODBC driver conformance tool (http://support.microsoft.com/default.aspx?scid=kb;EN-US;279882) which analyzes conformance with Microsoft OLE DB Provider for ODBC Drivers. The tool showed several ODBC API functions that are unsupported as well as several attributes that were in error for the Navision ODBC driver. I’m running the following driver versions: Navision (codbc.dll) 3.70.00.13164 MSDASQL 2000.81.9042.00 Note that the same ODBC connection works fine with Microsoft Query (via Excel), and also in a standalone Visual Basic application. But I need to be able to run queries in a trigger! As mentioned above, any help would be GREATLY appreciated! Any help at all: ideas for fixes, possible architectural design changes, anything. Thanks in advance for your help! Also, does anybody know about a reference for the Navision flavor of SQL?
C/ODBC does not work with the OLEDB provider for ODBC. The Code field type has incorrect meta data - C/ODBC says it is fixed length when it is not. It needs a fix in C/ODBC.