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!