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,
Query Oracle Table In SQL & Put In Temp Table
sa sa
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].
Rups11
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 ##TempDrop
Table ##TempGurpreet S. Gill