Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Read Oracle Table and Inset into SQL Server Table
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Read Oracle Table and Inset into SQL Server Table
 Login/Join
 
Member
posted
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,
 
Posts: 3 | Registered: December 17, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 3 | Registered: December 17, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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?
 
Posts: 3 | Registered: December 17, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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 ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
I just found this Troubleshooting article.

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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
<JG>
posted
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
 
Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 11 | Location: Information Builders France | Registered: May 22, 2003Reply With QuoteReport This Post
<JG>
posted
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
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Read Oracle Table and Inset into SQL Server Table

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.