order by slowing me down X 40

sql2k sp3
This query takes 40 seconds to run in Query Analyzer. If I
run it without the Order By it takes 0 seconds. This in
itself isnt to bizarre. But heres the catch, the Clustered
Index is on the transdate coulmn. That being the case,
shouldnt the difference be less?
select top 100 CustomerKey,transdate
from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey,transdate
order by transdate
TIA, ChrisRDid you view the query plan with and without the order by? What did you
see?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:21a7001c45afd$08f70a50$a501280a@.phx.gbl...
> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>|||Theres table scans either way. But the order by query has
sorts as well.
>--Original Message--
>Did you view the query plan with and without the order
by? What did you
>see?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:21a7001c45afd$08f70a50$a501280a@.phx.gbl...
>> sql2k sp3
>> This query takes 40 seconds to run in Query Analyzer.
If I
>> run it without the Order By it takes 0 seconds. This in
>> itself isnt to bizarre. But heres the catch, the
Clustered
>> Index is on the transdate coulmn. That being the case,
>> shouldnt the difference be less?
>>
>> select top 100 CustomerKey,transdate
>> from transdtl
>> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>> SERVICER39')
>> and TranCode not in (7008,7023)
>> Group By CustomerKey,transdate
>> order by transdate
>>
>> TIA, ChrisR
>
>.
>|||try
Group By transdate, CustomerKey
I'm guessing that without the order by it just has to get the first 100 groups. With the order by it has to get all the groups to sort.
Have a look at the number of rows in steps in the query plan.
"ChrisR" wrote:
> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>|||Even just using Order By TramsDate, it still slows it down just the same.
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:1BB2D0D3-8C0D-4024-98C7-317014533102@.microsoft.com...
> try
> Group By transdate, CustomerKey
>
> I'm guessing that without the order by it just has to get the first 100
groups. With the order by it has to get all the groups to sort.
> Have a look at the number of rows in steps in the query plan.
>
> "ChrisR" wrote:
> > sql2k sp3
> >
> > This query takes 40 seconds to run in Query Analyzer. If I
> > run it without the Order By it takes 0 seconds. This in
> > itself isnt to bizarre. But heres the catch, the Clustered
> > Index is on the transdate coulmn. That being the case,
> > shouldnt the difference be less?
> >
> >
> > select top 100 CustomerKey,transdate
> > from transdtl
> > where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> > SERVICER39')
> > and TranCode not in (7008,7023)
> > Group By CustomerKey,transdate
> > order by transdate
> >
> >
> >
> > TIA, ChrisR
> >
> >|||Chris,
The query is asking for different results depending on whether you
include the ORDER BY. Without the ORDER BY, you are asking for any 100
rows from among the (Customer, transdate) pairs appearing in rows that
satisfy your MerchName and TranCode criteria. With the ORDER BY, you
are asking for a specific 100 (Customer, transdate) pairs satisfying
your MerchName and TranCode criteria - the 100 pairs that have the
earliest transdate values.
There are various scenarios where the query will be slower with an
ORDER BY clause, although I'm not sure why you are seeing a table scan
(i.e., a clustered index scan) in both situations. In addition, "Table
Scan" should not appear as an operator when the table has a clustered
index, so do you think you could post the CREATE TABLE and CREATE INDEX
statements for these tables and the estimated execution plans obtained
with the SET SHOWPLAN_TEXT ON?
Steve Kass
Drew University
ChrisR wrote:
>sql2k sp3
>This query takes 40 seconds to run in Query Analyzer. If I
>run it without the Order By it takes 0 seconds. This in
>itself isnt to bizarre. But heres the catch, the Clustered
>Index is on the transdate coulmn. That being the case,
>shouldnt the difference be less?
>
>select top 100 CustomerKey,transdate
>from transdtl
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode not in (7008,7023)
>Group By CustomerKey,transdate
>order by transdate
>
>TIA, ChrisR
>
>|||Yes but Ill have to repost Monday. Have a great weekend.
"Steve Kass" <skass@.drew.edu> wrote in message
news:eWYRFh5WEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Chris,
> The query is asking for different results depending on whether you
> include the ORDER BY. Without the ORDER BY, you are asking for any 100
> rows from among the (Customer, transdate) pairs appearing in rows that
> satisfy your MerchName and TranCode criteria. With the ORDER BY, you
> are asking for a specific 100 (Customer, transdate) pairs satisfying
> your MerchName and TranCode criteria - the 100 pairs that have the
> earliest transdate values.
> There are various scenarios where the query will be slower with an
> ORDER BY clause, although I'm not sure why you are seeing a table scan
> (i.e., a clustered index scan) in both situations. In addition, "Table
> Scan" should not appear as an operator when the table has a clustered
> index, so do you think you could post the CREATE TABLE and CREATE INDEX
> statements for these tables and the estimated execution plans obtained
> with the SET SHOWPLAN_TEXT ON?
> Steve Kass
> Drew University
> ChrisR wrote:
> >sql2k sp3
> >
> >This query takes 40 seconds to run in Query Analyzer. If I
> >run it without the Order By it takes 0 seconds. This in
> >itself isnt to bizarre. But heres the catch, the Clustered
> >Index is on the transdate coulmn. That being the case,
> >shouldnt the difference be less?
> >
> >
> >select top 100 CustomerKey,transdate
> >from transdtl
> >where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> >SERVICER39')
> >and TranCode not in (7008,7023)
> >Group By CustomerKey,transdate
> >order by transdate
> >
> >
> >
> >TIA, ChrisR
> >
> >
> >
>

