Error linked server query after installing SP4 on SQL 2000

Hello
SQL 2000 SP3a run fine with an linked server query like
select * from openquery( linkedOlapServer, .. here is the MDX ...')
After installing SP4 build 2039 this query fails with error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'MSOLAP'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP
returned 0x80040154].
Appreciate hints from experience on which hot fix do I need to install?
Nic
Does changing the "AllowInProcess" to something non zero fix this problem?
http://msdn2.microsoft.com/en-us/library/ms190918.aspx
In Object Explorer, right-click the provider name and select Properties. The
following table describes the available provider options...
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Nic" <Nic@.discussions.microsoft.com> wrote in message
news:E4A1CCBC-331D-4C70-B83C-F1AFE5CFAA62@.microsoft.com...
> Hello
> SQL 2000 SP3a run fine with an linked server query like
> select * from openquery( linkedOlapServer, .. here is the MDX ...')
> After installing SP4 build 2039 this query fails with error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'MSOLAP'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP
> returned 0x80040154].
> Appreciate hints from experience on which hot fix do I need to install?
> Nic
>

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 in inserting value by trigger in linked server

Hi Pls help me
I have 2 db servers and its a lnked server
while am inserting value in source table (table having a trigger to raise ti
other table in defferent server) i am facing this error and Data is not
getting inserted.
error is
- Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ]
regards
KRP
Do you have the Distributed Transaction Coordinator running? On SQL Server
2000 it is one of the services in the SQL Server suite along with SQL Server
and SQL Agent.
RLF
"KRP" <KRP @.discussions.microsoft.com> wrote in message
news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> Hi Pls help me
> I have 2 db servers and its a lnked server
> while am inserting value in source table (table having a trigger to raise
> ti
> other table in defferent server) i am facing this error and Data is not
> getting inserted.
> error is
> - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ]
> regards
> KRP
|||Yes,
Its already started and every pre conf hasbeen done..
Please Can you send me a steps of creating a linked server?
Thanks
KRp
"Russell Fields" wrote:

> Do you have the Distributed Transaction Coordinator running? On SQL Server
> 2000 it is one of the services in the SQL Server suite along with SQL Server
> and SQL Agent.
> RLF
> "KRP" <KRP @.discussions.microsoft.com> wrote in message
> news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
>
>

Error in inserting value by trigger in linked server

Hi Pls help me
I have 2 db servers and its a lnked server
while am inserting value in source table (table having a trigger to raise ti
other table in defferent server) i am facing this error and Data is not
getting inserted.
error is
- Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The o
peration
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provi
der returned
message: New transaction cannot enlist in the specified transaction
coordinator. ]
regards
KRPDo you have the Distributed Transaction Coordinator running? On SQL Server
2000 it is one of the services in the SQL Server suite along with SQL Server
and SQL Agent.
RLF
"KRP" <KRP @.discussions.microsoft.com> wrote in message
news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> Hi Pls help me
> I have 2 db servers and its a lnked server
> while am inserting value in source table (table having a trigger to raise
> ti
> other table in defferent server) i am facing this error and Data is not
> getting inserted.
> error is
> - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB pro
vider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ]
> regards
> KRP|||Yes,
Its already started and every pre conf hasbeen done..
Please Can you send me a steps of creating a linked server?
Thanks
KRp
"Russell Fields" wrote:

> Do you have the Distributed Transaction Coordinator running? On SQL Serve
r
> 2000 it is one of the services in the SQL Server suite along with SQL Serv
er
> and SQL Agent.
> RLF
> "KRP" <KRP @.discussions.microsoft.com> wrote in message
> news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
>
>

Error in inserting value by trigger in linked server

Hi Pls help me
I have 2 db servers and its a lnked server
while am inserting value in source table (table having a trigger to raise ti
other table in defferent server) i am facing this error and Data is not
getting inserted.
error is
- Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ]
regards
KRPDo you have the Distributed Transaction Coordinator running? On SQL Server
2000 it is one of the services in the SQL Server suite along with SQL Server
and SQL Agent.
RLF
"KRP" <KRP @.discussions.microsoft.com> wrote in message
news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> Hi Pls help me
> I have 2 db servers and its a lnked server
> while am inserting value in source table (table having a trigger to raise
> ti
> other table in defferent server) i am facing this error and Data is not
> getting inserted.
> error is
> - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ]
> regards
> KRP|||Yes,
Its already started and every pre conf hasbeen done..
Please Can you send me a steps of creating a linked server?
Thanks
KRp
"Russell Fields" wrote:
> Do you have the Distributed Transaction Coordinator running? On SQL Server
> 2000 it is one of the services in the SQL Server suite along with SQL Server
> and SQL Agent.
> RLF
> "KRP" <KRP @.discussions.microsoft.com> wrote in message
> news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> > Hi Pls help me
> >
> > I have 2 db servers and its a lnked server
> > while am inserting value in source table (table having a trigger to raise
> > ti
> > other table in defferent server) i am facing this error and Data is not
> > getting inserted.
> > error is
> > - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> > could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> > to
> > begin a distributed transaction.
> > [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> > message: New transaction cannot enlist in the specified transaction
> > coordinator. ]
> >
> > regards
> > KRP
>
>

Error in accessing data via Linked Server

Hi,

I am trying to access the View through linked server connection .The linked server connection is between two sql server 2005.

The problem is I am not able to access 2 columns of nvarchar datatype of length 255, from the view through select statement.

I do a SELECT col1,col2 from VIEW,then getting the following error .

Cannot get the data of the row from the OLE DB provider "SQLNCLI" for linked server "SRVXPR". Could not convert the data value due to reasons other than sign mismatch or overflow.

Then I tried with

Select cast(Col1 as nvarchar) ,

cast(col2 as nvarchar) from VIEW,

I am able to get the values.

The collation type is same for all underlying tables and also for the servers.

Is there any restrictions in handling nvarchar data thru linked server/ anyother size limitation.

I don't want to use cast as this will lead to performance problem.

Thanks,

Philip

Seems as this is a problem of the OLEDB driver. You could send this to Microsoft as a BUG through the connect.microsoft.com portal, or in urgent cases call PSS to get the problem fixed.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

error help?

I created a linked server to access information in an excel spreadsheet
by using this code:

exec sp_addlinkedserver N'LINKEXCEL_OL',
@.srvproduct = N'',
@.provider = N'Microsoft.Jet.OLEDB.4.0',
@.datasrc = N'C:\dt.xls',
@.provstr = N'Excel 8.0;'

I created a login using this code:

EXEC sp_addlinkedsrvlogin 'LINKEXCEL_OL', 'false'

and I tried to run a query using this code:

select * from openquery(LINKEXCEL_OL, 'select * from [Sheet1$]')

Everything is ok until the select when I get this very descriptive error(sarcasm):

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

Does anyone have any suggestions? I am very lost and do nto see why this wouldn't work..May check this KBA (http://support.microsoft.com/default.aspx?scid=KB;en-us;Q314530%20.).

error handling with linked servers

Hi,
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
Stefano
The batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano
|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano
|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano
|||ok, thanks!

error handling with linked servers

Hi,
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
StefanoThe batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano|||ok, thanks!