Oracle DBA to learn MS SQL: connection

Showing posts with label connection. Show all posts

Monday, March 19, 2012

Oracle10 ODBC connection

I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures this
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and no
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie
|||"Arnie" wrote:

> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i

> - Arnie
>
>

Oracle10 ODBC connection

I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures t
his
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no
default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get."HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and n
o
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie|||"Arnie" wrote:

> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i

> - Arnie
>
>

Monday, March 12, 2012

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

Friday, March 9, 2012

Oracle Problems...

I get:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help Cannot create a connection to data source 'ODBC'. (rsErrorOpeningConnection) Get Online Help ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified

when trying to view a report that runs in Visual Studio .NET. After I deploy the report to the Reporting Services server I get the error. There must be a problem in the server's configuration, right? What can I do?

I seem to have gotten this to work. I found a post somewhere on the Internet about a guy DELETING the NLS registry key. This key can be found by searching the registry on the server for home0. I tried it once without restarting the server and it didn't work. Then I restored the key and continued to look for more information. After not find anything, I decided to delete the key again and bounce the server. PRESTO!

oracle oledb provider not registered in local machine

Hi,

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

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

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

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

Please help.
Thanks.

Do you have the Oracle OLE DB driver installed?

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

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

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

|||

r214acc wrote:

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

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

Hi Duane,

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

Please help me find the answer. Thanks.

|||

r214acc wrote:

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

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

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

|||

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

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

|||

Hi,

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

Is there a fix here?
Thanks.

Oracle OleDb Provider as Source

Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.

Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.

Thanks,

Rick

Update:

If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:

Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.

Error at DFT_LoadDimEntities: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Any suggestions would be greatly appreciated.

Thank you,

Rick

|||

This turned out to be a combination of quirks related to Oracle tooling and syntax.

I have learned that integrating Oracle is a multi-faceted project. The first phase is syntax normalization and dealing with the tooling anamolies that come up. The second phase is getting to true interop, where I can flip flop providers on source and target and have the same package, same code base just work.

I think I am just about done with phase 1 stuff, and here are some things to consider if you run into similar problems (many of these sound rediculously trivial, but when something just works against SQL and stops working against Oracle, it can be madenning):

1. Make sure that you are saving the connection string information during design time. The only way to do this is to select one of the "Encrypt..." options. Whereas SQL source/target Connection Managers seem to hapilly retain connection info (i.e. Windows Auth, makes sense), Oracle credentials in the connection string will not get saved. I have found that this creates a number of un-intuitive error messages.

2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.

3. Just because your code runs in both SQL and Oracle doesn't preclude strange "tooling" issues as I can them from cropping up. For example, in an OLE DB Source task, the MSDAORA provider does not seem to like comments (-- Blah) as the first line. This one drove me nuts for a good couple of days.

If I think of anything else, I'll post it, but I think that most of these problems have been addressed here and on the following blog posting which may prove helpful to others: http://rickgaribay.net/archive/2007/03/15/font-facearialstrikeadventuresstrikefont-contortions--with-ssis-oracle-interop.aspx

If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.

Rick

Oracle OleDb Provider as Source

Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.

Thanks,

Rick

Update:

If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:

Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.

Error at DFT_LoadDimEntities: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Any suggestions would be greatly appreciated.

Thank you,

Rick

|||

This turned out to be a combination of quirks related to Oracle tooling and syntax.

2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.

If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.

Rick

Wednesday, March 7, 2012

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 Connection Error After Upgrading to SQL 2005

Hello all.

I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.

We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:

=====================

OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".

Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12

Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".

=====================

A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.

Thanks,

Adam

Hi Adam,

You mentioned you are running Win2003 x64 edition. SQL 2000 has a 64-bit edition which supports only Itanium architecture, so I am assuming you had been running the 32-bit SQL edition in WOW, is this correct? Did you upgrade to a 32-bit edition of SQL 2005, too? BTW - there's no 64-bit version of MSDAORA, the 64-bit platform comes with the 32-bit version of the provider, so I am assuming that you are using everything 32-bit in WOW, correct? I am wondering if this might be related to the problem.

Regarding the error - apparently the provider initializes and finds the Oracle client. However, the client returns the ORA-06413. This is a typical connectivity error and could be caused by many factors, for instance see the following links:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=288371&SiteID=1

http://www.dbforums.com/archive/index.php/t-1294142.html

In general, SQL 2005 wouldn't change the way MSDAORA is used, except for tightening security and some other similar details. I would check the following:

(a) How is the linked server security configured?

(b) Does it make a difference when you connect to SQL with NT or with SQL authentication?

(d) If you create a short ADO script (vbs or js) and use it to connect to the Oracle server, are you experiencing the same errors?

(e) If you use the Oracle's OLEDB provider, do you get the same error?

In addition, the following KB articles might be of help:

How to set up and troubleshoot a linked server to an Oracle database in SQL Server http://support.microsoft.com/kb/280106

INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider http://support.microsoft.com/kb/244661/

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

Saturday, February 25, 2012

Oracle drivers in connection managers

I am not seeing an option to use the OraOLEDB.Oracle driver when defining connections in ssis. This driver shows up in management studio when trying to create linked servers so I don't know why it wouldn't show up here. All the client tools are installed fine and the linked server works. Any ideas?This might be a novice answer, but are you sure the actual OLE driver is installed? I'm no Oracle expert by far, but the client and the actual driver that my Oracle dba gives for sql server installation are on seperate disks.|||Yes, the oledb drivers are installed. One important thing I failed to mention is that this is a 64 bit server. What appears to be happening is that even though we can create a linked server with the 64 bit oracle oledb driver, when you create a package in ssis it only lists the 32 bit native oledb drivers. I confirmed this by installing the 32 bit oracle client stuff as well and now the oracle oledb driver is listed in the drop downs. This seems a little absurd that ssis would only use the 32 bit drivers, can anyone explain this?|||

"The 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. If you want to select a specific 64-bit provider to configure a connection manager, you must have the 32-bit version of the provider installed. However, you can still run the package in 64-bit mode in the development environment. Because the 32-bit and 64-bit versions of a provider have the same ID, the runtime selects the appropriate version to use based on the value of the Run64BitRuntime project property, which by default is True."

Found in this article....

http://msdn2.microsoft.com/en-us/library(d=robot)/ms141766.aspx

|||

Thanks for the info, that's a big help.

I understand why they did this but just like every other weird x64 quirk you have to spend hours digging through various documentation sources to even find a mention of it. Please just give us a comprehensive x64 guide instead of spreading stuff out in a thousand different places.

Oracle data source connection problem using Report Manager

I have an issue when using Report Manager for viewing reports that use a certain Oracle data source. When viewing the reports in Report Designer the reports display without problems. But when trying to display the reports with Report Manager, after deploying the reports to the Report Server, I now get the following error message:

blablabla....ORA-12705: invalid or unknown NLS parameter value specified

For testing purposes I am running Report Designer, Report Server and Report Manager locally on a PC with Windows XP. I am using Reporting Services 2005, IIS 6, Visual Studio 2005, an Oracle 9.x client and an Oracle 9.x (not 100% sure) database that is hosted on a remote server.

Earlier I got an error message that stated "ORA-12154: TNS: could not resolve the connect identifier specified". Access rights to some Oracle folders for the Network service account did the trick here but then I got the "NLS parameter" error message. What I have tried to do is removing the NLS_LANG register variable and also modified the value of the variable so that it matches the value of the NLS_LANG variable on the server that hosts the Oracle database, but none of them have worked. Any ideas?

Maybe this is an issue that is more appropriate in an Oracle forum but what the heck..the problem could reside in Reporting Services..

Thanks, Stefan

I found the solution to the problem. I was jerking around with the wrong NLS_LANG registry variable. Everywhere I have looked it says that the NLS_LANG variable is found in the Home0 directory in the Windows registry, so naturally I have tested settings with that NLS_LANG variable. Also, the first thing I did was to search the registry for the NLS_LANG variable and the only search result was the one found under the Home0 directory. Out of coincidence, I found another NLS_LANG variable located directly under the Oracle directory in the registry. This variable was set to "NA". I changed it to AMERICAN_AMERICA.WE8ISO8859P15, and boom shakalak!

/Stefan

oracle connection using configuration file

I created a package that uses configuration file for db connections. SQL Server connection works fine, but for oracle it fails.

I m putting servername=alias from tnsnames.ora file.

the connection works fine from ide

For an OLE-DB connection the value of the config file property assignment, for connection string, would look something like this-

Data Source=ABC.XYZ;User ID=UserName;Password=secret;Provider=MSDAORA.1;Persist Security Info=True;

ABC.XYZ is the alias from tnsnames.

|||

Thanks Darren...

I exactly have the same line as you suggested. The issue was with InitialCatalog propterty. I took that out from configuration file and it worked fine.

I read somewhere only the servername, userid and password property is enough to make a connection to oracle database.

Thanks

Mahesh

Oracle Connection String Stored in SQL Server Configuration Not Working

I'm trying to store connection information in a SQL Server SSIS Configuration. I can see the information in the SSISConfigurations table and it appears to be reading the data as the package loads in the designer, but I'm getting connection failure messages

(The AcquireConnection method call to the connection manager "APPLPROD" failed with error code 0xC0202009. MTL_SYSTEM_ITEMS.dtsx 0 0).

Do I have to do anything in the package other than set up the initial configuration? Any clues as to why my connection isn't picking up the configuration information?
Thanks!

Mark

Hi Mark,

for security reasons, we stripe out the password from a connection string. You need to go and change the configuration to include the password.

HTH,
Ovidiu Burlacu

|||Ovidiu,

in the case of OLEDB provider for Oracle I made the experience that providing the password
in a package configuration does not work. With a ADO.Net Provider this works fine.

This is of course a disappointing limitation but I hope that this will be solved in some of the future
releases...

Fridtjof|||

This has been our experience as well; we are able to get the Oracle connection properly set from an XML configuration, and we have set all the properties in the SQL Server configuration, but the SQL configuration just does not work.

We have multiple packages in our projects, and each package pointed to the same configuration file, and when we deployed we were getting an error about the file already existing (apparently another known bug) - hence the attempt to switch to a SQL Server configuration. The ADO.Net Provider is an option, but we're using dynamic SQL so we set the Data Access Mode in the connection to "SQL Command from Variable", and I can't seem to be able to set this property using a Data Reader source. So right now we're kind of stuck. Any suggestions?

Thanks,

Mark

|||

Here are a couple examples of how I am doing this:

Oracle adonet

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

-Oracle OLEDB

<?xml version="1.0"?>

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

--SQL Server oledb

<?xml version="1.0"?>

<ConfiguredValue>Data Source=datamart;User ID=ssis agent;Password=Guessit2;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

|||Mark,

since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.

Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*

Fridtjof|||

Friedel wrote:
Mark,
since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof

It DOES work and Phillipe has shown you examples of it working. I've got exactly the same thing set up for a number of Oracle sources and they all work perfectly well. If something isn't working it isn't the configuration - maybe this is masking something else.

-Jamie

|||Jamie,

I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!

Fridtjof|||

Friedel wrote:
Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof

Ah, OK. My apologies, I thought Phillipe was talking about OLE DB. But, what I said is still true. I have this working using an OLE DB Provider with no problems at all.

-Jamie

|||Oh, you're right. I've done some tests with OLEDB for Oracle again and the results are positive.
I don't know what went wrong when I tested this some time ago...

Fridtjof

Oracle Connection String Stored in SQL Server Configuration Not Working

(The AcquireConnection method call to the connection manager "APPLPROD" failed with error code 0xC0202009. MTL_SYSTEM_ITEMS.dtsx 0 0).

Do I have to do anything in the package other than set up the initial configuration? Any clues as to why my connection isn't picking up the configuration information?
Thanks!

Mark

Hi Mark,

for security reasons, we stripe out the password from a connection string. You need to go and change the configuration to include the password.

HTH,
Ovidiu Burlacu

|||Ovidiu,

in the case of OLEDB provider for Oracle I made the experience that providing the password
in a package configuration does not work. With a ADO.Net Provider this works fine.

This is of course a disappointing limitation but I hope that this will be solved in some of the future
releases...

Fridtjof|||

Thanks,

Mark

|||

Here are a couple examples of how I am doing this:

Oracle adonet

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

-Oracle OLEDB

<?xml version="1.0"?>

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

--SQL Server oledb

<?xml version="1.0"?>

<ConfiguredValue>Data Source=datamart;User ID=ssis agent;Password=Guessit2;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

|||Mark,

Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*

Fridtjof
|||

Friedel wrote:
Mark,
since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof

-Jamie

|||Jamie,

I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!

Fridtjof
|||

Friedel wrote:
Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof

Ah, OK. My apologies, I thought Phillipe was talking about OLE DB. But, what I said is still true. I have this working using an OLE DB Provider with no problems at all.

-Jamie

|||Oh, you're right. I've done some tests with OLEDB for Oracle again and the results are positive.
I don't know what went wrong when I tested this some time ago...

Fridtjof

Oracle Connection Problems

Hello,
I'm currently in the process of deploying reports transfered from
Crystal Reports to a Reporting Services server. All of the reports
have been deployed with the exception of about a third of the reports
that make use of Oracle. I originally designed the shared data source
to use the Oracle provider rather than the OLE DB provider. However,
when I recreate the shared data source on the Reporting Services
server (with Oracle 8.1.7 installed), attempting to view any of the
Oracle reports gives me the following error:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'internal'.
(rsErrorOpeningConnection)
ORA-12640: Authentication adapter initialization failed
Clicking on the online help links generated by the error message gives
me the following information: "Cannot create a connection to data
source". I loaded up SQL Plus on the Reporting Services server to
ensure that a connection could be made to the Oracle server. Sure
enough, I had no problem retrieving data from the Oracle server.
After trying a few of the more simple troubleshooting suggestions
available on the web without success, I switched my provider from
Oracle to OLE DB using the following connection string:
Provider=MSDAORA;Data Source=NameOfTheServer
For two thirds of the reports, this switch works perfectly fine. But
for the other third, the following error is generated when attempting
to view report data:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DataSet1'.
(rsErrorExecutingCommand)
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'SP_CDIPRODBYPIT' ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
The number of parameters in the report matches the number of
parameters in the stored procedure. Likewise, the data types used in
the report (mainly String) match the data types used in the stored
procedure (mainly VARCHAR). I've tried recreating the shared data
source on my machine and viewing the same reports that are plagued by
those errors: the same error is generated on my machine. However, if I
go to the data tab of the report on my machine, select the dataset
that causes the problem, hit run, and fill in the same data as I did
for the parameters in the report, the data is returned without a
single problem.
I've searched this newsgroup and the web for a solution without
finding one and, at this point, I'm a bit lost. Does anyone have any
suggestions?
Regards,
BryanRegarding ORA-12640:
* Did you enable tracing and perform some further investigations on the
Oracle side to determine the exact error within the Oracle Authentication
Adapter?
* Did the ORA-12640 error happen for all users trying to execute reports
(i.e. users which are members of the Administrator group on the report
server machine vs. non-admins)? If it only happens for non-admin users, then
it is a file system security permission issue. Just as a test, you might
want to try giving explicit Read&Execute permissions to all files and
subdirectories in the \oracle\ora81 installation directory.
Note: SQL*Plus uses a different mode to connect to the Oracle server and
therefore would most likely not run into the same authentication problem.
Regarding OleDB error when using shared datasources:
* you might want to use two shared datasources in the report project, one is
based on the managed Oracle provider, and one that is based on the OLE DB
provider
* design the report with the shared data source for the managed Oracle
provider
* when everything works, go to the "Data" view, open the data source dialog
and just switch from the Oracle shared data source to the OleDB shared data
source; click OK
* After this step make sure that you do _not_ execute the stored procedure
from the "Data" view or hit the green "Refresh Fields" icon (this will cause
a change in the RDL structure which will later cause the OleDB problem).
* You can still change the layout, run the report in preview and publish it
on the server
Regarding OleDB error when using embedded datasources:
You could do similar steps as above, or if the report is already published
using the managed Oracle provider you would need these steps:
* EDIT the report properties in report manager for the report:
- Go to "Data sources" and replace "Oracle" with "OLE DB"
- Replace the connection string with a valid OLE DB connection string
(e.g. Provider=MSDAORA; Data Source=NameOfTheServer).
* make sure to hit the "APPLY" button on the data source page
You could also manually edit the RDL file and just change the contents of
the <ConnectionProperties> element.
Always make sure the report works fine based on the managed Oracle provider
in report designer, before switching to OleDB. Let me know if the steps
above work for you. We plan to address the underlying issue for the OleDB
error in RS SP2.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bryan" <bscriven@.inland.lehighcement.com> wrote in message
news:7b5f5ea2.0407121311.710f2d65@.posting.google.com...
> Hello,
> I'm currently in the process of deploying reports transfered from
> Crystal Reports to a Reporting Services server. All of the reports
> have been deployed with the exception of about a third of the reports
> that make use of Oracle. I originally designed the shared data source
> to use the Oracle provider rather than the OLE DB provider. However,
> when I recreate the shared data source on the Reporting Services
> server (with Oracle 8.1.7 installed), attempting to view any of the
> Oracle reports gives me the following error:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'internal'.
> (rsErrorOpeningConnection)
> ORA-12640: Authentication adapter initialization failed
> Clicking on the online help links generated by the error message gives
> me the following information: "Cannot create a connection to data
> source". I loaded up SQL Plus on the Reporting Services server to
> ensure that a connection could be made to the Oracle server. Sure
> enough, I had no problem retrieving data from the Oracle server.
> After trying a few of the more simple troubleshooting suggestions
> available on the web without success, I switched my provider from
> Oracle to OLE DB using the following connection string:
> Provider=MSDAORA;Data Source=NameOfTheServer
> For two thirds of the reports, this switch works perfectly fine. But
> for the other third, the following error is generated when attempting
> to view report data:
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'DataSet1'.
> (rsErrorExecutingCommand)
> ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
> arguments in call to 'SP_CDIPRODBYPIT' ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> The number of parameters in the report matches the number of
> parameters in the stored procedure. Likewise, the data types used in
> the report (mainly String) match the data types used in the stored
> procedure (mainly VARCHAR). I've tried recreating the shared data
> source on my machine and viewing the same reports that are plagued by
> those errors: the same error is generated on my machine. However, if I
> go to the data tab of the report on my machine, select the dataset
> that causes the problem, hit run, and fill in the same data as I did
> for the parameters in the report, the data is returned without a
> single problem.
> I've searched this newsgroup and the web for a solution without
> finding one and, at this point, I'm a bit lost. Does anyone have any
> suggestions?
> Regards,
> Bryan|||Hi,
Thanks for the response. I'm just going to reply to each individual
point.
> * Did you enable tracing and perform some further investigations on the
> Oracle side to determine the exact error within the Oracle Authentication
> Adapter?
Not yet. That's something I'd have to pass along to the Oracle person
here - if it comes to that, I'll post the results.
> * Did the ORA-12640 error happen for all users trying to execute reports
> (i.e. users which are members of the Administrator group on the report
> server machine vs. non-admins)? If it only happens for non-admin users, then
> it is a file system security permission issue. Just as a test, you might
> want to try giving explicit Read&Execute permissions to all files and
> subdirectories in the \oracle\ora81 installation directory.
> Note: SQL*Plus uses a different mode to connect to the Oracle server and
> therefore would most likely not run into the same authentication problem.
It happens for all users. Just to be sure, I tried giving everyone
read & execute permissions to the Oracle directory - no luck.
> Regarding OleDB error when using shared datasources:
> * you might want to use two shared datasources in the report project, one is
> based on the managed Oracle provider, and one that is based on the OLE DB
> provider
> * design the report with the shared data source for the managed Oracle
> provider
> * when everything works, go to the "Data" view, open the data source dialog
> and just switch from the Oracle shared data source to the OleDB shared data
> source; click OK
> * After this step make sure that you do _not_ execute the stored procedure
> from the "Data" view or hit the green "Refresh Fields" icon (this will cause
> a change in the RDL structure which will later cause the OleDB problem).
> * You can still change the layout, run the report in preview and publish it
> on the server
I'm not having any luck with that either.
> Always make sure the report works fine based on the managed Oracle provider
> in report designer, before switching to OleDB. Let me know if the steps
> above work for you. We plan to address the underlying issue for the OleDB
> error in RS SP2.
The reports were all designed on my own machine using the Oracle
provider. When I couldn't get the Oracle provider working on the
Reporting Services server, I changed the properties of the shared data
source to use OLE DB.
What's confusing me is that most of the reports work just fine with
OLE DB, parameterized and all. I managed to get one of the Oracle
reports that I was having a problem with (PLS-00306) working. I
changed the two datetime parameters in the report to strings, then I
type cast the parameter values to datetime and then back to string. I
changed the stored procedure parameters to accept varchar2 values and
then type cast the values back to date later in the procedure.
Strangely enough, the report works as intended now. I'll be attempting
the same with the remaining reports when I have time - the stored
procedures used are much more complicated than the one I fixed, so it
could take a while.
Regards,
Bryan|||Thanks for the response. I did reboot the machine after installing the
Oracle client, but I didn't grant Everyone read & execute permissions
on the Oracle folder. I tried it but unfortunately had no luck.
Regards,
Bryan
> 2 things. After you installed the Oracle client on the report server did you reboot? Also, you need to make sure that are permissions on the oracle folder. Make sure that "everyone" has read and execute permission.
> Mark
> "Bryan" wrote:
> > Hello,
> >
> > I'm currently in the process of deploying reports transfered from
> > Crystal Reports to a Reporting Services server. All of the reports
> > have been deployed with the exception of about a third of the reports
> > that make use of Oracle. I originally designed the shared data source
> > to use the Oracle provider rather than the OLE DB provider. However,
> > when I recreate the shared data source on the Reporting Services
> > server (with Oracle 8.1.7 installed), attempting to view any of the
> > Oracle reports gives me the following error:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Cannot create a connection to data source 'internal'.
> > (rsErrorOpeningConnection)
> > ORA-12640: Authentication adapter initialization failed
> >
> > Clicking on the online help links generated by the error message gives
> > me the following information: "Cannot create a connection to data
> > source". I loaded up SQL Plus on the Reporting Services server to
> > ensure that a connection could be made to the Oracle server. Sure
> > enough, I had no problem retrieving data from the Oracle server.
> >
> > After trying a few of the more simple troubleshooting suggestions
> > available on the web without success, I switched my provider from
> > Oracle to OLE DB using the following connection string:
> > Provider=MSDAORA;Data Source=NameOfTheServer
> > For two thirds of the reports, this switch works perfectly fine. But
> > for the other third, the following error is generated when attempting
> > to view report data:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'DataSet1'.
> > (rsErrorExecutingCommand)
> > ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
> > arguments in call to 'SP_CDIPRODBYPIT' ORA-06550: line 1, column 7:
> > PL/SQL: Statement ignored
> >
> > The number of parameters in the report matches the number of
> > parameters in the stored procedure. Likewise, the data types used in
> > the report (mainly String) match the data types used in the stored
> > procedure (mainly VARCHAR). I've tried recreating the shared data
> > source on my machine and viewing the same reports that are plagued by
> > those errors: the same error is generated on my machine. However, if I
> > go to the data tab of the report on my machine, select the dataset
> > that causes the problem, hit run, and fill in the same data as I did
> > for the parameters in the report, the data is returned without a
> > single problem.
> >
> > I've searched this newsgroup and the web for a solution without
> > finding one and, at this point, I'm a bit lost. Does anyone have any
> > suggestions?
> >
> > Regards,
> >
> > Bryan
> >|||SP1 contains a fix the PLS-00306 issue when using the managed Oracle
provider in report designer. However, it can still happen with the OleDB
provider, when you execute the SP from Data view or refresh fields.
Do you have SP1 installed?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bryan" <bscriven@.inland.lehighcement.com> wrote in message
news:7b5f5ea2.0407140632.745a8151@.posting.google.com...
> Hi,
> Thanks for the response. I'm just going to reply to each individual
> point.
> > * Did you enable tracing and perform some further investigations on the
> > Oracle side to determine the exact error within the Oracle
Authentication
> > Adapter?
> Not yet. That's something I'd have to pass along to the Oracle person
> here - if it comes to that, I'll post the results.
> > * Did the ORA-12640 error happen for all users trying to execute reports
> > (i.e. users which are members of the Administrator group on the report
> > server machine vs. non-admins)? If it only happens for non-admin users,
then
> > it is a file system security permission issue. Just as a test, you might
> > want to try giving explicit Read&Execute permissions to all files and
> > subdirectories in the \oracle\ora81 installation directory.
> > Note: SQL*Plus uses a different mode to connect to the Oracle server and
> > therefore would most likely not run into the same authentication
problem.
> It happens for all users. Just to be sure, I tried giving everyone
> read & execute permissions to the Oracle directory - no luck.
> > Regarding OleDB error when using shared datasources:
> > * you might want to use two shared datasources in the report project,
one is
> > based on the managed Oracle provider, and one that is based on the OLE
DB
> > provider
> > * design the report with the shared data source for the managed Oracle
> > provider
> > * when everything works, go to the "Data" view, open the data source
dialog
> > and just switch from the Oracle shared data source to the OleDB shared
data
> > source; click OK
> > * After this step make sure that you do _not_ execute the stored
procedure
> > from the "Data" view or hit the green "Refresh Fields" icon (this will
cause
> > a change in the RDL structure which will later cause the OleDB problem).
> > * You can still change the layout, run the report in preview and publish
it
> > on the server
> I'm not having any luck with that either.
> > Always make sure the report works fine based on the managed Oracle
provider
> > in report designer, before switching to OleDB. Let me know if the steps
> > above work for you. We plan to address the underlying issue for the
OleDB
> > error in RS SP2.
> The reports were all designed on my own machine using the Oracle
> provider. When I couldn't get the Oracle provider working on the
> Reporting Services server, I changed the properties of the shared data
> source to use OLE DB.
> What's confusing me is that most of the reports work just fine with
> OLE DB, parameterized and all. I managed to get one of the Oracle
> reports that I was having a problem with (PLS-00306) working. I
> changed the two datetime parameters in the report to strings, then I
> type cast the parameter values to datetime and then back to string. I
> changed the stored procedure parameters to accept varchar2 values and
> then type cast the values back to date later in the procedure.
> Strangely enough, the report works as intended now. I'll be attempting
> the same with the remaining reports when I have time - the stored
> procedures used are much more complicated than the one I fixed, so it
> could take a while.
> Regards,
> Bryan|||SP1 is installed.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message news:<uUiw0TcaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> SP1 contains a fix the PLS-00306 issue when using the managed Oracle
> provider in report designer. However, it can still happen with the OleDB
> provider, when you execute the SP from Data view or refresh fields.
> Do you have SP1 installed?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bryan" <bscriven@.inland.lehighcement.com> wrote in message
> news:7b5f5ea2.0407140632.745a8151@.posting.google.com...
> > Hi,
> >
> > Thanks for the response. I'm just going to reply to each individual
> > point.
> >
> > > * Did you enable tracing and perform some further investigations on the
> > > Oracle side to determine the exact error within the Oracle
> Authentication
> > > Adapter?
> >
> > Not yet. That's something I'd have to pass along to the Oracle person
> > here - if it comes to that, I'll post the results.
> >
> > > * Did the ORA-12640 error happen for all users trying to execute reports
> > > (i.e. users which are members of the Administrator group on the report
> > > server machine vs. non-admins)? If it only happens for non-admin users,
> then
> > > it is a file system security permission issue. Just as a test, you might
> > > want to try giving explicit Read&Execute permissions to all files and
> > > subdirectories in the \oracle\ora81 installation directory.
> > > Note: SQL*Plus uses a different mode to connect to the Oracle server and
> > > therefore would most likely not run into the same authentication
> problem.
> >
> > It happens for all users. Just to be sure, I tried giving everyone
> > read & execute permissions to the Oracle directory - no luck.
> >
> > > Regarding OleDB error when using shared datasources:
> > > * you might want to use two shared datasources in the report project,
> one is
> > > based on the managed Oracle provider, and one that is based on the OLE
> DB
> > > provider
> > > * design the report with the shared data source for the managed Oracle
> > > provider
> > > * when everything works, go to the "Data" view, open the data source
> dialog
> > > and just switch from the Oracle shared data source to the OleDB shared
> data
> > > source; click OK
> > > * After this step make sure that you do _not_ execute the stored
> procedure
> > > from the "Data" view or hit the green "Refresh Fields" icon (this will
> cause
> > > a change in the RDL structure which will later cause the OleDB problem).
> > > * You can still change the layout, run the report in preview and publish
> it
> > > on the server
> >
> > I'm not having any luck with that either.
> >
> > > Always make sure the report works fine based on the managed Oracle
> provider
> > > in report designer, before switching to OleDB. Let me know if the steps
> > > above work for you. We plan to address the underlying issue for the
> OleDB
> > > error in RS SP2.
> >
> > The reports were all designed on my own machine using the Oracle
> > provider. When I couldn't get the Oracle provider working on the
> > Reporting Services server, I changed the properties of the shared data
> > source to use OLE DB.
> >
> > What's confusing me is that most of the reports work just fine with
> > OLE DB, parameterized and all. I managed to get one of the Oracle
> > reports that I was having a problem with (PLS-00306) working. I
> > changed the two datetime parameters in the report to strings, then I
> > type cast the parameter values to datetime and then back to string. I
> > changed the stored procedure parameters to accept varchar2 values and
> > then type cast the values back to date later in the procedure.
> > Strangely enough, the report works as intended now. I'll be attempting
> > the same with the remaining reports when I have time - the stored
> > procedures used are much more complicated than the one I fixed, so it
> > could take a while.
> >
> > Regards,
> >
> > Bryan|||I just wanted to leave an update.
I managed to get my remaining Oracle reports working. In all cases,
the PLS-00306 error was caused by the date parameters. I used the
following steps to get the reports working (some of which may not be
necessary - I haven't tested the procedure thoroughly)
1) Some of the stored procedures were old and were not quite written
to Microsoft specs. Being sure to declare which parameters were going
in and which were going out, as well as moving the cursor to the last
in the list, seemed to help.
2) Some of the "date" values were being brought in as varchar and were
being converted to date in the stored procedure. I changed the
datatype on the relevant parameters to date and got rid of the to_date
calls.
3) I left the report parameters as string datatypes.
4) I changed the provider from Oracle to OLE DB and refreshed the
dataset.
5) Since refreshing the dataset erased the parameter list, I entered
all parameters manually. Under the value fields I used the CDate
function to type cast the string start date and end date values to
date values.
Additionally, the Reporting Services server I'm using seems to become
confused and switches mm/dd/yyyy dates to dd/mm/yyyy for ALL of my
Oracle reports. Type casting dates from string to date also seems to
solve that problem.
bscriven@.inland.lehighcement.com (Bryan) wrote in message news:<7b5f5ea2.0407150623.7d7dc0fa@.posting.google.com>...
> SP1 is installed.
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message news:<uUiw0TcaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> > SP1 contains a fix the PLS-00306 issue when using the managed Oracle
> > provider in report designer. However, it can still happen with the OleDB
> > provider, when you execute the SP from Data view or refresh fields.
> > Do you have SP1 installed?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Bryan" <bscriven@.inland.lehighcement.com> wrote in message
> > news:7b5f5ea2.0407140632.745a8151@.posting.google.com...
> > > Hi,
> > >
> > > Thanks for the response. I'm just going to reply to each individual
> > > point.
> > >
> > > > * Did you enable tracing and perform some further investigations on the
> > > > Oracle side to determine the exact error within the Oracle
> Authentication
> > > > Adapter?
> > >
> > > Not yet. That's something I'd have to pass along to the Oracle person
> > > here - if it comes to that, I'll post the results.
> > >
> > > > * Did the ORA-12640 error happen for all users trying to execute reports
> > > > (i.e. users which are members of the Administrator group on the report
> > > > server machine vs. non-admins)? If it only happens for non-admin users,
> then
> > > > it is a file system security permission issue. Just as a test, you might
> > > > want to try giving explicit Read&Execute permissions to all files and
> > > > subdirectories in the \oracle\ora81 installation directory.
> > > > Note: SQL*Plus uses a different mode to connect to the Oracle server and
> > > > therefore would most likely not run into the same authentication
> problem.
> > >
> > > It happens for all users. Just to be sure, I tried giving everyone
> > > read & execute permissions to the Oracle directory - no luck.
> > >
> > > > Regarding OleDB error when using shared datasources:
> > > > * you might want to use two shared datasources in the report project,
> one is
> > > > based on the managed Oracle provider, and one that is based on the OLE
> DB
> > > > provider
> > > > * design the report with the shared data source for the managed Oracle
> > > > provider
> > > > * when everything works, go to the "Data" view, open the data source
> dialog
> > > > and just switch from the Oracle shared data source to the OleDB shared
> data
> > > > source; click OK
> > > > * After this step make sure that you do _not_ execute the stored
> procedure
> > > > from the "Data" view or hit the green "Refresh Fields" icon (this will
> cause
> > > > a change in the RDL structure which will later cause the OleDB problem).
> > > > * You can still change the layout, run the report in preview and publish
> it
> > > > on the server
> > >
> > > I'm not having any luck with that either.
> > >
> > > > Always make sure the report works fine based on the managed Oracle
> provider
> > > > in report designer, before switching to OleDB. Let me know if the steps
> > > > above work for you. We plan to address the underlying issue for the
> OleDB
> > > > error in RS SP2.
> > >
> > > The reports were all designed on my own machine using the Oracle
> > > provider. When I couldn't get the Oracle provider working on the
> > > Reporting Services server, I changed the properties of the shared data
> > > source to use OLE DB.
> > >
> > > What's confusing me is that most of the reports work just fine with
> > > OLE DB, parameterized and all. I managed to get one of the Oracle
> > > reports that I was having a problem with (PLS-00306) working. I
> > > changed the two datetime parameters in the report to strings, then I
> > > type cast the parameter values to datetime and then back to string. I
> > > changed the stored procedure parameters to accept varchar2 values and
> > > then type cast the values back to date later in the procedure.
> > > Strangely enough, the report works as intended now. I'll be attempting
> > > the same with the remaining reports when I have time - the stored
> > > procedures used are much more complicated than the one I fixed, so it
> > > could take a while.
> > >
> > > Regards,
> > >
> > > Bryan

Monday, March 19, 2012

Oracle10 ODBC connection

Oracle10 ODBC connection

Monday, March 12, 2012

Oracle Source Connection Error

Friday, March 9, 2012

Oracle Problems...

oracle oledb provider not registered in local machine

Oracle OleDb Provider as Source

Oracle OleDb Provider as Source

Wednesday, March 7, 2012

Oracle linked server trouble

Oracle linked server trouble

Oracle Linked Server Connection Error After Upgrading to SQL 2005

Saturday, February 25, 2012

Oracle drivers in connection managers

Oracle data source connection problem using Report Manager

oracle connection using configuration file

Oracle Connection String Stored in SQL Server Configuration Not Working

Oracle Connection String Stored in SQL Server Configuration Not Working

Oracle Connection Problems

Oracle DBA to learn MS SQL

Blog Archive

About Me