error in query execution plan when using linked server

One of our jobs stopped working. It gets to a certain update statement and
never finishes. We've tried new linked servers. I looked at an execution plan
yesterday and then today I added another linked server to test with and got
this error when I tried to look at the execution plan - "OLE DB provider
'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
from the index rowset.]."
And now when I go back to using the original linked server I get the same
error.
Any ideas?
Thanks,
--
Dan D.I guess the issue as to why you get the error now on the
original linked server is answered in your post on settings.
In terms of the specific error you are getting, it can
depend on what provider you are using and what options you
have selected for the provider. I've seen the error when
using Index As Access path for the provider option. You
typically use the option when the data source is on the
same server as SQL Server. You can find more information on
this option in books online under the topic:
Keyset-Driven Cursors Requirements for OLE DB Providers
Try removing the option to see if that specific error goes
away.
In terms of troubleshooting why the job stopped working, you
can turn on a trace flag to try to get additional error
information. Execute:
dbcc traceon(7300,3604)
and then run the query used by the job in Query Analyzer.
You could also use profiler and capture the OLEDB Errors
event.
-Sue
On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>One of our jobs stopped working. It gets to a certain update statement and
>never finishes. We've tried new linked servers. I looked at an execution plan
>yesterday and then today I added another linked server to test with and got
>this error when I tried to look at the execution plan - "OLE DB provider
>'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
>'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
>[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
>from the index rowset.]."
>And now when I go back to using the original linked server I get the same
>error.
>Any ideas?
>Thanks,|||Yes that's the option. When I stopped using it the error went away.
I'll try the traceon and profiler.
Thanks Sue,
Dan D.
"Sue Hoegemeier" wrote:
> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >One of our jobs stopped working. It gets to a certain update statement and
> >never finishes. We've tried new linked servers. I looked at an execution plan
> >yesterday and then today I added another linked server to test with and got
> >this error when I tried to look at the execution plan - "OLE DB provider
> >'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
> >'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
> >[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
> >from the index rowset.]."
> >
> >And now when I go back to using the original linked server I get the same
> >error.
> >
> >Any ideas?
> >
> >Thanks,
>|||Hi,
I am developing an ole db provider and am getting the same error. It occurs
right after sql server reads the INDEXES schema rowset. I have set the
"Index as access path option" on and need to make it work with this option
on.
I have found that if I change the schema rowset to indicate that it is an
integrated index, the error goes away. Also, it seems odd to me that the
message is generated before the IOpenRowset call to open the index rowset
even occurs. There must be something in the schema rowset that sql server
uses to produce this error message.
What changes do I need to make to the index schema entries to prevent this
error ?
Thanks,
Bill Emerson
"Sue Hoegemeier" wrote:
> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >One of our jobs stopped working. It gets to a certain update statement and
> >never finishes. We've tried new linked servers. I looked at an execution plan
> >yesterday and then today I added another linked server to test with and got
> >this error when I tried to look at the execution plan - "OLE DB provider
> >'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
> >'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
> >[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
> >from the index rowset.]."
> >
> >And now when I go back to using the original linked server I get the same
> >error.
> >
> >Any ideas?
> >
> >Thanks,
>