how to set SQL Server OLE DB provider index option , It gives error ?

All ,

I have created link server and i want to set all appropriate Setting for the Provider option

Provider used : - Microsoft OLE DB provider for SQL server
After setting “Index as Access Path” check box to true I have encountered
Following error

Server: Msg 7319, Level 16, State 1, Procedure Jobs, Line 2OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_T_Jobs' with incorrect bookmark ordinal 0.OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

Note :- Remote Query Icon Show 98%

Please let me know how to fix the problem and how to make sure distributed query Uses the proper index on remote Link server

Regards,
RahulB

microsoft ole db provider for sql server is not an index provider. it won't be able use index as access path from the remote sql server.

|||

I am facing the same issue. However, i don't much care which index on the oracle box a query uses...i just want the data. How do i avoid this error?

To test things, i took the table on oracle that was generating this error and duplicated it (with data) but without any indexes, and the data returns successfully.

Obviously the idea of removing indexes from oracle tables so the SQL Server can link-server connect to them is not reasonable. My method of connection is the 4-part method. Open query works even on the table...but i don't want to be forced to use that technique.

Here are the two queries...(Query 1 fails, Query 2 works):

1. SELECT * FROM ORCLPAO..SERVICE.BI_EVENT
2. SELECT TOP 10 * FROM OPENQUERY (orclpao, 'select * from service.bi_event')

Query 1 generates this error:

Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'EVENT_APTNUM_XS' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

how to set SQL Server OLE DB provider index option , It gives error ?

All ,

I have created link server and i want to set all appropriate Setting for the Provider option

Provider used : - Microsoft OLE DB provider for SQL server
After setting “Index as Access Path” check box to true I have encountered
Following error

Server: Msg 7319, Level 16, State 1, Procedure Jobs, Line 2OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_T_Jobs' with incorrect bookmark ordinal 0.OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

Note :- Remote Query Icon Show 98%

Please let me know how to fix the problem and how to make sure distributed query Uses the proper index on remote Link server

Regards,
RahulB

microsoft ole db provider for sql server is not an index provider. it won't be able use index as access path from the remote sql server.

|||

I am facing the same issue. However, i don't much care which index on the oracle box a query uses...i just want the data. How do i avoid this error?

To test things, i took the table on oracle that was generating this error and duplicated it (with data) but without any indexes, and the data returns successfully.

Obviously the idea of removing indexes from oracle tables so the SQL Server can link-server connect to them is not reasonable. My method of connection is the 4-part method. Open query works even on the table...but i don't want to be forced to use that technique.

Here are the two queries...(Query 1 fails, Query 2 works):

1. SELECT * FROM ORCLPAO..SERVICE.BI_EVENT
2. SELECT TOP 10 * FROM OPENQUERY (orclpao, 'select * from service.bi_event')

Query 1 generates this error:

Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'EVENT_APTNUM_XS' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

How to set IDENTITY_INSERT to ON?

Hi,
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value
for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
OFF.
/checkout.asp, line 112
Is this a database setting error? Can anyone instruct us on how to set the
IDENTITY_INSERT to ON for the ID column in our table?
Thanks.
nath.
Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }
HTH, Jens Suessmeyer.
|||Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }
HTH, Jens Suessmeyer.
|||The BOL? :o(
Total newbie here Jens...hope you can bear with me! :o)
Where/What is the BOL?
Thanks
Nath.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1141478203.215944.156110@.i40g2000cwc.googlegr oups.com...
> Hi,
> thats a session thing, llok in the BOL for more information:
>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
>
> SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
> OFF }
>
> HTH, Jens Suessmeyer.
>
|||BOL is Books Online. The documentation that comes with SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
> The BOL? :o(
> Total newbie here Jens...hope you can bear with me! :o)
> Where/What is the BOL?
> Thanks
> Nath.
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1141478203.215944.156110@.i40g2000cwc.googlegr oups.com...
>
|||Hi,
Doh!
Nath.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OpLMjd5PGHA.5152@.TK2MSFTNGP10.phx.gbl...
> BOL is Books Online. The documentation that comes with SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
> news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
>
|||Are you certain you want to set IDENTITY_INSERT ON? There are special
situations where you may need to turn on this option but in most cases you
want to omit the column from your insert statement so that SQL Server will
assign the value automatically.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:ONykDn4PGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit
> value
> for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
> OFF.
> /checkout.asp, line 112
> Is this a database setting error? Can anyone instruct us on how to set
> the
> IDENTITY_INSERT to ON for the ID column in our table?
> Thanks.
> nath.
>

How to set IDENTITY_INSERT to ON?

Hi,
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert exp
licit value
for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
OFF.
/checkout.asp, line 112
Is this a database setting error? Can anyone instruct us on how to set the
IDENTITY_INSERT to ON for the ID column in our table?
Thanks.
nath.Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-
e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table 
3; ON |
OFF }
HTH, Jens Suessmeyer.|||Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-
e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table 
3; ON |
OFF }
HTH, Jens Suessmeyer.|||The BOL? :o(
Total newbie here Jens...hope you can bear with me! :o)
Where/What is the BOL?
Thanks
Nath.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1141478203.215944.156110@.i40g2000cwc.googlegroups.com...
> Hi,
> thats a session thing, llok in the BOL for more information:
>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b18
2-e0a5e5c373ee.htm
>
> SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table &#
123; ON |
> OFF }
>
> HTH, Jens Suessmeyer.
>|||BOL is Books Online. The documentation that comes with SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
> The BOL? :o(
> Total newbie here Jens...hope you can bear with me! :o)
> Where/What is the BOL?
> Thanks
> Nath.
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1141478203.215944.156110@.i40g2000cwc.googlegroups.com...
>|||Hi,
Doh!
Nath.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OpLMjd5PGHA.5152@.TK2MSFTNGP10.phx.gbl...
> BOL is Books Online. The documentation that comes with SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
> news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
>|||Are you certain you want to set IDENTITY_INSERT ON? There are special
situations where you may need to turn on this option but in most cases you
want to omit the column from your insert statement so that SQL Server will
assign the value automatically.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:ONykDn4PGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert e
xplicit
> value
> for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
> OFF.
> /checkout.asp, line 112
> Is this a database setting error? Can anyone instruct us on how to set
> the
> IDENTITY_INSERT to ON for the ID column in our table?
> Thanks.
> nath.
>

How to set IDENTITY_INSERT to ON?

Hi,
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value
for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
OFF.
/checkout.asp, line 112
Is this a database setting error? Can anyone instruct us on how to set the
IDENTITY_INSERT to ON for the ID column in our table?
Thanks.
nath.Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }
HTH, Jens Suessmeyer.|||Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }
HTH, Jens Suessmeyer.|||The BOL? :o(
Total newbie here Jens...hope you can bear with me! :o)
Where/What is the BOL?
Thanks
Nath.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1141478203.215944.156110@.i40g2000cwc.googlegroups.com...
> Hi,
> thats a session thing, llok in the BOL for more information:
>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
>
> SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
> OFF }
>
> HTH, Jens Suessmeyer.
>|||BOL is Books Online. The documentation that comes with SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
> The BOL? :o(
> Total newbie here Jens...hope you can bear with me! :o)
> Where/What is the BOL?
> Thanks
> Nath.
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1141478203.215944.156110@.i40g2000cwc.googlegroups.com...
>> Hi,
>> thats a session thing, llok in the BOL for more information:
>>
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
>>
>> SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
>> OFF }
>>
>> HTH, Jens Suessmeyer.
>|||Hi,
Doh!
Nath.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OpLMjd5PGHA.5152@.TK2MSFTNGP10.phx.gbl...
> BOL is Books Online. The documentation that comes with SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
> news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
>> The BOL? :o(
>> Total newbie here Jens...hope you can bear with me! :o)
>> Where/What is the BOL?
>> Thanks
>> Nath.
>> "Jens" <Jens@.sqlserver2005.de> wrote in message
>> news:1141478203.215944.156110@.i40g2000cwc.googlegroups.com...
>> Hi,
>> thats a session thing, llok in the BOL for more information:
>>
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
>>
>> SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
>> OFF }
>>
>> HTH, Jens Suessmeyer.
>>
>|||Are you certain you want to set IDENTITY_INSERT ON? There are special
situations where you may need to turn on this option but in most cases you
want to omit the column from your insert statement so that SQL Server will
assign the value automatically.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:ONykDn4PGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit
> value
> for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
> OFF.
> /checkout.asp, line 112
> Is this a database setting error? Can anyone instruct us on how to set
> the
> IDENTITY_INSERT to ON for the ID column in our table?
> Thanks.
> nath.
>