order by slowing me down

sql2k sp3
This query takes 14 seconds to run in Query Analyzer. If I
run it without the Order By it takes 0 seconds. This in
itself isnt to bizarre. But heres the catch, the Clustered
Index is on the transdate coulmn. That being the case,
shouldnt the difference be less? Some fun facts:
I didnt design this table. I know it has too many NULLs.
Something I noticed in the output of showplan_text is that
its sorting by customerkey.
Below is the DDL, query, and output from set
showplan_text on:
CREATE TABLE [dbo].[TransDtl0] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_TransDtl0_TransDate] ON
[dbo].[TransDtl0]([TransDate]) WITH FILLFACTOR = 100 ON
[PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_PostDate] ON [dbo].[TransDtl0]
([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_CustomerKey] ON [dbo].
[TransDtl0]([CustomerKey]) WITH FILLFACTOR = 100 ON
[PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_RefNo] ON [dbo].[TransDtl0]
([RefNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_SerialNbr] ON [dbo].
[TransDtl0]([SerialNbr]) WITH FILLFACTOR = 100 ON
[PRIMARY]
GO
/****** The index created by the following statement is
for internal use only. ******/
/****** It is not a real index but exists as statistics
only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_MerchSIC] ON [dbo].
[TransDtl0] ([MerchSIC]) ')
GO
CREATE INDEX [IX_TransDtl0] ON [dbo].[TransDtl0]
([MerchName]) ON [PRIMARY]
GO
select
CustomerKey
from transdtl0 t
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode <> 7008 and trancode <> 7023
Group By t.CustomerKey
order by min(t.transdate)
StmtText
---
---
---
---
--
select
CustomerKey
from transdtl0 t
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode <> 7008 and trancode <> 7023
Group By t.CustomerKey
order by
min(t.transdate)
--t.customerkey,
--truncate table tmpdtl
(1 row(s) affected)
StmtText
---
---
---
---
----
|--Parallelism(Gather Streams, ORDER BY:([Expr1001] ASC))
|--Sort(ORDER BY:([Expr1001] ASC))
|--Stream Aggregate(GROUP BY:([t].
[CustomerKey]) DEFINE:([Expr1001]=MIN([t].[TransDate])))
|--Sort(ORDER BY:([t].[CustomerKey] ASC))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([t].[CustomerKey]))
|--Clustered Index Scan(OBJECT:
([MatViewTest].[dbo].[TransDtl0].[IX_TransDtl0_TransDate]
AS [t]), WHERE:((([t].[MerchName]='DTV*DIRECTV SERVICER39'
OR [t].[MerchName]='DTV*DIRECTV SERVICE') AND Convert([t].
[TranCode])<>7008) AND Convert([t].[TranCode])<>7023))
(6 row(s) affected)
TIA, ChrisRI just found some stuff out. As a result, this question
doesnt matter. Im going to repost a similar question
shortly. Hopefully nobody has spent time on this. Sorry.
>--Original Message--
>sql2k sp3
>This query takes 14 seconds to run in Query Analyzer. If
I
>run it without the Order By it takes 0 seconds. This in
>itself isnt to bizarre. But heres the catch, the
Clustered
>Index is on the transdate coulmn. That being the case,
>shouldnt the difference be less? Some fun facts:
>I didnt design this table. I know it has too many NULLs.
>Something I noticed in the output of showplan_text is
that
>its sorting by customerkey.
>Below is the DDL, query, and output from set
>showplan_text on:
>CREATE TABLE [dbo].[TransDtl0] (
> [TransDtlKey] [int] NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranCode] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> [TransAmt] [money] NULL ,
> [RefNbr] [char] (23) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [MerchName] [varchar] (25) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NULL ,
> [PostDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDateMonth] [tinyint] NULL ,
> [PostDateYear] [smallint] NULL ,
> [CreateDate] [datetime] NULL ,
> [MerchSIC] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY]
>GO
> CREATE CLUSTERED INDEX [IX_TransDtl0_TransDate] ON
>[dbo].[TransDtl0]([TransDate]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_PostDate] ON [dbo].
[TransDtl0]
>([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_CustomerKey] ON [dbo].
>[TransDtl0]([CustomerKey]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_RefNo] ON [dbo].[TransDtl0]
>([RefNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_SerialNbr] ON [dbo].
>[TransDtl0]([SerialNbr]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
>/****** The index created by the following statement is
>for internal use only. ******/
>/****** It is not a real index but exists as statistics
>only. ******/
>if (@.@.microsoftversion > 0x07000000 )
>EXEC ('CREATE STATISTICS [Statistic_MerchSIC] ON [dbo].
>[TransDtl0] ([MerchSIC]) ')
>GO
> CREATE INDEX [IX_TransDtl0] ON [dbo].[TransDtl0]
>([MerchName]) ON [PRIMARY]
>GO
>
>select
>CustomerKey
>from transdtl0 t
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode <> 7008 and trancode <> 7023
>Group By t.CustomerKey
>order by min(t.transdate)
>
>StmtText
>
>
>
>----
-
>----
-
>----
-
>----
-
>--
>select
>CustomerKey
>from transdtl0 t
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode <> 7008 and trancode <> 7023
>Group By t.CustomerKey
>order by
>min(t.transdate)
>--t.customerkey,
>--truncate table tmpdtl
>(1 row(s) affected)
>StmtText
>
>
>
>----
-
>----
-
>----
-
>----
-
>----
> |--Parallelism(Gather Streams, ORDER BY:([Expr1001]
ASC))
> |--Sort(ORDER BY:([Expr1001] ASC))
> |--Stream Aggregate(GROUP BY:([t].
>[CustomerKey]) DEFINE:([Expr1001]=MIN([t].[TransDate])))
> |--Sort(ORDER BY:([t].[CustomerKey] ASC))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS:([t].[CustomerKey]))
> |--Clustered Index Scan(OBJECT:
>([MatViewTest].[dbo].[TransDtl0].[IX_TransDtl0_TransDate]
>AS [t]), WHERE:((([t].[MerchName]='DTV*DIRECTV
SERVICER39'
>OR [t].[MerchName]='DTV*DIRECTV SERVICE') AND Convert([t].
>[TranCode])<>7008) AND Convert([t].[TranCode])<>7023))
>(6 row(s) affected)
>
>TIA, ChrisR
>.
>

Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I have
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
SQL Server Enterprise Manager
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
SQL Server Enterprise Manager
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance.
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David
|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:

> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>
|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David

Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I have
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
--
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance."Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:
> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> > We are attempting to create a linked server to an Oracle database and it
> > isn't working.
> >
> > We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> > installed the Oracle 9.2.0.1 client and am able to successfully connect to
> > the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> > creating an ODBC DSN using the Oracle driver provided by Oracle, and
> > hitting
> > the "test connection" button. Both ways work, so I am fairly certain I
> > have
> > the tnsnames.ora file configured correctly.
> >
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David

Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I hav
e
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection
"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
--
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance."Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:

> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David

Oracle linked server error

Hello,
sql server 2000 cluster patched to SP3 and NT5 patched to SP4.
Oracle 9i client installed.
We have set up a linked server to an Oracle instance using the 'Microsoft
OLE DB Provider for Oracle', this works for a while without any problems and
then for an unknown reason ceases to work.
The only error messages I have been able to get is by running the t-sql query
SELECT * FROM LINKSERV..SCHEMA.TABLE which returns
Server:Msg 7399, Level 16, State 1, Line 1
OLE DB Provider MSDAORA reported an error.
[OLE/DB provider returned message: Oracle error occured but error message
could not be retrieved from Oracle.}
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:].
However using the Oracle client on the server I can still connect to any
Oracle instance but I have to stop and start the sql service to get the
linked server to work again, becuase I have to stop and start sql server I'm
presuming that the fault is with sql server.
Did you check out that article before ?
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server
http://support.microsoft.com/dXefaul...en-us;Q280X106
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Chris@.CambridgeUK" <Chris@.CambridgeUK@.discussions.microsoft.com> schrieb im
Newsbeitrag news:0BE21B83-BA02-4C0D-AA57-573AD76C3FB5@.microsoft.com...
> Hello,
> sql server 2000 cluster patched to SP3 and NT5 patched to SP4.
> Oracle 9i client installed.
> We have set up a linked server to an Oracle instance using the 'Microsoft
> OLE DB Provider for Oracle', this works for a while without any problems
> and
> then for an unknown reason ceases to work.
> The only error messages I have been able to get is by running the t-sql
> query
> SELECT * FROM LINKSERV..SCHEMA.TABLE which returns
> Server:Msg 7399, Level 16, State 1, Line 1
> OLE DB Provider MSDAORA reported an error.
> [OLE/DB provider returned message: Oracle error occured but error message
> could not be retrieved from Oracle.}
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:].
> However using the Oracle client on the server I can still connect to any
> Oracle instance but I have to stop and start the sql service to get the
> linked server to work again, becuase I have to stop and start sql server
> I'm
> presuming that the fault is with sql server.
>
>
|||Yes, I've seen this document and it has been of help to me before but not in
this case as the linked server works and then stops without notice
"Jens Sü?meyer" wrote:

> Did you check out that article before ?
> HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server
> http://support.microsoft.com/dXefau...n-us;Q280X106
>
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Chris@.CambridgeUK" <Chris@.CambridgeUK@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:0BE21B83-BA02-4C0D-AA57-573AD76C3FB5@.microsoft.com...
>
>

Oracle linked server error

Hello,
sql server 2000 cluster patched to SP3 and NT5 patched to SP4.
Oracle 9i client installed.
We have set up a linked server to an Oracle instance using the 'Microsoft
OLE DB Provider for Oracle', this works for a while without any problems and
then for an unknown reason ceases to work.
The only error messages I have been able to get is by running the t-sql quer
y
SELECT * FROM LINKSERV..SCHEMA.TABLE which returns
Server:Msg 7399, Level 16, State 1, Line 1
OLE DB Provider MSDAORA reported an error.
[OLE/DB provider returned message: Oracle error occured but error messag
e
could not be retrieved from Oracle.}
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:].
However using the Oracle client on the server I can still connect to any
Oracle instance but I have to stop and start the sql service to get the
linked server to work again, becuase I have to stop and start sql server I'm
presuming that the fault is with sql server.Did you check out that article before ?
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server
http://support.microsoft.com/d_efau...;en-us;Q280_106
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Chris@.CambridgeUK" <Chris@.CambridgeUK@.discussions.microsoft.com> schrieb im
Newsbeitrag news:0BE21B83-BA02-4C0D-AA57-573AD76C3FB5@.microsoft.com...
> Hello,
> sql server 2000 cluster patched to SP3 and NT5 patched to SP4.
> Oracle 9i client installed.
> We have set up a linked server to an Oracle instance using the 'Microsoft
> OLE DB Provider for Oracle', this works for a while without any problems
> and
> then for an unknown reason ceases to work.
> The only error messages I have been able to get is by running the t-sql
> query
> SELECT * FROM LINKSERV..SCHEMA.TABLE which returns
> Server:Msg 7399, Level 16, State 1, Line 1
> OLE DB Provider MSDAORA reported an error.
> [OLE/DB provider returned message: Oracle error occured but error mess
age
> could not be retrieved from Oracle.}
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005:].
> However using the Oracle client on the server I can still connect to any
> Oracle instance but I have to stop and start the sql service to get the
> linked server to work again, becuase I have to stop and start sql server
> I'm
> presuming that the fault is with sql server.
>
>|||Yes, I've seen this document and it has been of help to me before but not in
this case as the linked server works and then stops without notice
"Jens Sü?meyer" wrote:

> Did you check out that article before ?
> HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server
> http://support.microsoft.com/d_efa...en-us;Q280_106
>
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Chris@.CambridgeUK" <Chris@.CambridgeUK@.discussions.microsoft.com> schrieb
im
> Newsbeitrag news:0BE21B83-BA02-4C0D-AA57-573AD76C3FB5@.microsoft.com...
>
>