Query Oracle Table In SQL & Put In Temp Table

Hi all,

I am querying a table in oracle, the server connection to the Oracle database is determined by a criteria. Though how can I put the results from the oracle query into a temp table ?

This is the code i'm using for the query:

DECLARE @.cmd VARCHAR(500)
declare @.Year varchar(25)
set @.Year = '2006'

DECLARE @.Link VARCHAR(100)
DECLARE @.Table VARCHAR(100)

select @.Link = Server from tbl_Conn where Area='Floor'
select @.Table = Target_Table from tbl_Conn where Area='Floor'

SET @.cmd =
'
select * from OPENQUERY
(
' + @.Link + ',
''
UPDATE '+ @.Table +'
SET TARGET_VALUE = '+@.Value+'
WHERE Date = '+@.Year'
''
)
'
EXEC (@.cmd)

How do I put the executed results into a TEMP table ?

Rgds,

From your codes its seem that, your are try to update the table which is at remote side & now you want to store the results of the output of this update statmet?

if so then update only returns the "number of row updated", exaple "10 rows updated", you want to store this string ?

or

if you want to store the results return by the select query, then solution may be like this (just an example)

Create Table ##Temp

(

AC_NAME_TEMP varchar(1000)

)

DECLARE @.sql varchar(1000)

SET @.sql = 'SELECT AC_NAME FROM DM.dbo.ACCOUNTS'

Insert ##Temp EXEC (@.sql)

Select * From ##Temp

Drop Table ##Temp

Gurpreet S. Gill

|||

Sorry about the code I showed, i copied across my Update statement instead of the select statement by mistake.

Though I had tried using the code same as yours before & tried again but keep getting this error:

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

Query on Custom source Component

Hi

in the acquireconnection method Using the below statment I can get a connection Object

oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

from the connection object I can get the connectionstring from the object by calling

oledbConnection.connectionstring() property which will have all the details like DataBase, UserName & other Inofrmation but there is no password Info.

How to get the password Information, I need that information since I will use that info to make OCI calls to fetch the data from the Oracle database in m,y custome source component.

any help is much appriciated

thanks in advance.

Just a guess, but look up OleDbConnection in the framework documentation, and see this-

The .NET Framework Data Provider for OLE DB does not persist or return the password in a connection string unless you set the Persist Security Info keyword to true (not recommended). To maintain a high level of security, it is strongly recommended that you use the Integrated Security keyword with Persist Security Info set to false.

Saying that, the password property may just be write-only, they often are for security, but I cannot find documentation for the extended properties right now.

|||

so does that mean that its not possible to extract password from connection Object

|||

Hi,

we are in process of developing custom source component. At design time we are using OLEDB connection to get the metadata.

we want to use the connection information given at design time by user to initialize OracleConnection of System.Data.OracleClient.

Can anybody guide us how to acheive this?

Thanks,

Anil

|||

To be clear you must be using an ADO.Net OLE-DB connection, not the native OLE-DB connection otherwise you won't be able to make head nor tail of the acquired connection. With that in mind, why not just use an ADO.NET OracleClient directly and stop messing about with "converting" it?

|||

Hi Darren,

Thanks for your reply. but we cant go ahead by specifying the connection type as ADO.Net: OracleClient as we are using the UI for some other component as well.

Is there any other way to do the same?

Thanks,

Anil.

|||Why do you want OleDb connections one minute and Oracle the next. They are very similar, but yet still different so masking one with the other does not make sense. Having multiple connections is not a problem. What UI? If it is your own Ui creating the OleDb connection, then why can it not create Oracle as well?|||

I created a single connection, ConnectionManagerType - ADO.NET:System.Data.OleDb.OleDbConnection..., which used the "Microsoft OLE DB Provider for Oracle" underneath.

I then used the following Script Task code to display the connection string and it shows the password just fine.

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections(0)

Dim oledb As OleDb.OleDbConnection = CType(conn.AcquireConnection(Nothing), OleDb.OleDbConnection)

System.Windows.Forms.MessageBox.Show(oledb.ConnectionString)

Dts.TaskResult = Dts.Results.Success

End Sub

I cannot see the problem, password is there, is this not what you want?

|||

Hi Darren

Dim oledb As OleDb.OleDbConnection = CType(conn.AcquireConnection(Nothing), OleDb.OleDbConnection)

System.Windows.Forms.MessageBox.Show(oledb.ConnectionString)

If the User Selects the Check Box "Save Password" then the password is there in the connection string Otherwise no.

Is there any way that we can get the password if the user has not select the option to save the password.

|||

No.

The .NET Framework Data Provider for OLE DB does not persist or return the password in a connection string unless you set the Persist Security Info keyword to true (not recommended). To maintain a high level of security, it is strongly recommended that you use the Integrated Security keyword with Persist Security Info set to false.