Oracle vs MS SQL Server Query Execution Speed

Hi,
I recently setup MS SQL Server and Oracle 9i on the same machine - and the world still has not come to an end ;-) - and both databases are started and operational. Both contain a single table, TRN, containing 6,348,752 rows. The SQL Server table was imported from the Oracle table and the tables have the same logical structure and the same number of indexes.
That's the background. Now the problem: I have a query that does monthly summaries. The Oracle query executes on average 13 times faster than the SQL Server Query. I have tried a number of suggestions on how to improve SQL performance but they me very little difference (tried STATISTICS, using the CAST function instead of the CONVERT function, tried the DATEPART function and using and ORDER BY on the same "fields" as used by the GROUP BY clause)
So over to you. I am open to any suggestions.
Here are the queries and table structures:
Oracle Query:
SELECT
METER_ID,
TO_CHAR(TS,'YYYY-MM') YEAR_MONTH,
SUM(UNITS) UNITS,
SUM(AMT) AMT,
COUNT(*) CNT
FROM
VPS.TRN
WHERE
TRN_TYPE_ID =3D 1 GROUP BY
METER_ID,
TO_CHAR(TS,'YYYY-MM')
Oracle Table:
CREATE TABLE VPS.TRN
(STN_ID NUMBER(15),
TRN_ID NUMBER(15) NOT NULL,
TRN_TYPE_ID NUMBER(15) NOT NULL,
PAY_TYPE_ID NUMBER(15),
BATCH_ITEM_ID NUMBER(15) NOT NULL,
AGR_ID NUMBER(15),
METER_ID NUMBER(15),
RES_ID NUMBER(15),
AMT NUMBER(19,4) NOT NULL,
TS DATE DEFAULT SYSDATE,
RNO VARCHAR2(20),
TKN VARCHAR2(30),
UNITS NUMBER(15,4),
TRF_ID NUMBER(15),
DEBT_ID NUMBER(15),
FCITEM_ID NUMBER(15),
TAXITEM_ID NUMBER(15),
GNO VARCHAR2(20),
REV_ID NUMBER(15) DEFAULT 0,
REP_ID NUMBER(15) DEFAULT 0,
COST NUMBER(19,4),
UERR NUMBER(7,3),
CMT VARCHAR2(255),
PAY_ID NUMBER(15),
UTIL_ID NUMBER(15) NOT NULL,
RSEQ NUMBER(15),
GSEQ NUMBER(15),
TSEQ NUMBER(15),
TNO VARCHAR2(20),
RDP_ID NUMBER(15),
CERR NUMBER(19,4),
DB_ID NUMBER(4),
REFERENCE_NO VARCHAR2(30),
SERVICE_TYPE_ID NUMBER(15),
OPERATOR_ID NUMBER(15),
TID DATE,
MSNO VARCHAR2(50),
SG_ID NUMBER(15),
CUST_ID_METHOD NUMBER(15),
ENCRYPTIONTYPE_TKTYPE_ID NUMBER(15),
VEND_REASON_ID NUMBER(15),
TARIFF_INDEX NUMBER(10),
NAME VARCHAR2(60),
ADDRESS VARCHAR2(255),
VOTE_ID NUMBER(15),
INVOICENO VARCHAR2(50),
RDP_UNITS NUMBER(15,4),
RDP_UNITS_ID NUMBER(15),
TRN_RECON_NO NUMBER(15),
MSG_ID NUMBER(15))
TABLESPACE VPS_DATA
/
CREATE INDEX VPS.TRN_PAY_TYPE_IDX ON VPS.TRN
(
PAY_TYPE_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_PAY_ASSOC ON VPS.TRN
(
PAY_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_AGR_ASSOC ON VPS.TRN
(
AGR_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_BATCHITEM_ASSOC ON VPS.TRN
(
BATCH_ITEM_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_STN_TSEQ_IDX ON VPS.TRN
(
STN_ID ASC,
TSEQ ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_DEBT_ASSOC ON VPS.TRN
(
DEBT_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_RECON_IDX ON VPS.TRN
(
TRN_RECON_NO ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_TS_IDX ON VPS.TRN
(
TS ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_OPERATOR_IDX ON VPS.TRN
(
OPERATOR_ID ASC
)
/
CREATE INDEX VPS.TRN_UTIL_IDX ON VPS.TRN
(
UTIL_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_DB_IDX ON VPS.TRN
(
DB_ID ASC,
TS ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_RDP_TS_IDX ON VPS.TRN
(
RDP_ID ASC,
TS ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_STN_GSEQ_IDX ON VPS.TRN
(
STN_ID ASC,
GSEQ ASC
)
/
CREATE INDEX VPS.TRN_TRNTYPE_ASSOC ON VPS.TRN
(
TRN_TYPE_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_REV_IDX ON VPS.TRN
(
REV_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_STN_RSEQ_IDX ON VPS.TRN
(
STN_ID ASC,
RSEQ ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_RDP_ASSOC ON VPS.TRN
(
RDP_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_GNO_IDX ON VPS.TRN
(
GNO ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.FK_TRN_VOTE ON VPS.TRN
(
VOTE_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_METER_ASSOC ON VPS.TRN
(
METER_ID ASC
) TABLESPACE VPS_INDEX
/
ALTER TABLE VPS.TRN
ADD CONSTRAINT PK_TRN PRIMARY KEY (TRN_ID)
USING INDEX TABLESPACE VPS_INDEX
/
MS SQL Server Query:
SELECT
METER_ID,
CONVERT( varchar(7), TS, 120) YEAR_MONTH,
SUM(UNITS) UNITS,
SUM(AMT) AMT,
COUNT(*) CNT
FROM
VPS.TRN
WHERE
TRN_TYPE_ID =3D 1
GROUP BY
METER_ID,
CONVERT( varchar(7), TS, 120)
MS SQL Server Table:
CREATE TABLE [VPS].[TRN] (
[STN_ID] [numeric](15, 0) NULL ,
[TRN_ID] [numeric](15, 0) NOT NULL ,
[TRN_TYPE_ID] [numeric](15, 0) NOT NULL ,
[PAY_TYPE_ID] [numeric](15, 0) NULL ,
[BATCH_ITEM_ID] [numeric](15, 0) NOT NULL ,
[AGR_ID] [numeric](15, 0) NULL ,
[METER_ID] [numeric](15, 0) NULL ,
[RES_ID] [numeric](15, 0) NULL ,
[AMT] [numeric](19, 4) NOT NULL ,
[TS] [datetime] NULL ,
[RNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TKN] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNITS] [numeric](15, 4) NULL ,
[TRF_ID] [numeric](15, 0) NULL ,
[DEBT_ID] [numeric](15, 0) NULL ,
[FCITEM_ID] [numeric](15, 0) NULL ,
[TAXITEM_ID] [numeric](15, 0) NULL ,
[GNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REV_ID] [numeric](15, 0) NULL ,
[REP_ID] [numeric](15, 0) NULL ,
[COST] [numeric](19, 4) NULL ,
[UERR] [numeric](7, 3) NULL ,
[CMT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAY_ID] [numeric](15, 0) NULL ,
[UTIL_ID] [numeric](15, 0) NOT NULL ,
[RSEQ] [numeric](15, 0) NULL ,
[GSEQ] [numeric](15, 0) NULL ,
[TSEQ] [numeric](15, 0) NULL ,
[TNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RDP_ID] [numeric](15, 0) NULL ,
[CERR] [numeric](19, 4) NULL ,
[DB_ID] [numeric](4, 0) NULL ,
[REFERENCE_NO] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SERVICE_TYPE_ID] [numeric](15, 0) NULL ,
[OPERATOR_ID] [numeric](15, 0) NULL ,
[TID] [datetime] NULL ,
[MSNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SG_ID] [numeric](15, 0) NULL ,
[CUST_ID_METHOD] [numeric](15, 0) NULL ,
[ENCRYPTIONTYPE_TKTYPE_ID] [numeric](15, 0) NULL ,
[VEND_REASON_ID] [numeric](15, 0) NULL ,
[TARIFF_INDEX] [numeric](10, 0) NULL ,
[NAME] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VOTE_ID] [numeric](15, 0) NULL ,
[INVOICENO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RDP_UNITS] [numeric](15, 4) NULL ,
[RDP_UNITS_ID] [numeric](15, 0) NULL ,
[TRN_RECON_NO] [numeric](15, 0) NULL ,
[MSG_ID] [numeric](15, 0) NULL ) ON [VPS_DATA]
GO
ALTER TABLE [VPS].[TRN] WITH NOCHECK ADD CONSTRAINT [PK_TRN] PRIMARY KEY CLUSTERED (
[TRN_ID]
) ON [VPS_DATA] GO
ALTER TABLE [VPS].[TRN] ADD CONSTRAINT [DF__TRN__TS__34C8D9D1] DEFAULT (getdate
()) FOR [TS],
CONSTRAINT [DF__TRN__REV_ID__35BCFE0A] DEFAULT (0) FOR [REV_ID],
CONSTRAINT [DF__TRN__REP_ID__36B12243] DEFAULT (0) FOR [REP_ID]
GO
CREATE INDEX [TRN_PAY_TYPE_IDX] ON [VPS].[TRN]
([PAY_TYPE_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_PAY_ASSOC] ON [VPS].[TRN]([PAY_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_AGR_ASSOC] ON [VPS].[TRN]([AGR_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_BATCHITEM_ASSOC] ON [VPS].[TRN]
([BATCH_ITEM_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_STN_TSEQ_IDX] ON [VPS].[TRN]([STN_ID], [TSEQ]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_DEBT_ASSOC] ON [VPS].[TRN]([DEBT_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_RECON_IDX] ON [VPS].[TRN]
([TRN_RECON_NO]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_TS_IDX] ON [VPS].[TRN]([TS]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_OPERATOR_IDX] ON [VPS].[TRN]
([OPERATOR_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_UTIL_IDX] ON [VPS].[TRN]([UTIL_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_DB_IDX] ON [VPS].[TRN]([DB_ID], [TS]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_RDP_TS_IDX] ON [VPS].[TRN]([RDP_ID], [TS]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_STN_GSEQ_IDX] ON [VPS].[TRN]([STN_ID], [GSEQ]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_TRNTYPE_ASSOC] ON [VPS].[TRN]
([TRN_TYPE_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_REV_IDX] ON [VPS].[TRN]([REV_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_STN_RSEQ_IDX] ON [VPS].[TRN]([STN_ID], [RSEQ]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_RDP_ASSOC] ON [VPS].[TRN]([RDP_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_GNO_IDX] ON [VPS].[TRN]([GNO]) ON [VPS_DATA]
GO
CREATE INDEX [FK_TRN_VOTE] ON [VPS].[TRN]([VOTE_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_METER_ASSOC] ON [VPS].[TRN]
([METER_ID]) ON [VPS_DATA]
GO
Software on Server:
Windows 2000 Server 5.00.2195 Service Pack 3
SQL Server Enterprise Edition 8.00.760 (SP3)
Oracle9i Enterprise Edition Release 9.2.0.3.0
Software on Client:
Windows 2000 Professional 5.00.2195 Service Pack 3
Vincent van der Vlis
Application Frameworks - Creating a framework of value
Microsoft Certified Partner
2nd Floor Waterside Place
Tygervalley Waterfront
South Gate
Carl Cronj=E9 Drive
Bellville
South Africa
7550
Phone Office: +27 21 914 9666 or 914 9775
Phone Mobile: +27 82 789 7049
Fax: +27 21 914 9826
Email: vincent@.appframeworks.com1) if SQL Server is using a parallel execution plan, turn it off
2) if this is a Xeon/Xeon MP system, disable Hyper-
Threading
3) make sure only one of SQL Server or Oracle is running during the test, or ensure that each can only use the same amount of memory
4) Are you executing this code on the client system from Query Analyzer or your own application?
If your own app, try seeing if there is a difference from Query Analyzer.
I am going to guess that TRN_TYPE_ID =3D 1 involves a large number of rows, such that the SQL Server execution plan for your query has a table scan on TRN at the far right (in Display Estimated Execution Plan)
Depending on the distribution of METER_ID and TS date value, you could have a Stream Aggregate or Hash Match,
The Hash Match is prefered for large row count on the group by clause.
If this is a very large Hash Match operation (>10-30K rows in group by clause) SQL Server will spool the results to the tempdb, hence sequential transfer performance of the tempdb is very important
Still, why don't you try the following on SQL Server,
it involves a computed column, sorry I don't know what the Oracle syntax for this is
ALTER TABLE [VPS].[TRN] ADD TS7 AS CONVERT( varchar(7), TS, 120)
CREATE INDEX [TRN_COVERED_IDX] ON [VPS].[TRN]( [TRN_TYPE_ID], [METER_ID], [TS7], UNITS, AMT) ON [VPS_DATA]
GO
SELECT METER_ID, TS7 YEAR_MONTH, SUM(UNITS) UNITS, SUM
(AMT) AMT, COUNT(*) CNT FROM VPS.TRN WHERE TRN_TYPE_ID =3D 1
GROUP BY METER_ID, TS7
OPTION (MAXDOP 1)
-joe chang
>--Original Message--
>Hi,
>I recently setup MS SQL Server and Oracle 9i on the same >machine - and the world still has not come to an end ;-
) - >and both databases are started and operational. Both >contain a single table, TRN, containing 6,348,752 rows. >The SQL Server table was imported from the Oracle table >and the tables have the same logical structure and the >same number of indexes.
>That's the background. Now the problem: I have a query >that does monthly summaries. The Oracle query executes on >average 13 times faster than the SQL Server Query. I have >tried a number of suggestions on how to improve SQL >performance but they me very little difference (tried >STATISTICS, using the CAST function instead of the CONVERT >function, tried the DATEPART function and using and ORDER >BY on the same "fields" as used by the GROUP BY clause)
>So over to you. I am open to any suggestions.
>Here are the queries and table structures:
>Oracle Query:
>SELECT
> METER_ID,
> TO_CHAR(TS,'YYYY-MM') YEAR_MONTH,
> SUM(UNITS) UNITS,
> SUM(AMT) AMT,
> COUNT(*) CNT
>FROM
> VPS.TRN
>WHERE
> TRN_TYPE_ID =3D 1 >GROUP BY
> METER_ID,
> TO_CHAR(TS,'YYYY-MM')
>Oracle Table:
>CREATE TABLE VPS.TRN
> (STN_ID NUMBER(15),
> TRN_ID NUMBER(15) NOT NULL,
> TRN_TYPE_ID NUMBER(15) NOT NULL,
> PAY_TYPE_ID NUMBER(15),
> BATCH_ITEM_ID NUMBER(15) NOT NULL,
> AGR_ID NUMBER(15),
> METER_ID NUMBER(15),
> RES_ID NUMBER(15),
> AMT NUMBER(19,4) NOT NULL,
> TS DATE DEFAULT SYSDATE,
> RNO VARCHAR2(20),
> TKN VARCHAR2(30),
> UNITS NUMBER(15,4),
> TRF_ID NUMBER(15),
> DEBT_ID NUMBER(15),
> FCITEM_ID NUMBER(15),
> TAXITEM_ID NUMBER(15),
> GNO VARCHAR2(20),
> REV_ID NUMBER(15) DEFAULT 0,
> REP_ID NUMBER(15) DEFAULT 0,
> COST NUMBER(19,4),
> UERR NUMBER(7,3),
> CMT VARCHAR2(255),
> PAY_ID NUMBER(15),
> UTIL_ID NUMBER(15) NOT NULL,
> RSEQ NUMBER(15),
> GSEQ NUMBER(15),
> TSEQ NUMBER(15),
> TNO VARCHAR2(20),
> RDP_ID NUMBER(15),
> CERR NUMBER(19,4),
> DB_ID NUMBER(4),
> REFERENCE_NO VARCHAR2(30),
> SERVICE_TYPE_ID NUMBER(15),
> OPERATOR_ID NUMBER(15),
> TID DATE,
> MSNO VARCHAR2(50),
> SG_ID NUMBER(15),
> CUST_ID_METHOD NUMBER(15),
> ENCRYPTIONTYPE_TKTYPE_ID NUMBER(15),
> VEND_REASON_ID NUMBER(15),
> TARIFF_INDEX NUMBER(10),
> NAME VARCHAR2(60),
> ADDRESS VARCHAR2(255),
> VOTE_ID NUMBER(15),
> INVOICENO VARCHAR2(50),
> RDP_UNITS NUMBER(15,4),
> RDP_UNITS_ID NUMBER(15),
> TRN_RECON_NO NUMBER(15),
> MSG_ID NUMBER(15))
>TABLESPACE VPS_DATA
>/
>CREATE INDEX VPS.TRN_PAY_TYPE_IDX ON VPS.TRN
> (
> PAY_TYPE_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_PAY_ASSOC ON VPS.TRN
> (
> PAY_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_AGR_ASSOC ON VPS.TRN
> (
> AGR_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_BATCHITEM_ASSOC ON VPS.TRN
> (
> BATCH_ITEM_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_STN_TSEQ_IDX ON VPS.TRN
> (
> STN_ID ASC,
> TSEQ ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_DEBT_ASSOC ON VPS.TRN
> (
> DEBT_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_RECON_IDX ON VPS.TRN
> (
> TRN_RECON_NO ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_TS_IDX ON VPS.TRN
> (
> TS ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_OPERATOR_IDX ON VPS.TRN
> (
> OPERATOR_ID ASC
> )
>/
>CREATE INDEX VPS.TRN_UTIL_IDX ON VPS.TRN
> (
> UTIL_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_DB_IDX ON VPS.TRN
> (
> DB_ID ASC,
> TS ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_RDP_TS_IDX ON VPS.TRN
> (
> RDP_ID ASC,
> TS ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_STN_GSEQ_IDX ON VPS.TRN
> (
> STN_ID ASC,
> GSEQ ASC
> )
>/
>CREATE INDEX VPS.TRN_TRNTYPE_ASSOC ON VPS.TRN
> (
> TRN_TYPE_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_REV_IDX ON VPS.TRN
> (
> REV_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_STN_RSEQ_IDX ON VPS.TRN
> (
> STN_ID ASC,
> RSEQ ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_RDP_ASSOC ON VPS.TRN
> (
> RDP_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_GNO_IDX ON VPS.TRN
> (
> GNO ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.FK_TRN_VOTE ON VPS.TRN
> (
> VOTE_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_METER_ASSOC ON VPS.TRN
> (
> METER_ID ASC
> ) >TABLESPACE VPS_INDEX
>/
>ALTER TABLE VPS.TRN
>ADD CONSTRAINT PK_TRN PRIMARY KEY (TRN_ID)
>USING INDEX TABLESPACE VPS_INDEX
>/
>
>MS SQL Server Query:
>SELECT
> METER_ID,
> CONVERT( varchar(7), TS, 120) YEAR_MONTH,
> SUM(UNITS) UNITS,
> SUM(AMT) AMT,
> COUNT(*) CNT
>FROM
> VPS.TRN
>WHERE
> TRN_TYPE_ID =3D 1
>GROUP BY
> METER_ID,
> CONVERT( varchar(7), TS, 120)
>MS SQL Server Table:
>CREATE TABLE [VPS].[TRN] (
> [STN_ID] [numeric](15, 0) NULL ,
> [TRN_ID] [numeric](15, 0) NOT NULL ,
> [TRN_TYPE_ID] [numeric](15, 0) NOT NULL ,
> [PAY_TYPE_ID] [numeric](15, 0) NULL ,
> [BATCH_ITEM_ID] [numeric](15, 0) NOT NULL ,
> [AGR_ID] [numeric](15, 0) NULL ,
> [METER_ID] [numeric](15, 0) NULL ,
> [RES_ID] [numeric](15, 0) NULL ,
> [AMT] [numeric](19, 4) NOT NULL ,
> [TS] [datetime] NULL ,
> [RNO] [varchar] (20) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [TKN] [varchar] (30) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNITS] [numeric](15, 4) NULL ,
> [TRF_ID] [numeric](15, 0) NULL ,
> [DEBT_ID] [numeric](15, 0) NULL ,
> [FCITEM_ID] [numeric](15, 0) NULL ,
> [TAXITEM_ID] [numeric](15, 0) NULL ,
> [GNO] [varchar] (20) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [REV_ID] [numeric](15, 0) NULL ,
> [REP_ID] [numeric](15, 0) NULL ,
> [COST] [numeric](19, 4) NULL ,
> [UERR] [numeric](7, 3) NULL ,
> [CMT] [varchar] (255) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [PAY_ID] [numeric](15, 0) NULL ,
> [UTIL_ID] [numeric](15, 0) NOT NULL ,
> [RSEQ] [numeric](15, 0) NULL ,
> [GSEQ] [numeric](15, 0) NULL ,
> [TSEQ] [numeric](15, 0) NULL ,
> [TNO] [varchar] (20) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [RDP_ID] [numeric](15, 0) NULL ,
> [CERR] [numeric](19, 4) NULL ,
> [DB_ID] [numeric](4, 0) NULL ,
> [REFERENCE_NO] [varchar] (30) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [SERVICE_TYPE_ID] [numeric](15, 0) NULL ,
> [OPERATOR_ID] [numeric](15, 0) NULL ,
> [TID] [datetime] NULL ,
> [MSNO] [varchar] (50) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [SG_ID] [numeric](15, 0) NULL ,
> [CUST_ID_METHOD] [numeric](15, 0) NULL ,
> [ENCRYPTIONTYPE_TKTYPE_ID] [numeric](15, 0) NULL ,
> [VEND_REASON_ID] [numeric](15, 0) NULL ,
> [TARIFF_INDEX] [numeric](10, 0) NULL ,
> [NAME] [varchar] (60) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS] [varchar] (255) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [VOTE_ID] [numeric](15, 0) NULL ,
> [INVOICENO] [varchar] (50) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [RDP_UNITS] [numeric](15, 4) NULL ,
> [RDP_UNITS_ID] [numeric](15, 0) NULL ,
> [TRN_RECON_NO] [numeric](15, 0) NULL ,
> [MSG_ID] [numeric](15, 0) NULL >) ON [VPS_DATA]
>GO
>ALTER TABLE [VPS].[TRN] WITH NOCHECK ADD > CONSTRAINT [PK_TRN] PRIMARY KEY CLUSTERED > (
> [TRN_ID]
> ) ON [VPS_DATA] >GO
>ALTER TABLE [VPS].[TRN] ADD > CONSTRAINT [DF__TRN__TS__34C8D9D1] DEFAULT (getdate
>()) FOR [TS],
> CONSTRAINT [DF__TRN__REV_ID__35BCFE0A] DEFAULT (0) >FOR [REV_ID],
> CONSTRAINT [DF__TRN__REP_ID__36B12243] DEFAULT (0) >FOR [REP_ID]
>GO
> CREATE INDEX [TRN_PAY_TYPE_IDX] ON [VPS].[TRN]
>([PAY_TYPE_ID]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_PAY_ASSOC] ON [VPS].[TRN]([PAY_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_AGR_ASSOC] ON [VPS].[TRN]([AGR_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_BATCHITEM_ASSOC] ON [VPS].[TRN]
>([BATCH_ITEM_ID]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_STN_TSEQ_IDX] ON [VPS].[TRN]
([STN_ID], >[TSEQ]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_DEBT_ASSOC] ON [VPS].[TRN]
([DEBT_ID]) >ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_RECON_IDX] ON [VPS].[TRN]
>([TRN_RECON_NO]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_TS_IDX] ON [VPS].[TRN]([TS]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_OPERATOR_IDX] ON [VPS].[TRN]
>([OPERATOR_ID]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_UTIL_IDX] ON [VPS].[TRN]([UTIL_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_DB_IDX] ON [VPS].[TRN]([DB_ID], [TS]) >ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_RDP_TS_IDX] ON [VPS].[TRN]([RDP_ID], >[TS]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_STN_GSEQ_IDX] ON [VPS].[TRN]
([STN_ID], >[GSEQ]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_TRNTYPE_ASSOC] ON [VPS].[TRN]
>([TRN_TYPE_ID]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_REV_IDX] ON [VPS].[TRN]([REV_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_STN_RSEQ_IDX] ON [VPS].[TRN]
([STN_ID], >[RSEQ]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_RDP_ASSOC] ON [VPS].[TRN]([RDP_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_GNO_IDX] ON [VPS].[TRN]([GNO]) ON >[VPS_DATA]
>GO
> CREATE INDEX [FK_TRN_VOTE] ON [VPS].[TRN]([VOTE_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_METER_ASSOC] ON [VPS].[TRN]
>([METER_ID]) ON [VPS_DATA]
>GO
>
>Software on Server:
> Windows 2000 Server 5.00.2195 Service Pack 3
> SQL Server Enterprise Edition 8.00.760 (SP3)
> Oracle9i Enterprise Edition Release 9.2.0.3.0
>Software on Client:
> Windows 2000 Professional 5.00.2195 Service Pack 3
>Vincent van der Vlis
>Application Frameworks - Creating a framework of value
>Microsoft Certified Partner
>2nd Floor Waterside Place
>Tygervalley Waterfront
>South Gate
>Carl Cronj=E9 Drive
>Bellville
>South Africa
>7550
>Phone Office: +27 21 914 9666 or 914 9775
>Phone Mobile: +27 82 789 7049
>Fax: +27 21 914 9826
>Email: vincent@.appframeworks.com
>.
>|||If the world only revolves around this particularly query, the it would
help to make the primary key non-clustered, and add a clustered index on
(TRN_TYPE_ID,METER_ID,TS).
Other suggestions:
1. Make sure the statistics are up to date. If not (or when in doubt)
run UPDATE STATISTICS.
2. As Andrew partly mentioned, it is more space efficient to use:
- int instead of numeric(15,0) (assuming the values will not exceed 2
billion),
- smallint instead of numeric(4,0),
- money instead of numeric(19,4)
- smalldatetime instead of datetime (assuming no precision beyond
minute is needed, and the date does not exceed 2079)
This is especially true for the columns in the proposed clustered index.
3. If you are using different disks for your different table spaces in
Oracle, then for good comparison you should create different filegroups
in SQL-Server (each group on its own disk), and create the indexes in a
different filegroup that the table.
Hope this helps,
Gert-Jan
Vincent wrote:
> Hi,
> I recently setup MS SQL Server and Oracle 9i on the same
> machine - and the world still has not come to an end ;-) -
> and both databases are started and operational. Both
> contain a single table, TRN, containing 6,348,752 rows.
> The SQL Server table was imported from the Oracle table
> and the tables have the same logical structure and the
> same number of indexes.
> That's the background. Now the problem: I have a query
> that does monthly summaries. The Oracle query executes on
> average 13 times faster than the SQL Server Query. I have
> tried a number of suggestions on how to improve SQL
> performance but they me very little difference (tried
> STATISTICS, using the CAST function instead of the CONVERT
> function, tried the DATEPART function and using and ORDER
> BY on the same "fields" as used by the GROUP BY clause)
> So over to you. I am open to any suggestions.
<snip>

Oracle provider error

I am using SQL Server 2005 on a 64 bit AMD processor machine using the Oracle 10.0.2.0.2.0 and SELECT OPENQUERY will return results from my Oracle table. When I try to do an UPDATE OPENQUERY I get this error message

Msg 7333, Level 16, State 2, Line 1
Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server "ORADB".

the same UPDATE OPENQUERY works on SQL Server 2000 using the Oracle 9 client. The provider is configured with Inprocess turned on. Any help would be appreciated.

Hi,

I once has a problem that I had to give a result back from my stored procedure in oracle although I didn#t actually need to return anything. It was just needed for the provider to do some mapping to "a" resultset. perhps you should try that if you are only about to execute a stored procedure with no return values at all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Is the behavior the same when you use MSDAORA? Have you checked Oracle's knowledge base? It's also a good idea to install the latest patches for the Oracle client.

From the description of the problem it sounds as the Provider is having a problem fetching a row from the Oracle DBMS specified by the SQL engine by a bookmark. If it worked with Oracle 9/SQL 2k, the cause could be either the SQL engine requesting the row from OLEDB in a different manner or an issue within the OraOLEDB provider. Using MSDAORA instead of OraOLEDB would determine where the issue resides.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

We have the same problem here. I don't have any answer yet...

For info, this is for the 64 bit server. With 32 bit and msdaora everything works perfectly...

Where is the native oracle driver for sqlserver 64 bit entreprise version as said in the doc/promo/site ?

Should we downgrade to 32 bit because the 64 bit can't even work with oracle ?

I hope to find an answer soon...

greetings

|||I had the same problem on my x64 box and resolved it (mostly) by installing the Oracle ODAC (available on the Oracle Technology Network) and a patch (#5043675 available on Metalink) that fixes a bug that causes updates and deletes to fail.

Oracle provider error

I am using SQL Server 2005 on a 64 bit AMD processor machine using the Oracle 10.0.2.0.2.0 and SELECT OPENQUERY will return results from my Oracle table. When I try to do an UPDATE OPENQUERY I get this error message

Msg 7333, Level 16, State 2, Line 1
Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server "ORADB".

the same UPDATE OPENQUERY works on SQL Server 2000 using the Oracle 9 client. The provider is configured with Inprocess turned on. Any help would be appreciated.

Hi,

I once has a problem that I had to give a result back from my stored procedure in oracle although I didn#t actually need to return anything. It was just needed for the provider to do some mapping to "a" resultset. perhps you should try that if you are only about to execute a stored procedure with no return values at all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Is the behavior the same when you use MSDAORA? Have you checked Oracle's knowledge base? It's also a good idea to install the latest patches for the Oracle client.

From the description of the problem it sounds as the Provider is having a problem fetching a row from the Oracle DBMS specified by the SQL engine by a bookmark. If it worked with Oracle 9/SQL 2k, the cause could be either the SQL engine requesting the row from OLEDB in a different manner or an issue within the OraOLEDB provider. Using MSDAORA instead of OraOLEDB would determine where the issue resides.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

We have the same problem here. I don't have any answer yet...

For info, this is for the 64 bit server. With 32 bit and msdaora everything works perfectly...

Where is the native oracle driver for sqlserver 64 bit entreprise version as said in the doc/promo/site ?

Should we downgrade to 32 bit because the 64 bit can't even work with oracle ?

I hope to find an answer soon...

greetings

|||I had the same problem on my x64 box and resolved it (mostly) by installing the Oracle ODAC (available on the Oracle Technology Network) and a patch (#5043675 available on Metalink) that fixes a bug that causes updates and deletes to fail.

Oracle provider error

I am using SQL Server 2005 on a 64 bit AMD processor machine using the Oracle 10.0.2.0.2.0 and SELECT OPENQUERY will return results from my Oracle table. When I try to do an UPDATE OPENQUERY I get this error message

Msg 7333, Level 16, State 2, Line 1
Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server "ORADB".

the same UPDATE OPENQUERY works on SQL Server 2000 using the Oracle 9 client. The provider is configured with Inprocess turned on. Any help would be appreciated.

Hi,

I once has a problem that I had to give a result back from my stored procedure in oracle although I didn#t actually need to return anything. It was just needed for the provider to do some mapping to "a" resultset. perhps you should try that if you are only about to execute a stored procedure with no return values at all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Is the behavior the same when you use MSDAORA? Have you checked Oracle's knowledge base? It's also a good idea to install the latest patches for the Oracle client.

From the description of the problem it sounds as the Provider is having a problem fetching a row from the Oracle DBMS specified by the SQL engine by a bookmark. If it worked with Oracle 9/SQL 2k, the cause could be either the SQL engine requesting the row from OLEDB in a different manner or an issue within the OraOLEDB provider. Using MSDAORA instead of OraOLEDB would determine where the issue resides.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

We have the same problem here. I don't have any answer yet...

For info, this is for the 64 bit server. With 32 bit and msdaora everything works perfectly...

Where is the native oracle driver for sqlserver 64 bit entreprise version as said in the doc/promo/site ?

Should we downgrade to 32 bit because the 64 bit can't even work with oracle ?

I hope to find an answer soon...

greetings

|||I had the same problem on my x64 box and resolved it (mostly) by installing the Oracle ODAC (available on the Oracle Technology Network) and a patch (#5043675 available on Metalink) that fixes a bug that causes updates and deletes to fail.

oracle oledb provider not registered in local machine

Hi,

I am trying to establish a connection to an Oracle database using the following code in a script task:

Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=DBxxx;" & _
"User ID=Userxxx;" & _
"Password=Passxxx"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

When I execute the script task, I receive the following error:
The 'OraOLEDB.Oracle' provider is not registered on the local machine.
Am using the correct provider?

I do not know how to resolve the said error.
Here are some facts:
Oracle 8i is installed.
Tnsnames.ora is updated.
I have successfully connected to Oracle SQL *Plus to test the above credentials.

Please help.
Thanks.

Do you have the Oracle OLE DB driver installed?

http://www.oracle.com/technology/software/tech/windows/ole_db/index.html|||

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

|||

r214acc wrote:

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

The Microsoft OLE DB Provider for Oracle is not the same as the Oracle OLE DB Provider. The Oracle OLE DB Provider is published by Oracle, not Microsoft, and isn't part of the SQL Server 2005 standard installation. I know this because I'm running SQL Server Standard on one of my machines.|||So, like I said, try downloading the Oracle OLE DB driver, and then try your code.|||

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

|||

r214acc wrote:

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

Sorry, I don't have an answer for your question. However, I have a question for you. Are you using the 64 bit version of SQL Server 2005?|||

I am using 32 bit version of SQL Server 2005.
Is this the cause of the problem?

|||

There are some issues with Oracle drivers on 64 bit, hence Duane asked.

You say Oracle and SQL with SSIS are installed on the same server. That is fine, but are you really working on the server itself or a desktop?

|||

Hi,

I am working on the server itself.
I found something in the forum that may be related to the problem:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=449593&SiteID=17
It says that there is a bug on parsing folder names with parenthesis which happens when a 64-bit OS installs 32-bit programs.
Since I am running a 32-bit SSIS. This may be the problem.

Is there a fix here?
Thanks.

Oracle linked server : won't run if driver AllowInProc is off

I've tested a link server from msde to oracle on a win2k server test machine
and after some effort, it worked properly. I noted down the steps i took to
get it to work. I could enable or disable AllowInProc for the Microsoft OLE
DB Oracle driver i was using to interface with Oracle. This machine is in a
test domain.
Now i wanted to configure the same thing on a server in our real domain.
That server is a true SQL server and is running multiple instances. The
thing is, i can't seem to get it to work if i disable the driver's
AllowInProc. When i disable AllowInProc i get this error mesage when trying
to see the tables of the linked server using Enterprise Manager:
Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
returned 0x80070005: Access denier.].
I tried reinstalling the latest MDAC (2.8) and rebooting the server
afterwards but it didn't change anything. I checked the Microsoft article
280106 and tried
If anybody has any ideas on this i'd be happy to read about them since i am
kind of running out of ideas right now...After much screaming and crying i found the potential cause. It seems the
driver doesn't receive the domain login hence returning "Access Denied". We
made a regular sql login and when used, everything worked perfectly. Any
idea if there are some services related to the driver or the oracle stuff
that might need to run under a different user account ?
Gonna try using odbc instead...
I'm posting all this in the hope that someone might help me or that it might
help someone.
"/dev/null" wrote:
> I've tested a link server from msde to oracle on a win2k server test machine
> and after some effort, it worked properly. I noted down the steps i took to
> get it to work. I could enable or disable AllowInProc for the Microsoft OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i am
> kind of running out of ideas right now...|||Why don't you use oracle driver instead? You can install ORACLE client on
the SQL box and you should be able to get it working with no issues.
HTH
"/dev/null" </dev/null@.discussions.microsoft.com> wrote in message
news:9B5EF51E-2CBA-4B2F-8841-0A400396288A@.microsoft.com...
> I've tested a link server from msde to oracle on a win2k server test
machine
> and after some effort, it worked properly. I noted down the steps i took
to
> get it to work. I could enable or disable AllowInProc for the Microsoft
OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in
a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when
trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i
am
> kind of running out of ideas right now...|||"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Why don't you use oracle driver instead? You can install ORACLE client on
> the SQL box and you should be able to get it working with no issues.
>
The Oracle Client is required for MSDAORA as well, so it is already
installed on the box. I assume that the OP is uncomfortable running the
Oralce OleDb drivers and the Oracle Client inside the SqlServer address
space for fear it could crash Sql Server.
And quite honestly, I would not allow ad-hoc queries to an Oracle linked
server with the Oracle Client in-process in a production Sql Server. With
canned queries and testing, ok, but it's still a concern.
David|||We don't want to run the driver in process for the reason you thought: sql
server crashes = we better run damn fast for our lives.
It's not that i didn't try the driver provided by oracle but i never got it
to work. It seemed even more trouble than the microsoft driver. Even then i
suspect we'd have to be running it in-process.
"David Browne" wrote:
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > Why don't you use oracle driver instead? You can install ORACLE client on
> > the SQL box and you should be able to get it working with no issues.
> >
> The Oracle Client is required for MSDAORA as well, so it is already
> installed on the box. I assume that the OP is uncomfortable running the
> Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> space for fear it could crash Sql Server.
> And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> server with the Oracle Client in-process in a production Sql Server. With
> canned queries and testing, ok, but it's still a concern.
> David
>
>|||I just tested using an ODBC data source using the Microsoft ole db for Oracle
driver and it's the same issue. Is it because we set up SQL server to run
under a specific user account (not localsystem or whatever the default is) ?
the linked server was set up to use an odbc source, a source which used the
same driver as before...
and the fun won't stop!
"/dev/null" wrote:
> We don't want to run the driver in process for the reason you thought: sql
> server crashes = we better run damn fast for our lives.
> It's not that i didn't try the driver provided by oracle but i never got it
> to work. It seemed even more trouble than the microsoft driver. Even then i
> suspect we'd have to be running it in-process.
> "David Browne" wrote:
> > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> > news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > > Why don't you use oracle driver instead? You can install ORACLE client on
> > > the SQL box and you should be able to get it working with no issues.
> > >
> >
> > The Oracle Client is required for MSDAORA as well, so it is already
> > installed on the box. I assume that the OP is uncomfortable running the
> > Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> > space for fear it could crash Sql Server.
> >
> > And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> > server with the Oracle Client in-process in a production Sql Server. With
> > canned queries and testing, ok, but it's still a concern.
> >
> > David
> >
> >
> >|||I think you hit the problem. I was having the same issues until I decided to
test it under the local system account instead of the domain account my SQL
Server was originally set.
Under the local system account, I had no problems setting a linked Oracle
server, in proc or out of proc, but when SQL Server is running under the
domain account, the connection doesn't work.
I've try to place the domain account in my local adminstrator group, but
that doesn't work either.
I've left my SQL Server running under my local system account for now, if
someone knows a fix for this issue, please post it.
H.Rosental
"/dev/null" wrote:
> I just tested using an ODBC data source using the Microsoft ole db for Oracle
> driver and it's the same issue. Is it because we set up SQL server to run
> under a specific user account (not localsystem or whatever the default is) ?
> the linked server was set up to use an odbc source, a source which used the
> same driver as before...
> and the fun won't stop!
> "/dev/null" wrote:
> > We don't want to run the driver in process for the reason you thought: sql
> > server crashes = we better run damn fast for our lives.
> >
> > It's not that i didn't try the driver provided by oracle but i never got it
> > to work. It seemed even more trouble than the microsoft driver. Even then i
> > suspect we'd have to be running it in-process.
> >
> > "David Browne" wrote:
> >
> > > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> > > news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > > > Why don't you use oracle driver instead? You can install ORACLE client on
> > > > the SQL box and you should be able to get it working with no issues.
> > > >
> > >
> > > The Oracle Client is required for MSDAORA as well, so it is already
> > > installed on the box. I assume that the OP is uncomfortable running the
> > > Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> > > space for fear it could crash Sql Server.
> > >
> > > And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> > > server with the Oracle Client in-process in a production Sql Server. With
> > > canned queries and testing, ok, but it's still a concern.
> > >
> > > David
> > >
> > >
> > >

Oracle Client Exception

Hi,

I've been working with MS XP OS and Oracle Client 9i (9.2) and my project was working properly. In another machine with the OS: MS Windows 2003 Server SP.1 and Oracle Client 10g I'm getting the error "System.Data.OracleClient requires Oracle client software version 8.1.7 or greater " when trying to open a connection.

Please help,

thnx

Hi,

YOu either have the Oracle client not installed / the installation is messed up and you will have to install it again or it could be based on the following issue:

http://dotnetjunkies.com/WebLog/rtgurskevik/archive/2005/01/19/45958.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I had a similar problem with Reporting Services trying to access an Oracle 9i db, this response from Robert Bruckner helped:

Another user replied to a Forums thread you started !

Re: 2005 Reporting Srv cannot connect to Oracle datasource on web server

Posted by Robert Bruckner MSFT in SQL Server Reporting Services

Most likely the RS web service and the RS windows service cannot access the Oracle client installation due to missing file system permissions. Please r! ead the following KB article: http://support.microsoft.com/default.aspx?scid=kb;en-us;870668

-- Robert

Hope this helps you as well

Richard

Oracle Client Exception

Hi,

I've been working with MS XP OS and Oracle Client 9i (9.2) and my project was working properly. In another machine with the OS: MS Windows 2003 Server SP.1 and Oracle Client 10g I'm getting the error "System.Data.OracleClient requires Oracle client software version 8.1.7 or greater " when trying to open a connection.

Please help,

thnx

Hi,

YOu either have the Oracle client not installed / the installation is messed up and you will have to install it again or it could be based on the following issue:

http://dotnetjunkies.com/WebLog/rtgurskevik/archive/2005/01/19/45958.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I had a similar problem with Reporting Services trying to access an Oracle 9i db, this response from Robert Bruckner helped:

Another user replied to a Forums thread you started !

Re: 2005 Reporting Srv cannot connect to Oracle datasource on web server

Posted by Robert Bruckner MSFT in SQL Server Reporting Services

Most likely the RS web service and the RS windows service cannot access the Oracle client installation due to missing file system permissions. Please r! ead the following KB article: http://support.microsoft.com/default.aspx?scid=kb;en-us;870668

-- Robert

Hope this helps you as well

Richard

oracle and sql

is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnxThey both sure can be installed on the same machine. But I would only do
that on a development machine, not in production.
In a typical production scenario, both the RDBMSes will be competing for the
resources on the machine. Even from a maintenance point of view, if you have
to reboot the machine for the sake of applying an Oracle patch, you will
unnecessarily impact SQL Server users.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnx|||Hi,
I also agree with Vyas on this. Earlier I had a non critical production
server which has both SQL Server and Oracle.
For applying any pathes on SQL Server or Oracle needs a reboot and will
cause a downtime.
As well as the resource usage also will be high. So I recommend you to have
either one of this RDBMS installed in a single server.
Thanks
Hari
SQL Server MVP
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the
> oracle server service more than 10 users max
> what issues could arise with such a setup
> thnx
>|||> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the orac
le
> server service more than 10 users max
> what issues could arise with such a setup
> thnx
OK
thnx for your input
it'll be an older server that will be used then
grtz

oracle and sql

is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnx
They both sure can be installed on the same machine. But I would only do
that on a development machine, not in production.
In a typical production scenario, both the RDBMSes will be competing for the
resources on the machine. Even from a maintenance point of view, if you have
to reboot the machine for the sake of applying an Oracle patch, you will
unnecessarily impact SQL Server users.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnx
|||Hi,
I also agree with Vyas on this. Earlier I had a non critical production
server which has both SQL Server and Oracle.
For applying any pathes on SQL Server or Oracle needs a reboot and will
cause a downtime.
As well as the resource usage also will be high. So I recommend you to have
either one of this RDBMS installed in a single server.
Thanks
Hari
SQL Server MVP
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the
> oracle server service more than 10 users max
> what issues could arise with such a setup
> thnx
>
|||> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the oracle
> server service more than 10 users max
> what issues could arise with such a setup
> thnx
OK
thnx for your input
it'll be an older server that will be used then
grtz

oracle and sql

is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnxThey both sure can be installed on the same machine. But I would only do
that on a development machine, not in production.
In a typical production scenario, both the RDBMSes will be competing for the
resources on the machine. Even from a maintenance point of view, if you have
to reboot the machine for the sake of applying an Oracle patch, you will
unnecessarily impact SQL Server users.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnx|||Hi,
I also agree with Vyas on this. Earlier I had a non critical production
server which has both SQL Server and Oracle.
For applying any pathes on SQL Server or Oracle needs a reboot and will
cause a downtime.
As well as the resource usage also will be high. So I recommend you to have
either one of this RDBMS installed in a single server.
Thanks
Hari
SQL Server MVP
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the
> oracle server service more than 10 users max
> what issues could arise with such a setup
> thnx
>|||> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the oracle
> server service more than 10 users max
> what issues could arise with such a setup
> thnx
OK
thnx for your input
it'll be an older server that will be used then
grtz