Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Read Oracle Table and Inset into SQL Server Table
 Login/Join
 
New Learner
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, 2010Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
New Learner
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, 2010Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
New Learner
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, 2010Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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
 
Report This Post
Jean-Claude CARRIERE
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: 16 | Location: Information Builders France | Registered: May 22, 2003Report 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
 
Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders