ORDER BY is very slow when querying linked Server Oracle9i

I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal '
code below
--
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEWHi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal '
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:

> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>
>

ORDER BY is very slow when querying linked Server Oracle9i

I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal ?
code below
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEW
Hi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal ?
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>
|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:

> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>
>
sql

ORDER BY is very slow when querying linked Server Oracle9i

I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal '
code below
--
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEWHi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal '
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:
> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
> >I am creating 3 views from the linked server
> > and then I have a question at the bottom returning results.
> > If I exclude the ORDER BY from my question the query will take about 40
> > seconds,
> > with the ORDER BY it takes about an hour to finish.
> >
> > Is this normal '
> >
> > code below
> > --
> >
> > create view xxxyyy1 as
> > SELECT INSATSID, STATUS
> > FROM HACTAR..EKOP2.INSATS
> >
> > go
> >
> > create view xxxyyy2 as
> > SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> >
> > go
> >
> > create view xxxyyy3 as
> > select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> > 'INK905_STATUS'
> > from xxxyyy1 A left outer join xxxyyy2 B
> > on a.insatsid = b.insats
> > where a.status <> b.status
> > go
> >
> > SELECT
> > A.INSATSID,
> > A.STATUS_OLD,
> > A.STATUS_NEW,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > B.INSATSID,
> > B.STATUS,
> > B.INK905_INSATS,
> > B.INK905_STATUS
> > FROM HACTAR..EKOP2.INSATSLOG A
> > right outer join xxxyyy3 B
> > on A.INSATSID = B.INSATSID
> > WHERE A.ANDR_DATUM < 20060225
> > ORDER BY
> > A.INSATSID,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > A.STATUS_OLD,
> > A.STATUS_NEW
> >
>
>

Oracle Linked Servers

I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craig
To use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>

Oracle Linked Servers

I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally us
ed
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Exec
ute
returned 0x80040155].
Thanks for any help.
-craigTo use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally
used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>

Oracle Linked Servers

I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craigTo use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
--
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>

Oracle linked server trouble

Okay, here's the situation. I have a query that runs against a linked server
to an Oracle database (OLE DB connection, Oracle 8i client installed). It w
as fine running daily as a scheduled job until Thursday. On Thursday, some s
ecurity patches were applie
d by Windows Update on the server that hosts Oracle. The equivalent query (u
sing Oracle PL/SQL) still runs fine. A query like SELECT * FROM LNKNAME..USE
RNAME.TABLE is successful with no where clause. It is likewise successful u
sing OPENQUERY to submit th
e SQL to Oracle directly with whatever conditions I want. Virtual tables wor
k as long as there is no where clause. However, a query using qualified 4 pa
rt table names with a where clause or functions in the select list returns a
n error.
The only changes made were security patches applied to the server hosting Or
acle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the la
st query, using OPENQUERY, is a better solution than the first query because
all the tables are in the same Oracle database, but I will need to write qu
eries across this datasourc
e and others in the near future, making that less appealing. Plus, this simp
ly SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
) AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [A
LVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [N
LVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows retur
ned 0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I ge
t the same error using a query against this datasource even if I simplify it
to a single table with only text functions used, etc. It does not appear to
be related to any numeric
data, or to the number of operands (as Oracle suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.Hello Mck,
We would appreciate your patience while we are looking into this issue and
will post as soon as we have updates for you.
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Oracle linked server trouble
>thread-index: AcQ+hcDvT9r0qjysQwCeo1J8y3xEtw==
>X-WN-Post: microsoft.public.sqlserver.connect
>From: examnotes <mck@.newsgroups.nospam>
>Subject: Oracle linked server trouble
>Date: Thu, 20 May 2004 09:16:06 -0700
>Lines: 85
>Message-ID: <78572429-DA2C-4E17-B576-344CFCE73B5B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:41814
>NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Okay, here's the situation. I have a query that runs against a linked
server to an Oracle database (OLE DB connection, Oracle 8i client
installed). It was fine running daily as a scheduled job until Thursday. On
Thursday, some security patches were applied by Windows Update on the
server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still
runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful
with no where clause. It is likewise successful using OPENQUERY to submit
the SQL to Oracle directly with whatever conditions I want. Virtual tables
work as long as there is no where clause. However, a query using qualified
4 part table names with a where clause or functions in the select list
returns an error.
The only changes made were security patches applied to the server hosting
Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the
last query, using OPENQUERY, is a better solution than the first query
because all the tables are in the same Oracle database, but I will need to
write queries across this datasource and others in the near future, making
that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [A
LVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [N
LVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows retur
ned
0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I
get the same error using a query against this datasource even if I simplify
it to a single table with only text functions used, etc. It does not appear
to be related to any numeric data, or to the number of operands (as Oracle
suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.

>|||Hello Mck,
Looking at the issue, seems like you will need a detailed troubleshooting
including running MDAC trace etc which will give us
the metadata query that runs behind the scene and we can see where it is
failing..
However would be done quickly and effectively with direct assistance from
a Microsoft Support Professional through Microsoft Product Support
Services.
If you would like us to create an incident for you and have Microsoft
Customer Service Representative contact you directly, please send email to
(remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: <ID 22824565>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Engineer
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Oracle linked server trouble
>thread-index: AcQ+hcDvT9r0qjysQwCeo1J8y3xEtw==
>X-WN-Post: microsoft.public.sqlserver.connect
>From: examnotes <mck@.newsgroups.nospam>
>Subject: Oracle linked server trouble
>Date: Thu, 20 May 2004 09:16:06 -0700
>Lines: 85
>Message-ID: <78572429-DA2C-4E17-B576-344CFCE73B5B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:41814
>NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Okay, here's the situation. I have a query that runs against a linked
server to an Oracle database (OLE DB connection, Oracle 8i client
installed). It was fine running daily as a scheduled job until Thursday. On
Thursday, some security patches were applied by Windows Update on the
server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still
runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful
with no where clause. It is likewise successful using OPENQUERY to submit
the SQL to Oracle directly with whatever conditions I want. Virtual tables
work as long as there is no where clause. However, a query using qualified
4 part table names with a where clause or functions in the select list
returns an error.
The only changes made were security patches applied to the server hosting
Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the
last query, using OPENQUERY, is a better solution than the first query
because all the tables are in the same Oracle database, but I will need to
write queries across this datasource and others in the near future, making
that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [A
LVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [N
LVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows retur
ned
0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I
get the same error using a query against this datasource even if I simplify
it to a single table with only text functions used, etc. It does not appear
to be related to any numeric data, or to the number of operands (as Oracle
suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.

>|||Mck-
Curious if you ever got this resolved and how? I'm having pretty much the same issue. Ple
ase reply to sreid@.solutran.com, as I don't check this forum regularly.
Thanks!

Oracle linked server trouble

Okay, here's the situation. I have a query that runs against a linked server to an Oracle database (OLE DB connection, Oracle 8i client installed). It was fine running daily as a scheduled job until Thursday. On Thursday, some security patches were applie
d by Windows Update on the server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful with no where clause. It is likewise successful using OPENQUERY to submit th
e SQL to Oracle directly with whatever conditions I want. Virtual tables work as long as there is no where clause. However, a query using qualified 4 part table names with a where clause or functions in the select list returns an error.
The only changes made were security patches applied to the server hosting Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the last query, using OPENQUERY, is a better solution than the first query because all the tables are in the same Oracle database, but I will need to write queries across this datasourc
e and others in the near future, making that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)) AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [ALVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [NLVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I get the same error using a query against this datasource even if I simplify it to a single table with only text functions used, etc. It does not appear to be related to any numeric
data, or to the number of operands (as Oracle suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.
Hello Mck,
We would appreciate your patience while we are looking into this issue and
will post as soon as we have updates for you.
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Oracle linked server trouble
>thread-index: AcQ+hcDvT9r0qjysQwCeo1J8y3xEtw==
>X-WN-Post: microsoft.public.sqlserver.connect
>From: =?Utf-8?B?TWNL?= <mck@.newsgroups.nospam>
>Subject: Oracle linked server trouble
>Date: Thu, 20 May 2004 09:16:06 -0700
>Lines: 85
>Message-ID: <78572429-DA2C-4E17-B576-344CFCE73B5B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:41814
>NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Okay, here's the situation. I have a query that runs against a linked
server to an Oracle database (OLE DB connection, Oracle 8i client
installed). It was fine running daily as a scheduled job until Thursday. On
Thursday, some security patches were applied by Windows Update on the
server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still
runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful
with no where clause. It is likewise successful using OPENQUERY to submit
the SQL to Oracle directly with whatever conditions I want. Virtual tables
work as long as there is no where clause. However, a query using qualified
4 part table names with a where clause or functions in the select list
returns an error.
The only changes made were security patches applied to the server hosting
Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the
last query, using OPENQUERY, is a better solution than the first query
because all the tables are in the same Oracle database, but I will need to
write queries across this datasource and others in the near future, making
that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [ALVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [NLVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned
0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I
get the same error using a query against this datasource even if I simplify
it to a single table with only text functions used, etc. It does not appear
to be related to any numeric data, or to the number of operands (as Oracle
suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.

>
|||Hello Mck,
Looking at the issue, seems like you will need a detailed troubleshooting
including running MDAC trace etc which will give us
the metadata query that runs behind the scene and we can see where it is
failing..
However would be done quickly and effectively with direct assistance from
a Microsoft Support Professional through Microsoft Product Support
Services.
If you would like us to create an incident for you and have Microsoft
Customer Service Representative contact you directly, please send email to
(remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: <ID 22824565>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Engineer
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Oracle linked server trouble
>thread-index: AcQ+hcDvT9r0qjysQwCeo1J8y3xEtw==
>X-WN-Post: microsoft.public.sqlserver.connect
>From: =?Utf-8?B?TWNL?= <mck@.newsgroups.nospam>
>Subject: Oracle linked server trouble
>Date: Thu, 20 May 2004 09:16:06 -0700
>Lines: 85
>Message-ID: <78572429-DA2C-4E17-B576-344CFCE73B5B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:41814
>NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Okay, here's the situation. I have a query that runs against a linked
server to an Oracle database (OLE DB connection, Oracle 8i client
installed). It was fine running daily as a scheduled job until Thursday. On
Thursday, some security patches were applied by Windows Update on the
server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still
runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful
with no where clause. It is likewise successful using OPENQUERY to submit
the SQL to Oracle directly with whatever conditions I want. Virtual tables
work as long as there is no where clause. However, a query using qualified
4 part table names with a where clause or functions in the select list
returns an error.
The only changes made were security patches applied to the server hosting
Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the
last query, using OPENQUERY, is a better solution than the first query
because all the tables are in the same Oracle database, but I will need to
write queries across this datasource and others in the near future, making
that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [ALVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [NLVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned
0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I
get the same error using a query against this datasource even if I simplify
it to a single table with only text functions used, etc. It does not appear
to be related to any numeric data, or to the number of operands (as Oracle
suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.

>
|||Mck-
Curious if you ever got this resolved and how? I'm having pretty much
the same issue. Please reply to sreid@.solutran.com, as I don't check
this forum regularly.
Thanks!
sreid55
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message233123.html

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
>

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...
>> 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
>|||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:
>> 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|||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...
>> 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
>|||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...
>> 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
>
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:
>> 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:
>> 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
>>
> 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:
>> 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
>|||Randall Arnold wrote:
> 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:
>> 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
>
Sorry...I never understood that you didn't knew how to actually run the
sp's. You simple have to open a query window and then type in e.g.
EXEC sp_tables_ex 'YourOracleServerName' and then hit Execute or F5.
This will excute the query.
Regards
Steen|||Thanks for your patience with my ignorance Steen.
At first I couldn't find a query window, but stumbled across the Database
Engine Query icon on the toolbar and that does the trick.
After some more digging, I realized that all I need to do is change the
connection properties of the linked servers that the Microsoft Access linked
table wizard creates. But as I said before, Microsoft for reasons that
completely mistify me locks the property sheet after the linked server is
created. I'd love to find a way to edit it but I haven't so far.
What I did find was another server procedure, sp_helplinkedsrvlogin, that MS
*claims* will enable the admin to change the logins for those linked
servers. I ran it, and it did pull up a table of every linked server and
its properties. What the sp did NOT enable, however, was (once again)
editing of those properties! Despite the fact that KB article 280106
implies that it does.
So, I remain stuck. I could navigate the Oracle tables IF I could get them
into my existing Access data Project. But the adp can't see my existing
linked servers and every time its wizard creates one the new linked server
lacks the proper connection values and I'm unable to edit them.
: (
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:eQiJgJFRGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
>> 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:
>> 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
>>
> Sorry...I never understood that you didn't knew how to actually run the
> sp's. You simple have to open a query window and then type in e.g.
> EXEC sp_tables_ex 'YourOracleServerName' and then hit Execute or F5. This
> will excute the query.
> Regards
> Steen|||Randall Arnold wrote:
> Thanks for your patience with my ignorance Steen.
> At first I couldn't find a query window, but stumbled across the Database
> Engine Query icon on the toolbar and that does the trick.
> After some more digging, I realized that all I need to do is change the
> connection properties of the linked servers that the Microsoft Access linked
> table wizard creates. But as I said before, Microsoft for reasons that
> completely mistify me locks the property sheet after the linked server is
> created. I'd love to find a way to edit it but I haven't so far.
> What I did find was another server procedure, sp_helplinkedsrvlogin, that MS
> *claims* will enable the admin to change the logins for those linked
> servers. I ran it, and it did pull up a table of every linked server and
> its properties. What the sp did NOT enable, however, was (once again)
> editing of those properties! Despite the fact that KB article 280106
> implies that it does.
> So, I remain stuck. I could navigate the Oracle tables IF I could get them
> into my existing Access data Project. But the adp can't see my existing
> linked servers and every time its wizard creates one the new linked server
> lacks the proper connection values and I'm unable to edit them.
> : (
> Randall Arnold
Hi Randall
You're right that the article says that you can use
sp_helplinkedsrvlogin to set the login info, but that's not correct.
If you look up sp_helplinkedsrvlogin in Books On Line, it says that it
"Provides information about login mappings defined against a specific
linked server used for distributed queries and remote stored procedures".
I'm not sure where it is you can't change the properties for the linked
server. I'm not familiar with this MS Access Linked Server wizard, but
you you use EnterpriseManager (SQL 2000) or Microsoft SQL Server
Management Studio (SQL2005) you can the possibility to manage linked
servers. In Enterprise Manager is under "Security" -> "Linked Servers".
In Management Studio you'll find it under "Server Object" ->" Linked
Servers".
There are also a number of stored procedures that can be used -
sp_linkedservers
sp_addlinkedserver
sp_addlinkedsrvlogin
sp_droplinkedsrvlogin
sp_dropserver
You can look them up in Books On Line where you can find the syntax and
description.
Linked servers might not always be the easiest thing to work with, and
also I'm not quite sure if you can get all the info you're looking for
in this case.
Regards
Steen