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,

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.

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.

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.

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

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?

To be honest, I am not sure.

Have you done SQL tracing to see what SQL is generated ?

When you issue SQL SQLMSS ? SET, what is shown ?

I just found this Troubleshooting article.

SQL Server error: Can't start a cloned connection...

quote:

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

Check the article mentioned by Waz

Maybe you wnat to look at this Microsoft note : Cannot Start a Cloned Connection While in Manual Transaction Mode

I guess if you are using a JDBC driver to access SQL Server you're on a UNIX/Linux system.

Look at the adapter connection string and compare to the Microsoft article.

On the basis of the Microsoft note there are a few other settings that you can try

ENGINE SQLMSS SET TRANSACTIONS AUTOCOMMITTED
ENGINE SQLMSS SET AUTODISCONNECT ON COMMAND