[SOLVED] Read Oracle Table and Inset into SQL Server Table
I am trying to read the data from an Oracle table and trying to insert the records into SQL Server table. Is there a way I can do this?This message has been edited. Last edited by: New Learner,
December 30, 2010, 03:51 PM
Waz
Yes.
Apart from an ETL tool, you will have to TABLE the contents of each table to a HOLD file, then either use SQL Insert statements, or MODIFY ro MAINTAIN, to load them into the SQLServer table.
This assumes that the new tables have been created.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 30, 2010, 03:52 PM
Waz
Just occurred to me that there is probably a tool in SQLServer that will do this anyway, take WebFOCUS out of the picure, one less step.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 30, 2010, 04:06 PM
New Learner
I want to use Webfocus to this. Here is my code: SQL ORA PREPARE SQLOUT FOR SELECT * from SPALKAR.SSP_CLARA END
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS #temp_table FORMAT SQLMSS END
-RUN SET SQLENGINE=SQLMSS SQL INSERT INTO dbo.TCRS(ANNUAL_STMT_CO_NO,DIVISION_NO) SELECT ANNUAL_STMT_CO_NO,FROM #temp_table ; END
When I run this I am getting following error message:
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3 0 HOLDING SQLMSS FILE... (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 08007 : (0) [08007] [Microsoft][SQLServer 2000 Driver for JDBC]Can't start a cl : oned connection while in manual transaction mode. (FOC1414) EXECUTE IMMEDIATE ERROR. (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 08007 : (0) [08007] [Microsoft][SQLServer 2000 Driver for JDBC]Can't start a cl : oned connection while in manual transaction mode. (FOC1414) EXECUTE IMMEDIATE ERROR.
December 30, 2010, 04:31 PM
Waz
If you put a -EXIT after the TABLE FILE HOLD END, is the first SQL error still there ?
If so, do you have access to create temporary table in MSSQL with the user being used to connect ?
Microsoft has a knowledgebase article on this here.
You may need to add a COMMIT, perhaps setting AUTOCOMMIT to ON.
You can query the settings with SQL SQLMSS ? SET
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 30, 2010, 05:01 PM
New Learner
I have put -EXIT now and error looks like this:
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3 0 HOLDING SQLMSS FILE... (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 08007 : (0) [08007] [Microsoft][SQLServer 2000 Driver for JDBC]Can't start a cl : oned connection while in manual transaction mode. (FOC1414) EXECUTE IMMEDIATE ERROR.
I do have access to create Temp table in MSSQL.I have put auto commit on as well.
Any other suggestion?
December 30, 2010, 05:42 PM
Waz
To be honest, I am not sure.
Have you done SQL tracing to see what SQL is generated ?
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS #temp_table FORMAT SQLMSS END
Don't do this.
Hold the file as a flat file, Then either generate the SQL insert statemnts using WebFOCUS or Use MODIFY to do the load.
You do not say what platform you are on, but the suggestion is that it's UNIX as you are using a JDBC connection.
If you are using a version of SQL server greater than 2000 then I would suggest that you upgrade the JDBC driver to the correct version.
As it's a JDBC connection make sure that JSCOM3 is correctly configured and working.
If you have multiple SQL servers configured make sure you are pointing to the correct 1 by issuing a ENGINE SQLMSS SET DEFAULT_CONNECTION adapter_instance