Oracle with Crystal: "Failed to Load Database Information"

I've already deployed the Crystal .msi and installed the Oracle Client on the server, and still getting this message:

Temp\temp_294ca6ef-60f1-4511-b5a5-c6986817b96d.rpt: Failed to load database information.

I gave permission to the ASP.NET user in all the folders it needs, Oracle, Ora92 and Ora92/bin, Temp...

In the development computer it runs OK, but in Production server its crashs!

Someone can help me? Any ideas will be quite welcome!

Thanks in advance!

Fernando Ruano.Does your server support crystal report?
you can try to load the crystal report in your server where from your application is running.|||Yes, my server support it because the deployment was already made.

And when I create an ODBC with Oracle it works fine.

Only when my app tries to call a .rtp Crystal Report from the server causes the failed load database.

How come?? O_o'|||See if you are able to find solution here
http://www.businessobjects.com/support/default.asp

Oracle Syntax Error.

Hi,

I have a Oracle query which runs fine at Oracle side(through TOAD or Oracle Client).

I am using sysdate+1 in my query to get the next date.

Now I am using same query in RS using Oracle Client which returns error "ORA-00932:Inconsistent datatypes.Expected DATE got NUMBER".

Basically the driver is unable to interpret to add 1(Number) to the Sysdate(Date) field.

Any suggestions/ideas are greatly appreciated.

Thanks !!

Maybe in your query you should do an explicit CONVERT back to date on the sysdate + 1 expression? I think you may be relying on an implicit conversion which is not happening in this case.

Oracle Source Connection Error

Hi,

I was trying to connect to Oracle Source in SSIS

1. Err: Test connection failed because of an error in initializing provider. Oracle Client and networking components were not found

Sol: I installeed the Oracle Client tools

2. Err: ORA -12154: TNS: Could not resolve teh connect identifier specified

Sol: Then I point out the TNS file

3. Now I coonect the Oracle db . I go to Connection Manager . Specifies the provide as: Native OLE DB\ Oracle Provide for OLE DB, Select my DB, provide teh user name and password. and check if the connection passed.

Then in my data Flow Task when I add this source and check the Colmns its gives me an error./ sort of warning

The component reported the following warnings:

Warning at {C947B.......} [Ole DB Source[1]]: Cannot retrieve the column code page info from the OLE DB provider. if the components supports teh "DefaultCodePage" property, the code page from that property will be used.....

Will this warning has an significance. i dont see any error in my output data. but is there something I m doing wrong.

4. When I set my ProtectionLevel under Security in package property to "Dont Save Sensitive" in order to easily configure the package I m getting this error: failed Validation. The default one was EncryptSensitiveWithUserKey then its running smoothly.

Can somebody advice me on this?

I addded the all the errors and solution for 1 and 2 becuase in that way this will be helpful in future.

One more thing...

What should be my Package property Protection layer in oreder to smoothly transfer and run the package on other computer.

P.S. I' m using Oracle Source where in I have to give the user name and password in order to connect it.

|||

To use Oracle for development you have to install the correct Oracle client for the version of Oracle you are connecting to and you need a TNS.ORA file with your connection info and permissions in your development folder because your error says your permissions could not be resolved. One more thing if your database is in 9i make sure your client is for 9i and not 10g. Hope this helps.

|||What should be my protection Level in Package in order to make work anywhere on any computer. I am using user name; and password in order to connect to server|||

Microsoft support have covered the protection level and related issues in link one and two covers how to use the Agent to run your packages.

http://support.microsoft.com/kb/918760/

http://support.microsoft.com/kb/912911

Oracle report returning strange data

I have a RS report that is connecting to an Oracle Database (8.1.7), the
8.1.7 oracle client is installed on RS Sever, and the result set is returning
strange data. I'm using a simple view that was created in the Oracle
database, that returns a small record set about 100 records. I'm seeing ASCII
values in place of some data, in the Data tab and the Preview tab and the
Published report.
I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
Oracle, and I get the same results.
Some of the data looks just fine but some of the characters get replaced by
ASCII values.
I have installed RS SP1.
Don't know what I am doing wrong...Hi Gary
We are running SQL Reporting Services (SP1) against an Oracle 9i database
without any problems. In our case 9.2.0.4 client is used to talk with
9.2.0.4 database.
I suspect the problem you are having is related to version of Oracle client
you are using. If you have access to a client 9.2.0.4 or higher you may want
to give that a try.
"garyc" wrote:
> I have a RS report that is connecting to an Oracle Database (8.1.7), the
> 8.1.7 oracle client is installed on RS Sever, and the result set is returning
> strange data. I'm using a simple view that was created in the Oracle
> database, that returns a small record set about 100 records. I'm seeing ASCII
> values in place of some data, in the Data tab and the Preview tab and the
> Published report.
> I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
> Oracle, and I get the same results.
> Some of the data looks just fine but some of the characters get replaced by
> ASCII values.
> I have installed RS SP1.
> Don't know what I am doing wrong...

Oracle query does not work in SSIS

Hello All,

I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:

select 'AAA-'||OWNER AS SOURCE,
table_name,
column_name,
SUBSTR(data_type,1,50) DATA_TYPE ,
SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH
from all_tab_cols
where owner='XXX'
ORDER BY TABLE_NAME, COLUMN_ID

Here ARE the following errorS I get when running from SSIS:

[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".

[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.

Any help?

Regards,

Raju

I would suggest testing the query in the execute sql task before running the job to try and see if it works there, maybe try another oracle driver and play with the different syntax like || and +.

Larry

|||

Larry,

The problem was with column allias "SOURCE".... looks like it is a keyword in ssis. We just changed the alias to SRC_SCHEMA and it worked. btb we were using the query in execute SQL task.

Thanks

Oracle query does not work in SSIS

Hello All,

I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:

select 'AAA-'||OWNER AS SOURCE,
table_name,
column_name,
SUBSTR(data_type,1,50) DATA_TYPE ,
SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH
from all_tab_cols
where owner='XXX'
ORDER BY TABLE_NAME, COLUMN_ID

Here ARE the following errorS I get when running from SSIS:

[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".

[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.

Any help?

Regards,

Raju

I would suggest testing the query in the execute sql task before running the job to try and see if it works there, maybe try another oracle driver and play with the different syntax like || and +.

Larry

|||

Larry,

The problem was with column allias "SOURCE".... looks like it is a keyword in ssis. We just changed the alias to SRC_SCHEMA and it worked. btb we were using the query in execute SQL task.

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 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 error

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

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

Oracle linked server error

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

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

Oracle linked server (64-bit)

We have an active-active cluster on two 64-bit servers, with an
instance of SQL Server running on each node.
We installed the 64-bit oracle client and it works just fine using
Oracle's "SQL Plus" app. But when we add it as a linked server in SQL
Server, it hangs when we try to access it, whether in the Enterprise
Manager or executing "sp_catalogs". It then kicks us off and the node
has to be rebooted for all the services and groups to come back online
properly.
we have a linked server to Oracle on another 64-bit Sql Server (not
clustered) which works just fine for us.
Has anyone seen this? Any helpful thoughts?
bryan
Make sure the provider is setup to execute in-process with SQL Server, by
default it is not since it is not a Microsoft provider, but the Oracle
provider does not work correctly out of process.
See http://support.microsoft.com/default...B;EN-US;280106
NOTE : If you use a third-party Oracle provider and it is incapable of
running outside of a SQL Server process, allow it to run in-process by
changing the provider options. To change the provider options, change the
value of the AllowInProcess (DWORD) entry in the following registry key,
which is located under the corresponding provider name, to 1:
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"bk" <r.bryan.kane@.gmail.com> wrote in message
news:1102633081.687411.319750@.f14g2000cwb.googlegr oups.com...
> We have an active-active cluster on two 64-bit servers, with an
> instance of SQL Server running on each node.
> We installed the 64-bit oracle client and it works just fine using
> Oracle's "SQL Plus" app. But when we add it as a linked server in SQL
> Server, it hangs when we try to access it, whether in the Enterprise
> Manager or executing "sp_catalogs". It then kicks us off and the node
> has to be rebooted for all the services and groups to come back online
> properly.
> we have a linked server to Oracle on another 64-bit Sql Server (not
> clustered) which works just fine for us.
> Has anyone seen this? Any helpful thoughts?
> bryan
>

Oracle Linked Server

Hi,
I have an Oracle 9i client installed on SQL Server 2000, and I want to add a
linked Oracle server to SQL Server, I got an error says 'OLEDB error, OLEDB
error trace: initialize returned 0x80004005', and I can not view the tables
or views in the Oracle lin
ked server, can anybody help me on this?
HongHi
0x80004005 is usually a general error, have you checked the connection
string?
http://www.connectionstrings.com/
John
"Hong Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:8AB9BCEB-F6C5-4AD6-ACA2-0A3C0A8890CC@.microsoft.com...
> Hi,
> I have an Oracle 9i client installed on SQL Server 2000, and I want to add
a linked Oracle server to SQL Server, I got an error says 'OLEDB error,
OLEDB error trace: initialize returned 0x80004005', and I can not view the
tables or views in the Oracle linked server, can anybody help me on this?
> Hong|||Try turning on trace flag 7300 on or use SQL Profiler to capture the
OLEDB Errors event. This should allow you to get more information
about the error.
For additional info, you may want to refer to the following:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106
-Sue
On Wed, 2 Jun 2004 13:01:08 -0700, Hong Wang
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have an Oracle 9i client installed on SQL Server 2000, and I want to add a linked
Oracle server to SQL Server, I got an error says 'OLEDB error, OLEDB error trace: i
nitialize returned 0x80004005', and I can not view the tables or views in the Oracle
li
nked server, can anybody help me on this?
>Hong

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji
When YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas

"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me o
n
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hjiWhen YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas
"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hjiWhen YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas
"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

oracle driver

Where can I download the Oracle driver for SQL Server Reporting Server? The
Oracle Client is 500MB, wasn't sure if there's something smaller for just the
driver. Thanks!I don't think there is a smaller download for the Oracle client. Make sure
you are installing version 8.1.7 or later of the Oracle client.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Teresa" <Teresa@.discussions.microsoft.com> wrote in message
news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
> Where can I download the Oracle driver for SQL Server Reporting Server?
> The
> Oracle Client is 500MB, wasn't sure if there's something smaller for just
> the
> driver. Thanks!|||Hi Robert,
Thanks! You seem very knowledgable with Rpt Svcs. I saw your posts in many
places. I'm running into a more critical issue with executing Oracle reports.
I kept getting the "rsErrorOpeningConnection with Oracle" when running from
the server and I know I can make the db connection from the server. And I
also double check the permission in the Oracle folder per one of your
posting. Could you think of anything else I can try?
Thank you very much!
"Robert Bruckner [MSFT]" wrote:
> I don't think there is a smaller download for the Oracle client. Make sure
> you are installing version 8.1.7 or later of the Oracle client.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Teresa" <Teresa@.discussions.microsoft.com> wrote in message
> news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
> > Where can I download the Oracle driver for SQL Server Reporting Server?
> > The
> > Oracle Client is 500MB, wasn't sure if there's something smaller for just
> > the
> > driver. Thanks!
>
>|||My first suggestion is to make sure that you have rebooted your server once
after the Oracle client installation happened (I have seen some machines
where applications running inside ASP.NET, such as report server, cannot
access the Oracle client without a reboot after the client installation).
Then, if this is RS 2005, you have to apply the following steps:
* make sure the WebServiceIdentity is configured through the RS
Configuration tool; often it is set to the Network Service user. You can
also check it by looking up the <WebServiceAccount> element in the
RSReportServer.config file.
* For the specified <WebServiceAccount> apply these steps on the Oracle
client installation directory (Oracle\Oracle version):
Right-click the directory, and then Properties. On the security tab, add the
<WebServiceAccount> and grant read&execute permission. Important: click on
the advanced button and select "Allow inheritable permissions from the
parent ..." before you click OK!
Note: it is also necessary to grant the account read&execute permissions
that is used to execute the RS Windows Service. For RS 2005, both the RS
Windows Service account and the configured RS Web Service account must have
permissions to access the files of the Oracle client installation in the
file system! On RS 2000, this was generally only needed for the RS Windows
Service account.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
PS: I guess I should know a few things about RS since I'm
designing/implementing some areas of the product :)
"Teresa" <Teresa@.discussions.microsoft.com> wrote in message
news:6BF6E4C3-B612-454D-99A7-DDFF431A04D2@.microsoft.com...
> Hi Robert,
> Thanks! You seem very knowledgable with Rpt Svcs. I saw your posts in many
> places. I'm running into a more critical issue with executing Oracle
> reports.
> I kept getting the "rsErrorOpeningConnection with Oracle" when running
> from
> the server and I know I can make the db connection from the server. And I
> also double check the permission in the Oracle folder per one of your
> posting. Could you think of anything else I can try?
> Thank you very much!
> "Robert Bruckner [MSFT]" wrote:
>> I don't think there is a smaller download for the Oracle client. Make
>> sure
>> you are installing version 8.1.7 or later of the Oracle client.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Teresa" <Teresa@.discussions.microsoft.com> wrote in message
>> news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
>> > Where can I download the Oracle driver for SQL Server Reporting Server?
>> > The
>> > Oracle Client is 500MB, wasn't sure if there's something smaller for
>> > just
>> > the
>> > driver. Thanks!
>>

Oracle client?

I'm using the Oracle data connector within Reporting Services because it uses
the Oracle client, which should be the most efficient.
There is very little description/documentation I can find in Reporting
Services help about this connector and it is difficult to figure out what it
is doing and what connection parameters it supports. Is it documented
anywhere?
For example, I tried to set the connection pool information in the
connection string (using the same properties as described in the ODP.NET
documentation), but for the oracle server log, it doesn't seem to be using
them correctly.
At this point I'm am considering writing a DPE for ODP.NET, just to make
sure I have that control.
Brendan.ODP.Net is not being used. Microsoft managed provider is being used. The
provider requires 8.1.7 or higher client to be installed I believe. It gets
even more confusing though. If you use the graphical query designer (4 pane)
it uses OLEDB. If you use generic query designer (2 pane) it uses the
managed provider. When you are at runtime it uses the MS managed provider
for Oracle. My suggestion is to always use the generic query designer so you
are developing with the same provider that you will be running under.
Hopefully this will get more clearcut with the next release (November).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brendan Whelan" <BrendanWhelan@.discussions.microsoft.com> wrote in message
news:94800733-02F7-4D4D-9782-72CADEC7FE6E@.microsoft.com...
> I'm using the Oracle data connector within Reporting Services because it
> uses
> the Oracle client, which should be the most efficient.
> There is very little description/documentation I can find in Reporting
> Services help about this connector and it is difficult to figure out what
> it
> is doing and what connection parameters it supports. Is it documented
> anywhere?
> For example, I tried to set the connection pool information in the
> connection string (using the same properties as described in the ODP.NET
> documentation), but for the oracle server log, it doesn't seem to be using
> them correctly.
> At this point I'm am considering writing a DPE for ODP.NET, just to make
> sure I have that control.
> Brendan.
>|||I use oracle and SRS now with no problems. you just have to install the
oracle client on the SRS server and configure a service using net manager.
once you can get to oracle using Sql plus then you can use the client to
connect to oracle. This is the fastest way using the native Oracle client.
Bruce L-C [MVP] wrote:
>ODP.Net is not being used. Microsoft managed provider is being used. The
>provider requires 8.1.7 or higher client to be installed I believe. It gets
>even more confusing though. If you use the graphical query designer (4 pane)
>it uses OLEDB. If you use generic query designer (2 pane) it uses the
>managed provider. When you are at runtime it uses the MS managed provider
>for Oracle. My suggestion is to always use the generic query designer so you
>are developing with the same provider that you will be running under.
>Hopefully this will get more clearcut with the next release (November).
>> I'm using the Oracle data connector within Reporting Services because it
>> uses
>[quoted text clipped - 15 lines]
>> Brendan.
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

Oracle Client Exception

Hi,

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

Please help,

thnx

Hi,

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

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

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

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

Another user replied to a Forums thread you started !

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

Posted by Robert Bruckner MSFT in SQL Server Reporting Services

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

-- Robert

Hope this helps you as well

Richard

Oracle Client Exception

Hi,

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

Please help,

thnx

Hi,

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

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

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

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

Another user replied to a Forums thread you started !

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

Posted by Robert Bruckner MSFT in SQL Server Reporting Services

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

-- Robert

Hope this helps you as well

Richard

Oracle 9i Client on SQL Server 2000 Ent.

I installed Oracle 9i client on a SQL Server 2000
Enterprise Edition server with Windows 2000 Advanced
Server operating system. Next, I opened the linked server
and lastly I tried to open the Microsoft OLE DB Provider
for SQL Server which caused the server to hang and had to
be rebooted.
Please help me with what could cause this problem?
Thank You,
MikeHonestly I don't know the answer, so I can just throw some
ideas:
I assume it was working fine before you installed the
Oracle 9i client. Just a crazy though, try to reboot your
server couple times and see if that fixes the problem.
If it still doesn't work then remove the Oracle 9i client
and see what happens.
hth.
>--Original Message--
>I installed Oracle 9i client on a SQL Server 2000
>Enterprise Edition server with Windows 2000 Advanced
>Server operating system. Next, I opened the linked
server
>and lastly I tried to open the Microsoft OLE DB Provider
>for SQL Server which caused the server to hang and had to
>be rebooted.
>Please help me with what could cause this problem?
>Thank You,
>Mike
>.
>