July 18, 2005 at 2:57 pm
Does anyone know how to get around the odbc error that is given when trying to update a linked server table. I have listed the general update, and the error below. This will be buried in dynamic sql afterwhile if it works.
update FSIDB.FSIDB.dbo.FMSSTYLE set PROCESS_DATE = CONVERT(char(8),GETDATE(),112)
Could not open table '"FSIDB"."dbo"."FMSSTYLE"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
Thanks,
July 18, 2005 at 3:15 pm
I have found a solution that works for now.
I have declared a new variable and set a default value on it.
In the the sproc I set the variable using the convert statement. I then
use the defined variable in the sql statement versus using the convert.
It worked fine.
Thanks
July 19, 2005 at 7:49 am
The subject caught my eye, cause I have a similar problem. I see that yours is solved... but I thought that you might have some thoughts for me on what to do. I am really at my witts end. I'm looking from a DBA perspective, and I am not very familiar with the development/app side.
We have an existing system, that was now migrated to a Consolidated SQL server environment. The old server was running on Windows 2000 with SQL2000 EE, SP3. New server has 5 instances of SQL2000 SE, SP3 running on Windows 2003.
The specific system has a linked server setup to point to mainframe DB2, via OLEDB Provider, via CAE. I can't seem to get the Linked Servers working again. It allows Reads and Writes, but not Updates. Gives following error:
OLE DB provider 'MSDASQL' could not UPDATE table '[MSDASQL]' because of
column 'ABC_CD'. The user did not have permission to write to the column. > [OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work
was done.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
returned 0x80040e21
It is definitely NOT the DB2, or SQL server privileges that is insufficient. I also eliminated the CAE versions (old box, test and prod, was on V6.1.0.31 and new on
V7.1.0.40, but upgraded old box, and still works), and configurations. Also the SQL versions and SP-levels (there test box also on SE SP3). I created a new Linked Server and new Datasourse on one of there old boxes, and it works perfectly. If I do the exact same thing on the new server, it does not work. I compared auths, and even the entries in the sysservers and sysxlogins etc, and can't see anything there either.
I read the following articles:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm /odbcproviderquery_based_updates.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefodbcprovspec.asp
According to all documentation on the OLE DB Provider for ODBC drivers (which we use in both the old and new environment), it indicates that cursor updates are not possible through OLE DB for ODBC.
Here is the query that they are executing:
UPDATE OPENROWSET
(
'MSDASQL',
'DSN=DB2P;UID=abcdef;PWD=abcdef',
'
SELECT A_CD, DESC, B_CD, C_CD
FROM ABCTB.TEST_TABEL
WHERE A_CD = ''12345''
'
)
SET B_CD = '12'
, C_CD = '012'
, DESC = '#TESTING 123'
There's also indications that a registry key/switch exists to bypass a time-out issue(which they are also experiencing in the new environment).
Article I read:
Introduction to OLE DB
OLE DB is a data access model from Microsoft. It uses the Component Object Model (COM) interfaces and, unlike ODBC, OLE DB does not assume that the data source uses a SQL query processor.
Adaptive Server Anywhere includes an OLE DB provider named ASAProv. This provider is ailable for current Windows and Windows CE platforms.
You can also access Adaptive Server Anywhere using the Microsoft OLE DB Provider for ODBC (MSDASQL), together with the Adaptive Server Anywhere ODBC driver.
Using the Adaptive Server Anywhere OLE DB provider brings several benefits:
· Some features, such as updating through a cursor, are not available using the OLE DB/ODBC bridge.
· If you use the Adaptive Server Anywhere OLE DB provider, ODBC is not required in your deployment.
· MSDASQL allows OLE DB clients to work with any ODBC driver but does not guarantee that you can use the full range of functionality of each ODBC driver. Using the Adaptive Server Anywhere provider, you can get full access to Adaptive Server Anywhere features from OLE DB programming environments.
I'd appreciate it if you have any thoughts/ideas/suggestions to share with me.
Thanks a million.
September 18, 2008 at 7:29 pm
Hi All,
Just wanted to pick your brains for a minute...
Each morning I have been importing information from another application into a sql database using stored procedures...
On a good day everything runs through okay.... but 9/10 I get this error msg 7306
"Could not open table '%ls' from OLE DB provider '%ls'. %ls" for linked server. The specified table or view does not exist or contains errors. "
The location of this error varies, but the error is the same.
To resolve this error I have to restart the database and run the stored procedure again, this seems to resolve the problem for me and the stored procedure runs okay.
Any suggestions on a long term solution to my problem...
Thanks so much...
September 24, 2008 at 8:14 am
I have a similar problem and a solution I discovered from reading articles on the internet is to put a unique index or key on that table.
However in my case the same query works ok on a SQL 2000 Server running on Windows 2000 Server.
However the same query give me an error when trying to execute the query from a sql 2000 on Windows 2003 box.
Any ideas why it works on one and not the other?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply