Error loading package

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

disenfranchised wrote:

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

So you say it fails intermittently? Well that's weird.

Its failing because it cannot validate one of its external data sources. The fact that its intermittent leads me to suggest that there is some outside influence that is causing this. network connectivity perhaps? I'm just speculating here.

-Jamie

|||

I have suspected that it might be something to do with the permissions on the domain account that the SQLAgent runs as.

Something like the account was getting locked perhaps.

However if that was the case I'd expect to see 5-6 failures as the job runs every 5 minutes, and the domain keeps accounts locked for 30 minutes. We only ever seem to get isolated failures, but about 6 times per day (which out of 288 executions isn't to bad)

|||

Hi

I have the same problem with my SSIS packages.

I'm still developing the package so it isn't finished yet. But on each step I put it into production to see, how it behaves.

In adtition to you, I noticed, that this error occurs more often, when the package is stored in MSDB store. Previously I have had packages stored in SQL's FileSystem store and error occurs maybe twice or three times per week. Now it occurs several times per day.

I use the domain user and id in Administators group on the server.

I execute the package on the server where the inserting database is.

I execute the package every minute.

BR

ZigaP

|||Again, it sounds like an intermittent connectivity issue.|||

I think the network connectivity is not an issue.

It cannot be, because I run packages on the server where the database is.

what about this: if I replace the name of the server with "(local)"... can this solve the problem?

|||My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.
|||

FarmerChet wrote:

My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.

I've not observed this in my packages. So are you here with a question, or just posting your experience? There are so many things that influence package execution, and until you are sure you have a perfect environment, I wouldn't take your post with too much credit.|||

Hm... After my last post error occured only once.

I didn't do anything from my last post. Also admins on server didn't do anything...

Is server has to get used to these packages to run well?

BR
ZigaP

Error loading package

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

disenfranchised wrote:

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

So you say it fails intermittently? Well that's weird.

Its failing because it cannot validate one of its external data sources. The fact that its intermittent leads me to suggest that there is some outside influence that is causing this. network connectivity perhaps? I'm just speculating here.

-Jamie

|||

I have suspected that it might be something to do with the permissions on the domain account that the SQLAgent runs as.

Something like the account was getting locked perhaps.

However if that was the case I'd expect to see 5-6 failures as the job runs every 5 minutes, and the domain keeps accounts locked for 30 minutes. We only ever seem to get isolated failures, but about 6 times per day (which out of 288 executions isn't to bad)

|||

Hi

I have the same problem with my SSIS packages.

I'm still developing the package so it isn't finished yet. But on each step I put it into production to see, how it behaves.

In adtition to you, I noticed, that this error occurs more often, when the package is stored in MSDB store. Previously I have had packages stored in SQL's FileSystem store and error occurs maybe twice or three times per week. Now it occurs several times per day.

I use the domain user and id in Administators group on the server.

I execute the package on the server where the inserting database is.

I execute the package every minute.

BR

ZigaP

|||Again, it sounds like an intermittent connectivity issue.|||

I think the network connectivity is not an issue.

It cannot be, because I run packages on the server where the database is.

what about this: if I replace the name of the server with "(local)"... can this solve the problem?

|||My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.|||

FarmerChet wrote:

My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.

I've not observed this in my packages. So are you here with a question, or just posting your experience? There are so many things that influence package execution, and until you are sure you have a perfect environment, I wouldn't take your post with too much credit.|||

Hm... After my last post error occured only once.

I didn't do anything from my last post. Also admins on server didn't do anything...

Is server has to get used to these packages to run well?

BR
ZigaP

Error loading package

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

disenfranchised wrote:

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

So you say it fails intermittently? Well that's weird.

Its failing because it cannot validate one of its external data sources. The fact that its intermittent leads me to suggest that there is some outside influence that is causing this. network connectivity perhaps? I'm just speculating here.

-Jamie

|||

I have suspected that it might be something to do with the permissions on the domain account that the SQLAgent runs as.

Something like the account was getting locked perhaps.

However if that was the case I'd expect to see 5-6 failures as the job runs every 5 minutes, and the domain keeps accounts locked for 30 minutes. We only ever seem to get isolated failures, but about 6 times per day (which out of 288 executions isn't to bad)

|||

Hi

I have the same problem with my SSIS packages.

I'm still developing the package so it isn't finished yet. But on each step I put it into production to see, how it behaves.

In adtition to you, I noticed, that this error occurs more often, when the package is stored in MSDB store. Previously I have had packages stored in SQL's FileSystem store and error occurs maybe twice or three times per week. Now it occurs several times per day.

I use the domain user and id in Administators group on the server.

I execute the package on the server where the inserting database is.

I execute the package every minute.

BR

ZigaP

|||Again, it sounds like an intermittent connectivity issue.|||

I think the network connectivity is not an issue.

It cannot be, because I run packages on the server where the database is.

what about this: if I replace the name of the server with "(local)"... can this solve the problem?

|||My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.
|||

FarmerChet wrote:

My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.

I've not observed this in my packages. So are you here with a question, or just posting your experience? There are so many things that influence package execution, and until you are sure you have a perfect environment, I wouldn't take your post with too much credit.|||

Hm... After my last post error occured only once.

I didn't do anything from my last post. Also admins on server didn't do anything...

Is server has to get used to these packages to run well?

BR
ZigaP

sql

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:
>
>
|||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:
>
>

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,
>

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 pla
n
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 ordi
nal
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 pl
an
>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 ord
inal
>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:
>
>|||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:
>
>

Error handling when calling external sql-Files

I am using SQL Server 2000. I have some files with SQL-Statements.

The SQL-Serveragent runs jobs which execute the SQL-Files:
(e.g. osql /E /n /i \\server\d$\lager_pool.sql)

How can I implement an error handling.

If an error occurs, the script stops, and I can't read the variable
@.error

My script - table xy doesnt exist

SELECT * FROM XY
SELECT @.@.error
SELECT 33

The execution stops with an error after the first line

Thanks for your help.
aaapaulHi

Look at using the -b flag when you call osql, there is more information in
Books Online.

John

<lvpaul@.gmx.net> wrote in message
news:1128067412.114048.12840@.g44g2000cwa.googlegro ups.com...
I am using SQL Server 2000. I have some files with SQL-Statements.

The SQL-Serveragent runs jobs which execute the SQL-Files:
(e.g. osql /E /n /i \\server\d$\lager_pool.sql)

How can I implement an error handling.

If an error occurs, the script stops, and I can't read the variable
@.error

My script - table xy doesnt exist

SELECT * FROM XY
SELECT @.@.error
SELECT 33

The execution stops with an error after the first line

Thanks for your help.
aaapaul|||Thanks, but it doesnt work

This is my script - its a file c:\test.sql

declare @.errorcode int
select * from abc
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

The table "abc" dont exist.

If I call the script

osql /E /n /ic:\test.sql (with or without /b) the insert anweisung
instruction wont be executed.

I want something like ON ERROR RESUME NEXT. Is this possible ?

Thanks
aaapaul|||Hi

You may want to try using EXEC for your select statement

declare @.errorcode int
EXEC ('select * from abc')
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

John

<lvpaul@.gmx.net> wrote in message
news:1128491173.083475.231000@.o13g2000cwo.googlegr oups.com...
Thanks, but it doesnt work

This is my script - its a file c:\test.sql

declare @.errorcode int
select * from abc
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

The table "abc" dont exist.

If I call the script

osql /E /n /ic:\test.sql (with or without /b) the insert anweisung
instruction wont be executed.

I want something like ON ERROR RESUME NEXT. Is this possible ?

Thanks
aaapaul