Post reply

ODBC Connection In A Trigger

  • April 7, 2004 at 3:15 pm

    #82365

    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!


    I Only Work Here......

  • April 8, 2004 at 10:24 am

    #502180

    I am not familiar with the Navision database, but have you tried the simple things to try and bypass the reported problem?

    Such as converting the problem fixed length field to variable length :

    SELECT * FROM OpenQuery(Navision, 'SELECT CONVERT(VARCHAR(11), Name) as Name, Description, "Default Column Layout", "Analysis View Name" FROM "Acc_ Schedule Name"')

    ....using whatever is the equivalent of CONVERT and VARCHAR on the Navision database.

    Or maybe just including the field in a calculation might fool it into calculating the return datalenth size differently:

    SELECT * FROM OpenQuery(Navision, 'SELECT Name + '' as Name, Description, "Default Column Layout", "Analysis View Name" FROM "Acc_ Schedule Name"')

    Regards

    Peter Tillotson

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply