March 12, 2004 at 10:06 am
I am getting an error trying to update a table in DB2 via SQL Server using a Linked Server. I can query the table using OPENQUERY but not via four-part name.
I can do this...
SELECT
Select * from OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn = 2')
When I try to Query using four-part names I get the following error...
7399 OLE DB Provider 'MSDASQL' reported an error. The provider does not support the necessary method.
UPDATE
When I try to update using this...
Update OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn=2') Set frst_nm = 'Fred'
I get the following error...
The OLE DB provider 'MSDASQL' indicates that the object has no columns.
When I try to update using four part names I get the error that says that the provider does not support the necessary method.
OTHER INFO
I was originially using version 6.1 of the db2 client (db2odbc.dll) but tried the 7.1 client and got the same error.
I believe we are using version 6.1 of db2 connect.
The mainframe db2 is version 7.0 release 1.0
Any help would be greatly appreciated. I referenced Microsoft Knowledge Base Article #270119 to get the workarounds that I attempted...
Gary
March 15, 2004 at 8:00 am
This was removed by the editor as SPAM
March 22, 2004 at 1:47 pm
I still have not resolved my original issue so I tried to accomplish the same task in a different way...
Since I am unable to update via openquery I now am trying to use DTS to transfer data from SQLserver to Db2 and am getting an error doing this as well.
I can set up a connection to SQLServer. I can set up a connection to DB2...I can see all of my tables in both environments, I select my table and select Append rows....everybody is happy right? wrong. When I run the package I get an error on my first destination column..."Column name "ID_CASE" was not found" I have tried this with multiple tables and it always errors out on the first column in my destination table...any thoughts?
I'm at the end of my rope here on this one...I need to communicate with a yet to be written Cobol batch job...If I can't get this to work my backup plan is to do my processing in SQL Server and then FTP results so that the batch can process via the Ftp'd flat file...but I'd rather not have to do this.
on my knees and beggin...
Gary
March 22, 2004 at 9:10 pm
Couple of things to check.
First Check that the provider MSDA ->OLEODBC provider is allowed adhoc access. Given your mentioning db2odbc.dll.
Second Make sure the provider support IOpenRowset, IRowsetLocate, and IRowsetChange methods.
THIRD SET XACT_ABORT ON for the updates.
Fourth, Check that the odbc provider for OLEDB limitations do not apply to your columns.
Peter Evans (__PETER Peter_)
From MSDN
________________
Check that the provider is allowed adhoc access
For each instance of SQL Server 2000, members of the sysadmin fixed server role can enable or disable the use of ad-hoc connector names for an OLE DB provider using the SQL Server DisallowAdhocAccess property. When ad-hoc access is enabled, any user logged on to that instance can execute SQL statements containing ad-hoc connector names referencing any data source on the network that can be accessed using that OLE DB provider. To control access to data sources, members of the sysadmin role can disable ad-hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. By default, ad-hoc access is enabled for the SQL Server OLE DB provider, and disabled for all other OLE DB providers.
Transact-SQL UPDATE and DELETE statements can reference remote tables only if the following conditions are met by the OLE DB provider that is used to access the remote table:
The Microsoft OLE DB Provider for SQL Server supports these interfaces only on a table that has a unique index. Consequently, UPDATE or DELETE statements are permitted against a remote table in another instance of Microsoft® SQL Server™ only if the table has a unique index.
The XACT_ABORT SET option should be set to ON for INSERT, UPDATE, or DELETE statements to work.
March 23, 2004 at 9:06 am
Hi,
Can some one please help, I am facing the following problem.
I am using SQL Server 2000 with SP3 on Windows 2003 server and Oracle 8i.
I am having a linked server from SQL Server 2000 to connect to Oracle 8i.
Connection is fine. I can do query from oracle. I can update and delete in Oracle as well.
But when I am doing updates in transaction mode, I am getting the following error.
The operation could not be performed because the OLE DB Provider 'MSDAORA' was unable to begin a distributed transaction.
MS DTS is also running. Also tried SET XACT_ABORT ON but same problem.
Also updated the registiry entries according to KB Q264012
Please help me.
Regards,
Atiq Rahman
March 23, 2004 at 10:12 am
Interestingly I am having the self same problem on SQL 2k SP3a on Windows 2000 Advance Server, MDAC 2.71, Oracle 8i.
All the entries are set as Microsoft suggest. I wouldn't use a begin tran, but unfortunaely it is forced upon me as a stored proc is being called by Remedy which uses transactions when it runs the sp. I'd love to have this finxed as it would resolve so many problems.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply