Skip to main content

[archive] Linkserver With SQL Server 2005 and AcuXDBC

  • June 27, 2008
  • 5 replies
  • 0 views
D

[Migrated content. Thread originally posted on 25 June 2008]

I have been trying to set up a linked server with SQL server 2005 and AcuXdbc.

I have been able to set up the linkedserver in SQL server 2005.

But with my current set up I have limitations.

I have only be able to use OPENQUERY.

SELECT * FROM OPENQUERY(linkedserverName, 'select * from Table t');

I have not been able to update or insert.

I have tried to create an SYNONYM for linkedserverName.GENESIS.dbo.Table

This has failed, I don't know if I'm using the schema right or not.
This is the error, "Method is not supported by this provider.".
I don't see why this would work in winsql for querys and not in a linked server using odbc.

This is the first time I created a linked server in SQL server 2005 with ODBC;

I was wondering if any one else had attempted to do a Linked server?

If you have what did you do?

Is there a different option you would take?

This is we have half the information in one DB and the rest in the files that AcuXdbc is linked to.

I want to be able to have real time data with out a slowness ETL or SISS.

    5 replies

    D

    [Migrated content. Thread originally posted on 25 June 2008]

    I have been trying to set up a linked server with SQL server 2005 and AcuXdbc.

    I have been able to set up the linkedserver in SQL server 2005.

    But with my current set up I have limitations.

    I have only be able to use OPENQUERY.

    SELECT * FROM OPENQUERY(linkedserverName, 'select * from Table t');

    I have not been able to update or insert.

    I have tried to create an SYNONYM for linkedserverName.GENESIS.dbo.Table

    This has failed, I don't know if I'm using the schema right or not.
    This is the error, "Method is not supported by this provider.".
    I don't see why this would work in winsql for querys and not in a linked server using odbc.

    This is the first time I created a linked server in SQL server 2005 with ODBC;

    I was wondering if any one else had attempted to do a Linked server?

    If you have what did you do?

    Is there a different option you would take?

    This is we have half the information in one DB and the rest in the files that AcuXdbc is linked to.

    I want to be able to have real time data with out a slowness ETL or SISS.
    My experience is that the only way to make AcuODBC and AcuXDBC work with a Linked Server is to use OpenQuery.

    Did you try this format for an update?...

    update openquery(linkedserver, ?select * from table where col6 is null?) set col6 = val;

    If you did and it failed was there an error message?

    I suspect it may be due to security settings on the files or the account that is being used.

    Regards,
    D

    [Migrated content. Thread originally posted on 25 June 2008]

    I have been trying to set up a linked server with SQL server 2005 and AcuXdbc.

    I have been able to set up the linkedserver in SQL server 2005.

    But with my current set up I have limitations.

    I have only be able to use OPENQUERY.

    SELECT * FROM OPENQUERY(linkedserverName, 'select * from Table t');

    I have not been able to update or insert.

    I have tried to create an SYNONYM for linkedserverName.GENESIS.dbo.Table

    This has failed, I don't know if I'm using the schema right or not.
    This is the error, "Method is not supported by this provider.".
    I don't see why this would work in winsql for querys and not in a linked server using odbc.

    This is the first time I created a linked server in SQL server 2005 with ODBC;

    I was wondering if any one else had attempted to do a Linked server?

    If you have what did you do?

    Is there a different option you would take?

    This is we have half the information in one DB and the rest in the files that AcuXdbc is linked to.

    I want to be able to have real time data with out a slowness ETL or SISS.
    I have tryed that it failed.

    This is the error.

    OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "Neither the isolation level nor a strengthening of it is supported.".
    OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "[TOD][ODBC Driver]NOTCAP: Driver not capable".


    This is the Update statement

    UPDATE OPENQUERY(LinkedServer, 'select * from TableA A WHERE A.ID = 12345') SET NAME = 'TEST'
    D

    [Migrated content. Thread originally posted on 25 June 2008]

    I have been trying to set up a linked server with SQL server 2005 and AcuXdbc.

    I have been able to set up the linkedserver in SQL server 2005.

    But with my current set up I have limitations.

    I have only be able to use OPENQUERY.

    SELECT * FROM OPENQUERY(linkedserverName, 'select * from Table t');

    I have not been able to update or insert.

    I have tried to create an SYNONYM for linkedserverName.GENESIS.dbo.Table

    This has failed, I don't know if I'm using the schema right or not.
    This is the error, "Method is not supported by this provider.".
    I don't see why this would work in winsql for querys and not in a linked server using odbc.

    This is the first time I created a linked server in SQL server 2005 with ODBC;

    I was wondering if any one else had attempted to do a Linked server?

    If you have what did you do?

    Is there a different option you would take?

    This is we have half the information in one DB and the rest in the files that AcuXdbc is linked to.

    I want to be able to have real time data with out a slowness ETL or SISS.
    I have tryed that it failed.

    This is the error.

    OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "Neither the isolation level nor a strengthening of it is supported.".
    OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "[TOD][ODBC Driver]NOTCAP: Driver not capable".


    This is the Update statement

    UPDATE OPENQUERY(LinkedServer, 'select * from TableA A WHERE A.ID = 12345') SET NAME = 'TEST'
    D

    [Migrated content. Thread originally posted on 25 June 2008]

    I have been trying to set up a linked server with SQL server 2005 and AcuXdbc.

    I have been able to set up the linkedserver in SQL server 2005.

    But with my current set up I have limitations.

    I have only be able to use OPENQUERY.

    SELECT * FROM OPENQUERY(linkedserverName, 'select * from Table t');

    I have not been able to update or insert.

    I have tried to create an SYNONYM for linkedserverName.GENESIS.dbo.Table

    This has failed, I don't know if I'm using the schema right or not.
    This is the error, "Method is not supported by this provider.".
    I don't see why this would work in winsql for querys and not in a linked server using odbc.

    This is the first time I created a linked server in SQL server 2005 with ODBC;

    I was wondering if any one else had attempted to do a Linked server?

    If you have what did you do?

    Is there a different option you would take?

    This is we have half the information in one DB and the rest in the files that AcuXdbc is linked to.

    I want to be able to have real time data with out a slowness ETL or SISS.
    Please try the update in this form:

    UPDATE OPENQUERY(LinkedServer, 'select * from TableA WHERE ID = 12345') SET NAME = 'TEST'
    D

    [Migrated content. Thread originally posted on 25 June 2008]

    I have been trying to set up a linked server with SQL server 2005 and AcuXdbc.

    I have been able to set up the linkedserver in SQL server 2005.

    But with my current set up I have limitations.

    I have only be able to use OPENQUERY.

    SELECT * FROM OPENQUERY(linkedserverName, 'select * from Table t');

    I have not been able to update or insert.

    I have tried to create an SYNONYM for linkedserverName.GENESIS.dbo.Table

    This has failed, I don't know if I'm using the schema right or not.
    This is the error, "Method is not supported by this provider.".
    I don't see why this would work in winsql for querys and not in a linked server using odbc.

    This is the first time I created a linked server in SQL server 2005 with ODBC;

    I was wondering if any one else had attempted to do a Linked server?

    If you have what did you do?

    Is there a different option you would take?

    This is we have half the information in one DB and the rest in the files that AcuXdbc is linked to.

    I want to be able to have real time data with out a slowness ETL or SISS.
    I have tried this in many forms. I'm thinking that the way I set up the linked server was wrong.

    Here is what I did, set up a server object linked server.

    Here are the only fields I filled in, Provider, Product name, and Data Source.

    Selected Microsoft OLE db Provider for ODBC Drivers as my Provider.

    The product name is, AcuXdbc.

    I have a Data Source set up using ODBC manager, so I have that as my Data Source.

    I'm using SqlServer 2005
    1 Attachments

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement