LINKED SERVER Problems

Hi @.ll
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this?
For any kind of information... many thanks
klaus
Hi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this?
>
> For any kind of information... many thanks
>
> klaus
>
>
>
|||Hi Mike
that is what we looking for!!
BUT HOW?
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>
|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas

"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW?
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>

LINKED SERVER Problems

Hi @.ll
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this'
For any kind of information... many thanks
klausHi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this'
>
> For any kind of information... many thanks
>
> klaus
>
>
>|||Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>> Hi @.ll
>> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
>> Oracle SERVER
>> During big problems with the Provider and a lot of testing we looking for
>> a way to change the LINKED Server to an ODBC Connection!
>> Is there any way to do this'
>>
>> For any kind of information... many thanks
>>
>> klaus
>>
>>
>>
>|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>> Hi @.ll
>> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
>> Oracle SERVER
>> During big problems with the Provider and a lot of testing we looking for
>> a way to change the LINKED Server to an ODBC Connection!
>> Is there any way to do this'
>>
>> For any kind of information... many thanks
>>
>> klaus
>>
>>
>>
>

LINKED SERVER Problems

Hi @.ll
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this'
For any kind of information... many thanks
klausHi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this'
>
> For any kind of information... many thanks
>
> klaus
>
>
>|||Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>

Linked Server oracle 0x80004005 error

Linked Server oracle 0x80004005 error
Has anyone gotten around the .....
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ]. ...
error when creating a linked server to oracle using the oracle drivers ?
Have no problem with the linked server ms oracle driver other than the fact
that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
plus.
Any ideas greatly appreciated ! Thanks, Steve.Hi
0x80004005 = Access Denied. Check that your credentials are correct that you
are supplying. I have also seen this when there are not enough connections
available on the Oracle side to connect.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Note that linked servers are dangerous if you are using the four part naming
method. It is very very very easy to end up pulling the whole table over and
the where clause being processed locally rather than on Oracle. If you are
going to use linked server then use the OpenQuery method instead of 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
> fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Bruce,
That's an interesting point you raise. I use linked servers with 4 part
names heavily. They are all MS SQL 2000 servers and I have no problems
at all with this. Are you suggesting this is a problem with all linked
servers or specifically Oracle?
Having said that I want the report server doing the processing rather
than loading the linked servers.
Chris
Bruce L-C [MVP] wrote:
> Note that linked servers are dangerous if you are using the four part
> naming method. It is very very very easy to end up pulling the whole
> table over and the where clause being processed locally rather than
> on Oracle. If you are going to use linked server then use the
> OpenQuery method instead of 4 part naming.|||I am using linked servers against Sybase but it makes no difference. It
could be that your tables are small enough that you haven't seen it. Another
SQL MVP did some testing for me. This gets better in Yukon. But he saw this
a whole lot. I suggest you take some of your queries and do get the query
plan for them and see what is happening. I would be surprised if some of
them aren't bringing all the data over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:uHCpg%23JIFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Bruce,
> That's an interesting point you raise. I use linked servers with 4 part
> names heavily. They are all MS SQL 2000 servers and I have no problems
> at all with this. Are you suggesting this is a problem with all linked
> servers or specifically Oracle?
> Having said that I want the report server doing the processing rather
> than loading the linked servers.
>
> Chris
> Bruce L-C [MVP] wrote:
>> Note that linked servers are dangerous if you are using the four part
>> naming method. It is very very very easy to end up pulling the whole
>> table over and the where clause being processed locally rather than
>> on Oracle. If you are going to use linked server then use the
>> OpenQuery method instead of 4 part naming.
>|||Grutzi Mike,
Can connect to the oracle server with the dsn from ms
access on the server with no problems. Can also see all the tables and views
for this linked server from the enterprise manager console.
Used these instructions to set it up.How to use Microsoft SQL Analyzer with
the Oracle Provider for OLE DB
---
Configuration: ============== Microsoft SQL Server 2000 SP 1 or 2 Microsoft
Windows 2000 Server SP 2 Oracle Provider for OLE DB 8.1.7.3.0 Oracle Client
8.1.7.3.0 Oracle Database 8.1.7.3.0 Instructions to Setup the Linked Server
Connection: =================================================== 1. Open SQL
Server Enterprise Manager 2. Click on the + to expand the tree for the
database server. You will need to expand it three times. Note: If the
SQL Server does not appear here, you will need to right click and go
through the Wizard for SQL Server Registration. - The first level of
the tree is called Microsoft Sql Server, The second level of the tree is
called SQL Server Group, and the third level of the tree is your SQL
Server Database. For these instructions we will call our SQL Server
Database, 'Scott'. 3. Under the Scott Database, click on the + to expand
the tree control for 'Security' 4. Under Security, right click on Linked
Servers and select "New Linked Server" 5. In the Linked Server Properties
do the following: a. Type in a Linked Server Name, for this example we
will call it 'Tiger' b. Under Server Type select Other data source
- For the provider name select Oracle Provider for OLE DB. c. In the
Data Source field, type in the Oracle Service name (SQL Net Alias)
d. Leave the Product Name and Provider String blank. e. Click on the
Provider Options button. - Check the check boxes for "Dynamic
Parameters" and "Allow InProcess" f. Click Apply & then click OK. - The
Provider Options dialog closes. 6. Go to the "Security" tab in the Linked
Server Properties. a. Select the radio button "Be made using this security
context" at the bottom of the box. b. Type in the User ID (your
Oracle login), ie. Scott in the "Remote login" field and your
Password, ie. Tiger in the "With Password" field. 7. Go to the "Server
Options" tab in the Linked Server Properties. a. While leaving the
default selections selected, also check the checkbox for "Collation
Compatible". b. Click OK This will create your Linked Server named Tiger.
Test the Linked Connection ================================== To test the
Linked Server, open up Microsoft Query Analyzer (It is found under the
Microsoft SQL Server menu option). 1. Select your SQL Server in the Connect
to SQL Server box. a. Since we have the SQL Server installed on the same
machine, we select (local). b. Select the appropriate Connect using
option. This is determined when you Installed/Created your SQL Server
database. For our example here we use Windows Authentication. And click
on the Ok button. 2. In the Query screen you can type a query. The
query will follow this format: SELECT * FROM <Linked Server Name>..<Oracle
user name in all CAPS>.<Oracle Table Name in all CAPS> IE. SELECT * FROM
TIGER..SCOTT.EMP 3. If you have the default scott/tiger schema installed in
your Oracle database you can use the following query: SELECT * FROM
TIGER..SCOTT.EMP 4. Type it into the Query box. 5. Click on the Green Arrow
to run the query. 6. You should see the contents of the Emp table in the
Grid.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that your credentials are correct that you
> are supplying. I have also seen this when there are not enough connections
> available on the Oracle side to connect.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Hi Bruce,
Thank you so much for the reply. Created the VMFO linked server
again using the Oracle driver and tried the openquery method and... still
got the ...
SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: ]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ].
"Bruce L-C [MVP]" wrote:
> Note that linked servers are dangerous if you are using the four part naming
> method. It is very very very easy to end up pulling the whole table over and
> the where clause being processed locally rather than on Oracle. If you are
> going to use linked server then use the OpenQuery method instead of 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ..
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> > fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Go to your linked server in Enterprise Manager. Right mouse click,
properties. Go to the Security tab. Do you have a username and password set
for it?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> Hi Bruce,
> Thank you so much for the reply. Created the VMFO linked
server
> again using the Oracle driver and tried the openquery method and... still
> got the ...
> SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: ]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ].
> "Bruce L-C [MVP]" wrote:
> > Note that linked servers are dangerous if you are using the four part
naming
> > method. It is very very very easy to end up pulling the whole table over
and
> > the where clause being processed locally rather than on Oracle. If you
are
> > going to use linked server then use the OpenQuery method instead of 4
part
> > naming.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > ..
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > Linked Server oracle 0x80004005 error
> > >
> > > Has anyone gotten around the .....
> > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > [OLE/DB provider returned message: Unspecified error]
> > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > returned 0x80004005: ]. ...
> > > error when creating a linked server to oracle using the oracle drivers
?
> > > Have no problem with the linked server ms oracle driver other than the
> > > fact
> > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
sql
> > > plus.
> > >
> > > Any ideas greatly appreciated ! Thanks, Steve.
> > >
> >
> >
> >|||Hi Bruce,
Thanks for your reply. Yes, there is a user/pw set. Also, all
the tables and views for the linked server appear perfectly. Do not think
this would be possible without that. Steve.
"Bruce L-C [MVP]" wrote:
> Go to your linked server in Enterprise Manager. Right mouse click,
> properties. Go to the Security tab. Do you have a username and password set
> for it?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> > Hi Bruce,
> > Thank you so much for the reply. Created the VMFO linked
> server
> > again using the Oracle driver and tried the openquery method and... still
> > got the ...
> >
> > SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> >
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: ]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ].
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Note that linked servers are dangerous if you are using the four part
> naming
> > > method. It is very very very easy to end up pulling the whole table over
> and
> > > the where clause being processed locally rather than on Oracle. If you
> are
> > > going to use linked server then use the OpenQuery method instead of 4
> part
> > > naming.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > > ..
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > > Linked Server oracle 0x80004005 error
> > > >
> > > > Has anyone gotten around the .....
> > > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > > [OLE/DB provider returned message: Unspecified error]
> > > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > > returned 0x80004005: ]. ...
> > > > error when creating a linked server to oracle using the oracle drivers
> ?
> > > > Have no problem with the linked server ms oracle driver other than the
> > > > fact
> > > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
> sql
> > > > plus.
> > > >
> > > > Any ideas greatly appreciated ! Thanks, Steve.
> > > >
> > >
> > >
> > >
>
>sql

Linked Server OLE DB provider does not contain the table: Provide

Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:

> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie

Linked Server OLE DB provider does not contain the table: Provide

Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie

Linked Server OLE DB provider does not contain the table: Provide

Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:

> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie

Linked Server object

I found the article abount linking exchange server with Sql 2005
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
I didn't see the 'Exchange OLE DB provider',
'exoledb.DataSource.1',
Do you have any idea how I can link the exchange server?
Can I search Exchange inbox in sql 2005?
Is Exchange on the same box as SQL Server? The Exchange
install would have installed the provider. It's a server
side only provider so they need to be on the same box.
-Sue
On Wed, 18 Oct 2006 15:43:02 -0700, BMW
<BMW@.discussions.microsoft.com> wrote:

>I found the article abount linking exchange server with Sql 2005
>EXEC sp_addlinkedserver 'exchange',
>'Exchange OLE DB provider',
>'exoledb.DataSource.1',
>'file:\\.\backofficestorage\localhost\public folders'
>I didn't see the 'Exchange OLE DB provider',
>'exoledb.DataSource.1',
>Do you have any idea how I can link the exchange server?
>Can I search Exchange inbox in sql 2005?
|||Hi Sue,
Is it means that the SQL Server and Exchange Server must on the same computer?
If they are on the same machine, can I serach Exchange inbox through the
stroe procedure in SQL?
Thanks.
"Sue Hoegemeier" wrote:

> Is Exchange on the same box as SQL Server? The Exchange
> install would have installed the provider. It's a server
> side only provider so they need to be on the same box.
> -Sue
> On Wed, 18 Oct 2006 15:43:02 -0700, BMW
> <BMW@.discussions.microsoft.com> wrote:
>
>
|||Yes. Just like the topic in books online states:
The Exchange Web Store should be located in the same
computer as SQL Server. Web Stores located in other
computers cannot be accessed by using the OLE DB Provider
for Exchange.
Then you can follow the example in books online.
-Sue
On Thu, 19 Oct 2006 09:52:01 -0700, BMW
<BMW@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue,
>Is it means that the SQL Server and Exchange Server must on the same computer?
>If they are on the same machine, can I serach Exchange inbox through the
>stroe procedure in SQL?
>Thanks.
>"Sue Hoegemeier" wrote:
sql

Linked Server Headache (Access)

There seems to be a lot of confusion around the groups about linking
to an Access mdb with the SQL Server Jet OLE DB provider and I havent
been able to find a straight forward solution. Basically, I have an
Access MDB (A2K) on one server and a SQL Server DB (2005 std ed.) on
another - Both on the same network. I'm trying to create a linked
server object in the SQL server to view data in the mdb. I've set it
up and it works - but only from the server machine itself. If you try
to connect the the linked server from any other computer on the
network you get the usual access denied / file in use error.

The fact that I can use the linked server from the server box itself
but not from another pc on the network makes me think that it may be a
permissions problem. But I am logging in with full Admin rights and
still no joy. Also there is no workgroup security on the mdb, so thats
not the problem. I've used the surface editor to remove any
restrictions that may cause problems, OLE DB connect, OPENROWSET etc.
but still no joy.

I've tried mapping the mdb's location on the server so I could use a
standard filepath, rather than a //Server-Name/... path. Again, works
from the server, but not from any client PCs, so no joy there either.
In frustration, I copied the mdb to the same server and viola - full
access to the linked server from anywhere. But this is no good, I need
the mdb file to stay where it is. An mdb full of linked tables wont
work... they don't show up in the linked server.

So that's it - out of ideas! Am I just going to have to accept that
linked server objects are limited just to mdb files on the same server
machine, or is there something I'm missing? Firewalls, service
logins, server settings... something one of you gurus out there know
about that might be the key to making my headache go away!!!

Any input gratefully recieved!!!OK, bloody typical - I waste the best part of a day and half on theis
problem, and when I post to look for help I find the solution myself 2
mins later anyway:

In case anyone else is being crippled with this, here's the answer:

http://support.microsoft.com/?id=241267
Maybe it will help someone some day ;)

Linked server for Oracle on SQL server - query performance

Hi folks,
I have created a linked server on SQL server 2000 for an Oracle 8i
database using Microsoft's OLE provider for ORACLE. The connection
works fine and I am able to run queries (select queries) agaist the
Oracle table from SQL query analyzer.
But the problem is, the query is miserably slower than if I run the
query directly on the Oracle table using either SQL*plus or TOAD. In
the former case (linked table) the query run time is upwards of 7
seconds whereas, in the latter case (directly using SQL*plus) the
query runs in 200 milli seconds. The table is indexed and I am hitting
the index columns.
Any ideas or suggestions? As always thanks in advance and your help
will be much appreciated.
Appu.
Hi everybody,
I am experiencing the same problems as explained below. Help is appriciated.
ETT
"Sat" wrote:

> Hi folks,
> I have created a linked server on SQL server 2000 for an Oracle 8i
> database using Microsoft's OLE provider for ORACLE. The connection
> works fine and I am able to run queries (select queries) agaist the
> Oracle table from SQL query analyzer.
> But the problem is, the query is miserably slower than if I run the
> query directly on the Oracle table using either SQL*plus or TOAD. In
> the former case (linked table) the query run time is upwards of 7
> seconds whereas, in the latter case (directly using SQL*plus) the
> query runs in 200 milli seconds. The table is indexed and I am hitting
> the index columns.
> Any ideas or suggestions? As always thanks in advance and your help
> will be much appreciated.
> Appu.
>
|||Depending on the capabilities of the data provider used for the linked
server and various run-time settings or details of your query, such as
collations, it may not be possible for the SQL Server query processor to
send the relevant part of the WHERE condition to the Oracle server.
Instead, the SQL Server may be requesting an entire table from the
Oracle server and filtering the rows afterwards without the benefit of
the Oracle index. There is no one way to resolve this. The semantics
of the query may mean it is simply wrong to filter the query on the
Oracle side, but if there is a solution, look at data provider settings,
ANSI compliance settings, collations, and exact matching of data types.
You might also post your question in an Oracle group, since Oracle users
might be able to help.
Steve Kass
Drew University
ETT wrote:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated.
>ETT
>"Sat" wrote:
>

Linked server for Oracle on SQL server - query performance

Hi folks,
I have created a linked server on SQL server 2000 for an Oracle 8i
database using Microsoft's OLE provider for ORACLE. The connection
works fine and I am able to run queries (select queries) agaist the
Oracle table from SQL query analyzer.
But the problem is, the query is miserably slower than if I run the
query directly on the Oracle table using either SQL*plus or TOAD. In
the former case (linked table) the query run time is upwards of 7
seconds whereas, in the latter case (directly using SQL*plus) the
query runs in 200 milli seconds. The table is indexed and I am hitting
the index columns.
Any ideas or suggestions? As always thanks in advance and your help
will be much appreciated.
Appu.Hi everybody,
I am experiencing the same problems as explained below. Help is appriciated.
ETT
"Sat" wrote:

> Hi folks,
> I have created a linked server on SQL server 2000 for an Oracle 8i
> database using Microsoft's OLE provider for ORACLE. The connection
> works fine and I am able to run queries (select queries) agaist the
> Oracle table from SQL query analyzer.
> But the problem is, the query is miserably slower than if I run the
> query directly on the Oracle table using either SQL*plus or TOAD. In
> the former case (linked table) the query run time is upwards of 7
> seconds whereas, in the latter case (directly using SQL*plus) the
> query runs in 200 milli seconds. The table is indexed and I am hitting
> the index columns.
> Any ideas or suggestions? As always thanks in advance and your help
> will be much appreciated.
> Appu.
>|||Depending on the capabilities of the data provider used for the linked
server and various run-time settings or details of your query, such as
collations, it may not be possible for the SQL Server query processor to
send the relevant part of the WHERE condition to the Oracle server.
Instead, the SQL Server may be requesting an entire table from the
Oracle server and filtering the rows afterwards without the benefit of
the Oracle index. There is no one way to resolve this. The semantics
of the query may mean it is simply wrong to filter the query on the
Oracle side, but if there is a solution, look at data provider settings,
ANSI compliance settings, collations, and exact matching of data types.
You might also post your question in an Oracle group, since Oracle users
might be able to help.
Steve Kass
Drew University
ETT wrote:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated
.
>ETT
>"Sat" wrote:
>
>

Linked Server Fails w/Error: 7399

I'm trying to connnect from a SQL Server 7.0 db to an
Oracle 8.i instance. Connection is failing with the
following error message: Error 7399 OLE DB
Provider 'MSDAOR' reported an error.
I used the following ddl to create my linked server &
linked server logon:
exec
sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
rd'
exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
Server logon', 'Oracle Server Logon', 'Oracle Server logon
password'
I can connect to the Oracle database from the box where
I'm running SQL Server.
When I issue: ping oracle server name from box running SQL
Server I can ping server.
When I issue tnsping oracle database name from box running
SQL server I can ping Oracle database.
When I issue sqlplus ORA logon/ORA password@.ORA dbname I
can logon to database so I know I can connect to the box
and I'm using the right logon & password.
Any help greatly appreciated. Thanks in advance.Check if these articles help:
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;220915
http://support.microsoft.com/defaul...kb;en-us;277002
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:6f5901c4061d$a857ef90$a601280a@.phx.gbl...
> I'm trying to connnect from a SQL Server 7.0 db to an
> Oracle 8.i instance. Connection is failing with the
> following error message: Error 7399 OLE DB
> Provider 'MSDAOR' reported an error.
> I used the following ddl to create my linked server &
> linked server logon:
> exec
> sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
> rd'
> exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
> Server logon', 'Oracle Server Logon', 'Oracle Server logon
> password'
> I can connect to the Oracle database from the box where
> I'm running SQL Server.
> When I issue: ping oracle server name from box running SQL
> Server I can ping server.
> When I issue tnsping oracle database name from box running
> SQL server I can ping Oracle database.
> When I issue sqlplus ORA logon/ORA password@.ORA dbname I
> can logon to database so I know I can connect to the box
> and I'm using the right logon & password.
> Any help greatly appreciated. Thanks in advance.|||I reviewed the Knowledge Base articles you suggested. This was a pre-existi
ng linked server definition that was working and has just failed. I've atte
mpted to redefine it. I'm not getting the message about the Oracle client a
nd networking comonents mis
sing so I'm ruling out 277002 & 220915. Article 280106 suggests that the ve
rsion of MDAC might be wrong (you need MDAC 2.5, or later, installed on SQL
Server computer). How do I check my version of MDAC to see if I'm at 2.5 or
later? TIA|||> How do I check my version of MDAC to see if I'm at 2.5 or later?
You can determine the installed MDAC version with the component checker
tool:
<http://www.microsoft.com/downloads/...a8df6-4a21-4b43
-bf53-14332ef092c9&displaylang=en>
Hope this helps.
Dan Guzman
SQL Server MVP
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:89454DC5-D415-4284-95FD-760FA7B0C8EC@.microsoft.com...
> I reviewed the Knowledge Base articles you suggested. This was a
pre-existing linked server definition that was working and has just failed.
I've attempted to redefine it. I'm not getting the message about the Oracle
client and networking comonents missing so I'm ruling out 277002 & 220915.
Article 280106 suggests that the version of MDAC might be wrong (you need
MDAC 2.5, or later, installed on SQL Server computer). How do I check my
version of MDAC to see if I'm at 2.5 or later? TIA|||I downloaded the component checker and verified I have MDAC 2.5 sp3 installe
d on my client (SQL Server) computer. I took another look at one of the Kno
wledge Base Articles, #280106, that Dejan suggested. We checked the registr
y entries for Oracle client
software. We found that the registry entries were pointing to Oracle Client
versions for Oracle Client 7.3 event though the dlls were not present on th
e machine, Oracle Client 8.i is loaded onto the machine. That's the version
we expected to see in the
registry. We suspected that the registry entries were somehow corrupted or
overlaid. When we checked the event log for the server it was full of error
s, the server had lost it's connection to the domain server. We scheduled a
n emergecy reboot of the s
erver (server hadn't been stopped & restarted in over 100 days) and when the
server came back up we were able to query our linked server.

Linked Server Fails after MDAC 2.8 upgrade

We installed the MDAC 2.8 upgrade and our existing
Microsoft OLE DB Provider for Oracle stopped working.
Other providers were fine, i.e. SQL Server (linking to
another mssql server).
Has anyone had this issue? Or does anyone have any
ideas as to what changed in 2.8 that would affect this
linked server?
Thanks in advance,
MarkI think that may be something related with distributed transactions.
I have some applications that stopped to work when I upgrade to MDAC 2.8.
Trying to solve this I discover that just returning the sql server odbc
driver files to version 2.7 the applications could work correctly... anyway
I think that is a bug in MDAC 2.8
Agrinei
"Mark D" <anonymous@.discussions.microsoft.com> wrote in message
news:816301c4076c$80de9630$a601280a@.phx.gbl...
> We installed the MDAC 2.8 upgrade and our existing
> Microsoft OLE DB Provider for Oracle stopped working.
> Other providers were fine, i.e. SQL Server (linking to
> another mssql server).
> Has anyone had this issue? Or does anyone have any
> ideas as to what changed in 2.8 that would affect this
> linked server?
> Thanks in advance,
> Mark

linked server error: OLE DB provider 'SQLOLEDB' reported an error.

I set up a linked server between two SQL Server databases. I was able to
query the linked database just fine.
Then I deleted the linked server using Enterprise Manager. Then I re-created
the linked server using Enterprise Manager but the original query (select *
from [linked server name].[database name].dbo.[table name]) does not work
anymore even though it used to work before I deleted and recreated the linked
server.
It returns the following error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].
Hi,
to solve the problem go the registry
(HKLM\Software\Microsoft\Mssqlserver\providers\SQL OLEDB ) and make sure all
except "AllowInProcess" have a value of 0.
Bye
Gabriele
MCP
"RHI Claims Explorer" wrote:

> I set up a linked server between two SQL Server databases. I was able to
> query the linked database just fine.
> Then I deleted the linked server using Enterprise Manager. Then I re-created
> the linked server using Enterprise Manager but the original query (select *
> from [linked server name].[database name].dbo.[table name]) does not work
> anymore even though it used to work before I deleted and recreated the linked
> server.
> It returns the following error message:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
>

linked server error: OLE DB provider 'SQLOLEDB' reported an error.

I set up a linked server between two SQL Server databases. I was able to
query the linked database just fine.
Then I deleted the linked server using Enterprise Manager. Then I re-created
the linked server using Enterprise Manager but the original query (select *
from [linked server name].[database name].dbo.[table name]) does
not work
anymore even though it used to work before I deleted and recreated the linke
d
server.
It returns the following error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].Hi,
to solve the problem go the registry
(HKLM\Software\Microsoft\Mssqlserver\pro
viders\SQLOLEDB ) and make sure all
except "AllowInProcess" have a value of 0.
Bye
Gabriele
MCP
"RHI Claims Explorer" wrote:

> I set up a linked server between two SQL Server databases. I was able to
> query the linked database just fine.
> Then I deleted the linked server using Enterprise Manager. Then I re-creat
ed
> the linked server using Enterprise Manager but the original query (select
*
> from [linked server name].[database name].dbo.[table name]) do
es not work
> anymore even though it used to work before I deleted and recreated the lin
ked
> server.
> It returns the following error message:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. The provider did not give an
y
> information about the error.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterfac
e
> returned 0x80004005: The provider did not give any information about the
> error.].
>

Linked Server error 7399

I have setup a linked server to a Sybase database using the ODBC driver (which in turn uses the Sybase OLE DB provider). Logged onto the box as the SQL Service account everything is good. Using Enterprise Manager from any machine to the SQL Server box using an account that is a local administrator on the SQL Server box everything is good. However, if I attempt to make a connection via Enterprise Manager with an account that does not have local administrator rights on the server I get an error. I get the same error if I attempt an OPENQUERY against a table in the Linked server database. The error I receive is as follows:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [SYBASE][ODBC Sybase driver]Allocation of a Sybase Open Client Context failed. Sybase normally generates a SYBINIT.ERR file contianing more specific reasons for failing.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

The drives are secured so that only local administrators have rights to them.

I found in the Microsoft KB the follownig article:
http://support.microsoft.com/?kbid=814398

In this article it is suggesting that I need to open up the server from this lockdown. So, just to test this out without having to restart SQL Server I first allowed for full access to the C drive to "Everyone" and everything was good.

My question then is, has anybody else ran into this sort of thing? It doesn't seem right that I have to open up some folder on the database server to everyone in order to get a linked server going.

ThanksI figured it out. Here is what I found out:
Using the FileMon.exe tool from sysinternals.com I noticed that the file referred to in the error called "SYBINIT.ERR" could not be written since regular users did not have write access to the directory that it was trying to be written to. This was C:\WINNT\System32. So, I temporarily gave "Everyone" rights to modify the files within this directory. After doing so I ended up getting a "SYBINIT.ERR" file created which in turn told me that it "Cannot access file c:\sybase\ini\objectid.dat". I then gave Everyone read rights to the C:\sybase\ini folder. I then attempted again and got the following error: "Cannot access file c:\sybase\locales\locales.dat". Again, I gave read rights to this folder and tried again... this time getting the following errors: "Cannot access c:\sybase\charsets\iso_1\binary.srt file
Cannot access c:\sybase\charsets\iso_1\charset.loc file
Cannot access c:\sybase\charsets\iso_1\iso_1.cfg file"

Not wanting to keep this up, I then gave read rights to the C:\sybase folder and tried again. Everything worked. Then, I removed modify rights from "C:\WINNT\System32" and tried again. EVERYTHING WORKED!!! Yeah, this means that I just had to give read rights without opening up the SQL Server too much.|||Appreciate your interest in posting solution too, keep it up.

linked server error & tuning for OLE ODBC database

Hi,
I have SQL 2005 connecting a ODBC database via linked servers. The issues
are 2:
1. select * from linksever.db.owner.invoicedetail takes longer for the
results to come. The table has 4,700,050 records. Is there any suggestion
for performance tuning.
2. I get an error when creating the view for the above table. The error is:
Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
supplied inconsistent metadata. An extra column was supplied during
execution that was not found at compile time.
http://support.microsoft.com/Default.aspx?id=266337
The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
Any suggestion is appreciated.
Regards,
ChinsHi Chins
"Chinnasamy Arumugam" wrote:

> Hi,
> I have SQL 2005 connecting a ODBC database via linked servers. The issues
> are 2:
> 1. select * from linksever.db.owner.invoicedetail takes longer for the
> results to come. The table has 4,700,050 records. Is there any suggestion
> for performance tuning.
Have you tried using OPENQUERY or executing a procedure on the linked server?">
> 2. I get an error when creating the view for the above table. The error is
:
> Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
> supplied inconsistent metadata. An extra column was supplied during
> execution that was not found at compile time.
> http://support.microsoft.com/Default.aspx?id=266337
> The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
>
Do you explicitly name the columns being used? How about posting DDL (table
and view)?

> Any suggestion is appreciated.
> --
> Regards,
> Chins
>
John

linked server error & tuning for OLE ODBC database

Hi,
I have SQL 2005 connecting a ODBC database via linked servers. The issues
are 2:
1. select * from linksever.db.owner.invoicedetail takes longer for the
results to come. The table has 4,700,050 records. Is there any suggestion
for performance tuning.
2. I get an error when creating the view for the above table. The error is:
Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
supplied inconsistent metadata. An extra column was supplied during
execution that was not found at compile time.
http://support.microsoft.com/Default.aspx?id=266337
The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
Any suggestion is appreciated.
Regards,
Chins
Hi Chins
"Chinnasamy Arumugam" wrote:

> Hi,
> I have SQL 2005 connecting a ODBC database via linked servers. The issues
> are 2:
> 1. select * from linksever.db.owner.invoicedetail takes longer for the
> results to come. The table has 4,700,050 records. Is there any suggestion
> for performance tuning.
Have you tried using OPENQUERY or executing a procedure on the linked server?
> 2. I get an error when creating the view for the above table. The error is:
> Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
> supplied inconsistent metadata. An extra column was supplied during
> execution that was not found at compile time.
> http://support.microsoft.com/Default.aspx?id=266337
> The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
>
Do you explicitly name the columns being used? How about posting DDL (table
and view)?

> Any suggestion is appreciated.
> --
> Regards,
> Chins
>
John

linked server error & tuning for OLE ODBC database

Hi,
I have SQL 2005 connecting a ODBC database via linked servers. The issues
are 2:
1. select * from linksever.db.owner.invoicedetail takes longer for the
results to come. The table has 4,700,050 records. Is there any suggestion
for performance tuning.
2. I get an error when creating the view for the above table. The error is:
Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
supplied inconsistent metadata. An extra column was supplied during
execution that was not found at compile time.
http://support.microsoft.com/Default.aspx?id=266337
The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
Any suggestion is appreciated.
--
Regards,
ChinsHi Chins
"Chinnasamy Arumugam" wrote:
> Hi,
> I have SQL 2005 connecting a ODBC database via linked servers. The issues
> are 2:
> 1. select * from linksever.db.owner.invoicedetail takes longer for the
> results to come. The table has 4,700,050 records. Is there any suggestion
> for performance tuning.
Have you tried using OPENQUERY or executing a procedure on the linked server?
> 2. I get an error when creating the view for the above table. The error is:
> Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
> supplied inconsistent metadata. An extra column was supplied during
> execution that was not found at compile time.
> http://support.microsoft.com/Default.aspx?id=266337
> The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
>
Do you explicitly name the columns being used? How about posting DDL (table
and view)?
> Any suggestion is appreciated.
> --
> Regards,
> Chins
>
John

Linked Server Error

SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?
> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server
.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I c
an
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's usef
ul
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)