none
Linked Server using Microsoft OLE DB provider for ODBC Drivers

    Question

  • Good day,

    We have a vendor based system which uses the softvelocity odbc drivers.  We purchased the drivers so we could have access to our database to automate some tasks.  After installing the drivers and creating an ODBC entry, we could query the database using WinSQL.

    On Sql Server express, I created a linked server using Microsoft OLE DB provider for ODBC Drivers.  In executing the following select query:

    select lastname 
    from [skills manager]...emp 
    where lastname = 'Test';
    I receive the following error:

    Msg 7422, Level 16, State 1, Line 1
    OLE DB provider "MSDASQL" for linked server "skills manager" returned an invalid index definition for table "emp".


    Any insight or assistance would be appreciated. What's baffling is that I can use WinSQL and view the indexes; I'm not finding unsupported datatypes in the table. Just not sure what to look at next.

    Thanks for your time.

    GFrank
    Tuesday, December 29, 2009 5:20 PM

Answers

  • When you run a dml statement (insert, update, delete) against a linked server, a distributed transaction is started; when you run in in a tool like WinSql a simple transaction is used.
    It's controlled by the MSDTC = Distributed Transaction Coordinator; MSDTC has to run on both server, see: http://msdn.microsoft.com/en-us/library/ms190799.aspx

    But this error message sounds more that the linked server do not provide distributed transaction.

    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, December 30, 2009 6:47 AM

All replies

  • Additional information - using the below syntax, I am able to get a result set.  I'll be working on what syntax I need to use to update the information as well.

    SELECT   *

    FROM OPENQUERY([skills manager], 'select lastname  from emp where lastname = ''Test''')

    Tuesday, December 29, 2009 8:06 PM
  • Hello GFrank,

    See, in your first query you used a query with a full-qualified object name (server.database.schema.object) in T-SQL.
    This works fine, if the linked server is also a MS Sql Server, understanding T-SQL.

    For all other types of linked server you should use OpenQuery, as you done in your second query.

    E.g. if your linked server is Oracle and you want to use the Oracle function to_date in T-SQL, it would quit execution with an error, because T-SQL / MS Sql Server don't know the function to_date.
    So you have to use OpenQuery; a so called Pass-Through query. It's send to the linked server without beeing interpreted by Sql Server.  
    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Tuesday, December 29, 2009 8:21 PM
  • Thank you - I've only dealt with direct sql server linking in the past.  However, in updating the information, I get a different type of situation:

    Updating data in the linked server: 
    UPDATE OPENQUERY ([skills manager], 'SELECT * FROM emp') 
    SET lastname = 'Test1' WHERE lastname = 'Test';

    produces the following error:
    Msg 7390, Level 16, State 2, Line 1 The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "skills manager" does not support the required transaction interface.

    Using WinSQL, I can update just fine. Thoughts? Thank you.
    Tuesday, December 29, 2009 9:29 PM
  • When you run a dml statement (insert, update, delete) against a linked server, a distributed transaction is started; when you run in in a tool like WinSql a simple transaction is used.
    It's controlled by the MSDTC = Distributed Transaction Coordinator; MSDTC has to run on both server, see: http://msdn.microsoft.com/en-us/library/ms190799.aspx

    But this error message sounds more that the linked server do not provide distributed transaction.

    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, December 30, 2009 6:47 AM