Oracle DBA to learn MS SQL: databases

Showing posts with label databases. Show all posts

Monday, March 19, 2012

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>

Friday, March 9, 2012

Oracle Replication

I have two databases one is in head office and other is at factory site.
I want to share information between these two sites.
I have tried to complete setup of Oracle Advance replication but failed
to complete with errors
If Any body can help please go fast I am in need ...
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
try the oracle newsgroups.
"Amir Sajjad" <amir_sajjad@.hotmail.com> wrote in message
news:eCtICzIHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I have two databases one is in head office and other is at factory site.
> I want to share information between these two sites.
> I have tried to complete setup of Oracle Advance replication but failed
> to complete with errors
> If Any body can help please go fast I am in need ...
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

God morning!
I 've just installed SQL Server 2005 on Windows 2003 Server 64 bit. One of my databases need to connect to seveal Oracle databases.
- I tried installing 64 bit Orcale 9 on this server. Got a strange message that the application (Oracle 64 bit) could not be installed on a 32 bit server! Strange!
- I installed Oracle 9 32 bit on the server, but I could not find ODBC driver in the Data sources! Neither the oledb under providers in MSS Manager Studio!
- Same result efter several desintall and installation. A college copied the .dll files och run a script to register the dll files. We could then se the Oracle driver in the Database sources, but we could not create any connection.
- We have even tried an 10G version of Oracle with the same result.

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

I have posted this issue on several forums including Oracles site. We are going to leave that database which demand linked servers to Oracle on the 32 bit server. I am going to install SQL Server 2005 on that server later. But I am still hoping for some solution to this delicate problem.

|||On 64 bit Windows you have seperate 32 bit and 64 bit ODBC worlds. There is no thunking between 32 and 64 bit components as there was between 16 and 32 bit. To work with 32 bit drivers you need to run the 32 bit ODBC administrator C:\WINDOWS\SysWOW64\odbcad32.exe|||

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I managed fixing thanks to tips from Ron (http://www.dbforums.com/showthread.php?p=6242791#post6242791) by downloading and installning "Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (x64)" from Oracle download page.

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

Then I tried installong the 32 bit version. After that couldn't I see the odbc driver for Oracle in 64 bit odbc. At that time could I however fix my odbc source by running the 32 version av odbc! C:\Windows\SysWOW64\odbcad32.exe

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

I deinstalled Oracle 32 bit, downloaded x64 version av Oracle (after a tip from some one in the forum). The 64 bit version which I tried to install was for Itanium. I had AMD! I manage downloading X64 which is for AMD ("Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (x64)") from Oracle download page.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

I did not install the Oracle database however. I ran the custom installation together with our Oracle expert. We insatalled ODBC, Oledb, ... After that I could find both drivers under the 64 bit version of ODBC and under linked servers in SQL Server 2005.

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

1. Install 64-bit 10.2.0.1 Database into OraDb_home.
2. Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home.
3. Apply patch 5500954 for 64-bit - this is a Critical Bug patch 8 for 64-bit.
4. Apply patch 5337014 for 32-bit - this is a patchset to upgrade the Client to 10.0.2.0.3.
5. Contrary to what Oracle note 334528.1 says (which is to patch the 64-bit Server), you must patch/upgrade the 32-bit client as well (otherwise, it still fails).

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

Lastly, the note above describing the use of the 32-bit 10.3.0.1 does NOT note that there has turned out to be a bug in the sqora32.dll ODBC driver (and two others). This can be fixed by patch 5699495 from MetaLink - metalink.oracle.com.

- Will

|||

Dear Sir,

I have ASP v3 installed on Windows Server 2003 x64 which is connected to Oracle9i Database server. As you know ASP works under 32 bit Tech. So i need to install 32bit oracle client on the server to let ASP connect to Oracle. I tried to install oracle9i client but it didnot work ( the ODBC test connection works but the ASP cannot connect). I Also tried ODBC 32-bit Driver & ODAC all-in-one for 64bit & InstantClient .... all of the failed.

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek

|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Wednesday, March 7, 2012

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle linked server tables

I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David
|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen
|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen
|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "this
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they could
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen
|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, but
> I don't see how they help me in my need. I can't see anything that gets me
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
> 9.2 databases. I want to be able to view the Oracle tables in SS Management
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can't
> get any farther with the resources at hand, and MS docs, as I said, come up
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen
|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen
|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>

Oracle linked server tables

I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "thi
s
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they cou
ld
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, b
ut
> I don't see how they help me in my need. I can't see anything that gets m
e
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Orac
le
> 9.2 databases. I want to be able to view the Oracle tables in SS Manageme
nt
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can
't
> get any farther with the resources at hand, and MS docs, as I said, come u
p
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>

Monday, March 19, 2012

Oracle vs MS SQL Server Query Execution Speed

Friday, March 9, 2012

Oracle Replication

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Wednesday, March 7, 2012

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle linked server tables

Oracle linked server tables

Oracle DBA to learn MS SQL

Blog Archive

About Me