Highlighted
Absent Member.
Absent Member.
971 views

[archive] Linkserver With SQL Server 2005 and AcuXDBC

[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.
0 Likes
4 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] Linkserver With SQL Server 2005 and AcuXDBC

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,
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Linkserver With SQL Server 2005 and AcuXDBC

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'
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Linkserver With SQL Server 2005 and AcuXDBC

Please try the update in this form:

UPDATE OPENQUERY(LinkedServer, 'select * from TableA WHERE ID = 12345') SET NAME = 'TEST'
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Linkserver With SQL Server 2005 and AcuXDBC

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
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.