HOW TO GET THE RESULTS IN THE SAME SEQUENCE IN ORACLE 9i AND SQL SERVER 2005?

Aug 13, 2006

In ORACLE 9i, I created the table test that show the tree structure of an organizaion with the following SQL statement:

CREATE TABLE TEST(
PARFOLDERNO NUMBER(8,0),
FOLDERNO NUMBER(8,0)
)

And select the data using the following SQL Statement:
SELECT PARFOLDERNO,FOLDERNO FROM TEST

The result is:
PARFOLDERNO FOLDERNO
0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

To show the subnodes of the root node 2461, the following SQL Statement is used:

SELECT PARFOLDERNO,FOLDERNO FROM TEST START WITH FOLDERNO=2461 CONNECT BY PRIOR FOLDERNO=PARFOLDERNO

the results:

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470
2461 2462
2462 2465
2462 2466

I have created the table test with the same structure and the same data in SQL Server 2005. To show the subnodes of the root node 2461, the following SQL Statement is used:

WITH CTE_TEST(PARFOLDERNO,FOLDERNO)
AS
(
SELECT PARFOLDERNO,FOLDERNO FROM TEST WHERE FOLDERNO=2461
UNION ALL

SELECT TEST.PARFOLDERNO,TEST.FOLDERNO FROM TEST, CTE_TEST
WHERE TEST.PARFOLDERNO=CTE_TEST.FOLDERNO
)

SELECT PARFOLDERNO,FOLDERNO FROM CTE_TEST

PARFOLDERNO FOLDERNO

02461
24612463
24612462
24622465
24622466
24632469
24632470


The results are shown again in Oracle 9i and SQL Server 2005 as follwos:

Oracle 9i SQL Server 2005

PARFOLDERNO FOLDERNO PARFOLDERNO FOLDERNO
0 2461 0 2461
2461 2463 2461 2463
2463 2469 2461 2462
2463 2470 2462 2465
2461 2462 2462 2466
2462 2465 2463 2469
2462 2466 2463 2470

How can I get the result with the same sequence in SQL Server 2005?


Thanks!

View 11 Replies


ADVERTISEMENT

Oracle Sequence/Link 2 MS SQL SERVER 2005

Nov 30, 2006

hi,1. is there a statement in ms sql, what creates a sequence? cant findanything in web :-(-oracle: CREATE SEQUENCE XYZ INCREMENT BY 1 START WITH 1 NOCYCLECACHE 20;-ms sql: ???2. hwo do i create a link to another ms-sql databasethx a lot need help, urgend :-)

View 14 Replies View Related

Inserting Into Oracle Table That Has DATE_HIGH As A Partition And Need Oracle Sequence Used

May 11, 2007

Hi Everyone,



I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..



Source: MS SQL Server

Destination: Oracle 10g



The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand



OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.



Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?



If these are simple questions - my appologies, I am new to the product.



Best Regards,



Steve Collins

View 1 Replies View Related

How To Create A Sql Server Sequence Similar To Oracle?

Jun 2, 2008

In oracle, I can setup a sequence generating unique ids and query the next value (which is used as a unique identifer). I know sql server has the identity field but I need to query the next val so I can insert rows into multiple tables.
When a user submits a form, I want to take the dept info from the form (in c# asp.net 2.0) and grab the first two characters. Then query the next val from a table that holds an int.. During insert into two tables it would be something like "IT100" or "SL101". But it needs to be unique.
Is there a way to setup a table in sql server similar to a sequence where I can just query the next val (or some other way?). Remember, I cant do this as identity because I need the key being inserted in other tables during form submit.
 It seems very simple but I can't seem to find an answer online that allows me to query the next val in my code then perform the multiple inserts.
Thanks in advance for any assistance you can lend on this,
dev1aspnet

View 2 Replies View Related

Sqldatasource InsertCommand && Oracle Sequence.

Jan 10, 2006

I cannot get an Oracle sequence to work in an Sqldatasource InsertCommand:I've tried the following:
InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)And: InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (:Testing,:CUSTOMER_ID)<asp:Parameter DefaultValue="whsts_id.nextval" Name="testing" />AndInsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)
This is the classic error I get:ORA-01036: illegal variable name/numberI did review the asp.net forums before posting this.  As well, I've looked through the VWD 2005 documentation and cannot find the answer to this question.  Argg!  Any help is appreciated.   I've also worked to try and find out how to view the SQL that the Sqldatasource is generating, but I can't find the answer to this either.

View 8 Replies View Related

Need To Lookup An Oracle Sequence Value Within A Dataflow

Apr 18, 2007

I can successfully get an Oracle sequence value using an "Execute SQL Task" transform. I need to, however, be able to get the same type of value from within a dataflow. I've tried the "OLE DB Command" and the "Lookup" Transform without any luck.



With the lookup transform I run into the following problem. First of all for those of you who know about the Oracle Sequence it just returns the next value from a sequence generator using "mysequence.NEXTVAL". The lookup transform won't just return a value without providing a link. So what I attempted to do was create a derived column with a value of "0" and use the following SQL command in my lookup ( SELECT mysequence.NEXTVAL, 0 AS DUMMY from DUAL). I then linked the derived column to the DUMMY column and it appeared to work. I was able to get the sequence. However, it appears to cache the value and so for each row the sequence is the same and doesn't increment.



I then tried to unsuccessfully play around with nocache but had strange errors about "not a valid sql statement".



I then also tried the OLE DB Command with a SQL statement "SELECT mysequence.NEXTVAL FROM DUAL" but couldn't get that to work.



Does anyone have any ideas or recommendations. Please keep in mind that I HAVE to get the NEXTVAL from Oracle as this is a mandatory requirement since the source is Oracle Applications ERP and I can't make up my own sequences in SSIS or update that sequence after the fact since many other applications could be using that sequence besides me.



thanks

John

View 16 Replies View Related

How To View Results From Sequence Clustering Using DMX Query

Mar 4, 2007

somebody help me??

View 4 Replies View Related

Integration Services :: Oracle Sequence Number Within SSIS

Jul 22, 2015

For each row coming out of my data source, I would like to add the result of an Oracle query to it (select sequence.nextval from dual).

I need to acquire the sequence number before all my processes in my data flow, so I don't want to have a trigger in Oracle call nextval and do it automatically for me.

I also think getting the value of nextval inside of a variable at the beginning of the process would not work because it only increments the value once.

View 2 Replies View Related

Problem When From Sequence File Insert Into Oracle Destination Table In SSIS Package

Jun 20, 2006

Hi ,



i was used the Follwing DataFlow for my Package.using Oracle 8i



FalteFile Source -------------> Data Conversion --------------->OLEDB Destination (Oracle Data table)

using above control flow to map the Source file to Destination . When i run the SSIS Package teh Folwing Error i got

"Truncation Occur maydue to inserting data from data flow column "columnName " with a length of 50 "

regarding this Error i i understood its for happening Data Length . so that i was changed the Source Column Length Exactly Match with the The Destination table.

still i am getting this Error. pls any one give me a solution . SHould i Change the DataType also?

pls give your suggestion



Thanks & Regards

Jeyakumar.M

chennai

View 3 Replies View Related

Native Oracle SQL -&&> SQL Server 2005 CE .sdf File -&&> Using Visual Studio 2005?

May 23, 2007

I've got a table adapter that connects using an oracle data connector. In the adapter, I'm using native oracle SQL such as:

select TO_DATE(SUBSTR(TO_CHAR(weird_oracle_field),0,12),'YYYYMMDDHH24MI') as dt_added from oracle_data_table

There's also a CASE statement in there with some other data transformations.

Anyway, I want to take the results of that Oracle query and put the dataset into a SQL Server Compact Edition database - within an application that I'm creating in Visual Studio 2005.

For whatever reason, I can't seem to do anything like that in 'bulk' and there aren't any data migration tools that work with anything other than "full" SQL Server versions. My client doesn't support SQL Server, but I can deploy my app with SQL CE. I need a 'local' copy of the database (for several reasons) and just can't seem to figure out how to make this work.

I'm really going nuts. I feel like I'm soooo close when I see the data I want in the table adapter - but I can't seem to actually *move* the data over!!

Can anyone help?

thanks,

Jon

View 6 Replies View Related

Using SQL Results As Parameters For Oracle Query Through SSIS

Apr 20, 2006

Is there a way to use the results of a query as parameters of a WHERE statement in Oracle?

I have a list in a SQL table that I would like to use as criteria for a query through Oracle? Basically I have two data sources; one Oracle and the other SQL, I'm currently querying the SQL one, then using a Merge Join object in SSIS to combine the data. The SQL query has roughly 2000 rows which is fine, however the Oracle one had several million... I've tried to limit the oracle one as much as I can however its still returning far too much data...

If anyone has any suggestions on how to do this I'd greatly appreciate it. I've looked into Linked servers, however this isn't an option with my set up due to account restictions on the Oracle server. Thanks and let me know if you require any other details

View 3 Replies View Related

Oracle And SQL Server 2005

Oct 20, 2007

I have more than 8 GB Data in oracle and Everyday we have to check some data in oracle but it takes times due to lot of data. So what i was looking that data that we need can import in SQL Server database and do checking purpose. But I was looking some command that connect to oracle brings its data and import in SQL Server. For that we will fire SQL statement to oracle through asp.net and  insert into SQL Server I will fire differencial data insert covery only. is there any way or idea to do that please advice me.

View 3 Replies View Related

SQL Server 2005 Vs Oracle

Apr 6, 2006

Anyone know where I can find some good resources to help us choose betweenSQL and Oracle ( Progress Openedge as well ) . Any comments on what youwould choose ?? We are creating a new Warehouse Management System which wilmanage our very large inventory.Anyway comments suggestions welcomeThanksPaul

View 42 Replies View Related

Oracle .dmp To SQL Server 2005

Aug 28, 2007

I am trying to import an Oracle .dmp database into SQL Server 2005, what would you suggest as the fastest and easiest way to do this? DTS/SSIS doesn't have an instant solution and the script I tried to use gave me an error...


Msg 3241, Level 16, State 0, Line 1

The media family on device '<DATABASE>' is incorrectly formed. SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.


I would like a GUI based way to do this if at all possible. Thanks
-Kyle

View 8 Replies View Related

DTS From Oracle To SQL Server 2005

Mar 13, 2008

Hello guys,

I'm relatively new to DTS and I was trying to import data from the oracle database to SQL server database based on some parameter from the destination table.
i.e select * from SourceTable where SourceTable.Column1 > (select max(Column1) from DestinationTable)
This tries to search DestinationTable from the source database(Oracle)
How shoud I go to solve this problem?
Any Hint is greatly appreciated....

View 1 Replies View Related

SQL Server 2005 64-bit &&amp; Oracle

Feb 21, 2008



OK new Windows 2003 Enterprise 64-bit with MS SQL Server 2005 64-bit.. Now I am migrating the DTS packages from our olld Windows 2000 Standard with MS SQL 2000 (all 32-bit). I am not moving the packages to SSIS yet just want to move the legacy DTS jobs over... However whenever I try and connect to the Oracle DB I got ORA-12154: TNS:could not resolve the connect identifier specified.

I have the Oracle 10g 32-bit & Oracle 10g 64-bit clients installed and I can run SQL+Plus and do everything just fine. It is just in MS SQL 2005 I can not. Even in SSIS I get the ORA-12154 error. I can create an ODBC and test it and it works just fine... Has anyone here ever fixed this? Some say it is b/c of the ('s around x86 in program files and I have done their suggestion in making a ProgramFilesx86 directory and copy the visual stupid & sql directories there... still nothing. I have one of my DBA's searching Oracle for any patches but thought I would ask and see.

Thanks,

Billy S.

View 3 Replies View Related

SQL Server 2005 Versus Oracle

Jun 18, 2007

If there is someone who is intimate with both sqlserver and oracle tell me the main differences between the two?

what features are not available in either one? is one better for certain situations over others?

View 5 Replies View Related

Tutorial For DTS From Oracle To SQL Server 2005

Jan 9, 2008

Hi,
I want the tutorial for developing Integration Service for copying the database from Oracle 9i to SQL Server 2005.

If anybody knows then pls give me the steps..

Thanx in advance.

View 1 Replies View Related

Tuning On Oracle 10g And SQL Server 2005

May 4, 2006

Does anyone have some more detailed information about how Oracle and MSimplement / allow Tuning on Oracle 10g and SQL Server 2005 and thedifferences between them?Which of them, In a deep comparison about it, allow better tuning andwhy.Regards,Marcio Evangelista

View 3 Replies View Related

Replication From Oracle 10g R2 To SQL Server 2005 SP2

Oct 6, 2007

Hello All,
I created all the role and logins as described in oracleadmin.sql file and were able to query Oracle tables.
But when I try to crate publisher on Oracle server from sqL server I get the following error:
-------------------------------------------------------------------------------------------------------------------------------
TITLE: Distributor Properties
------------------------------

An error occurred applying the changes to the Distributor.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server+Management+Studio&ProdVer=9.00.3186.00&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

SQL Server could not enable 'oracle_dev' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Failed to execute the HREPL.INITPUBLISHER request to Oracle Publisher 'ORACLE_DEV'. Verify that the Oracle package code exists on the Publisher, and that the replication administrative user account has sufficient permissions.
Changed database context to 'master'.
OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "One or more errors occurred during processing of command.".
OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "ORA-06550: line 1, column 8:
PLS-00201: identifier 'HREPL.INITPUBLISHER' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
".
Error: 7215, Sev: 17, State: 1, Msg: Could not execute statement on remote server 'ORACLE_DEV'. (Microsoft SQL Server, Error: 21651)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3186&EvtSrc=MSSQLServer&EvtID=21651&LinkId=20476

--------------------------------------------------------------------------------------------------------------------------------------------------------
I searched the web and could not find any related info, where is this package can I run it manually?
Any help appreciated.

View 6 Replies View Related

Equivalence Of Oracle's LEAST In SQL Server 2005

Nov 20, 2006

Greetings, i just want to know what would be the equivalent sentence of

LEAST (IBA_MPACCIONESXREQ.FECHAFINALAXR,SYSDATE)

in SQL Server 2005. I suposse that i could just use MIN function in this case?

Thank you in advance,

Fernando

View 7 Replies View Related

Oracle's TO_CHAR, What In SQL Server 2005?

Nov 22, 2006

Greetings,

In oracle i use the following sentence TO_CHAR(Table1.DateIn, ,'mm/yyyy') within a query. I need how to replace this for using it in SQL Server. I tried to use the SQL Server CONVERT function but nothing work. Could you help me?

Thank you in advance,

Fernando

View 3 Replies View Related

Publishing From Oracle To Sql Server 2005

Sep 13, 2005

HI guys,

View 3 Replies View Related

Sql Server 2005 Sp2 And Oracle Access

Nov 21, 2006

When I try to create a model in Report Builder based on a connection to Oracle 10g database I get the following error.

ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }

Is this a bug in the CTP version or am I missing something?

View 19 Replies View Related

Migrating From Oracle To SQL Server 2005

May 30, 2006

I am migrating a database from Oracle to SQL Server 2005. I have a problem when migrating code from PL-SQL (oracle labguage) to T-SQL (sql server language). My problem is: there is pseudo-column in oracle called level that return the level from a register in a tree view of a select (a parent-child relationship). I would want to know how to translate that level column to T-SQL. Thanks!

View 3 Replies View Related

What Are The Differences Between MS SQL Server 2005 &&amp; Oracle 9i ?

Aug 5, 2006

Hi guyz,
I have basic knowledge of Sql Server 2005 and now i wanna move ahead in Oracle 9i !
So, i have 2 questions here -

a)Whats the Difference between Sql Server 2005 and Oracle 9 i ?

Note: Please keep the discussion general so that student like me can understand. I have never seen Oracle but the industry requirements suggest that Oracle is way better than than SQL Server 2005. But thats what i think

b)I am Running Windows XP SP 2 and i would like to practise Oracle 9i. So, Where i can download it for free ? I know i have checked there website but they don't mention the difference between each version. Oracle does't market their products as well as Microsoft.

Please , I am Student .. so i request you to make the discussion general .
Thank you for your time.

View 7 Replies View Related

Oracle/Unix Environments --&&> SQL Server 2005/Windows 2003 Server

May 8, 2007

I was a Oracle Developer / DBA on Unix Environments all along my career, Very recently iam starting to manage a SQL Server 2005/Windows 2003 Server setup.

Part of my new job is to automate to load Huge Data files/Flat Files (3/4 GB in size) into SQL Server 2005 DB.

Have these initial questions..
Since the files are too large to open at once... What sort of Command Line Interfaces people use on the Windows Boxes.. like doing a "wc" (Word Count) / GREP 'ng Files / Massaging Data Files one line at a time (Like using SED / AWK Commands).. Etc

Any Input / Direction is appreciated...

View 4 Replies View Related

Importing Data From Oracle 10g To Sql Server 2005 Using Linked Server

Jul 30, 2007

Hi,

I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.


Please let me know.

Thanks

View 15 Replies View Related

Sql Server 2005 Versus Oracle 10g Certification

Mar 21, 2008

hi guys,
I am joining IT industry in august,my main intrest is in database(readed some stuff of warehousing oracle 9i and sql).
However to move my career in that direction i need certification.
So here is the point,should i get MCTS(in sql server 2005) or OCP(oracle associate 10g).
My main goal is to become DBA. which of the above is more benificial.
I mean more oppurtunites.

please reply.

View 4 Replies View Related

Extracting The Oracle Data Into SQL Server 2005.

Apr 28, 2008

Hi All,

We are extracting the data from ORACLE databse into SQL Server 2005 database. Both are hosted on different servers.

For this we have created linked server on SQL Server and we using the OPENQUERY to extract the data. Its taking too much time to extract the data.

Is there any other alternate thing for OPENQUERY clause.

Please help me on this.

Thanks in advance.

Thanks,
Ramesh.

View 3 Replies View Related

Accessing Oracle Data In Sql Server 2005

Feb 20, 2008

HI,
I need some help regarding the data access.
I needs to access some data from tables which are in oracle and load it into sql server tables.
Please let me know the process for this.

Thanks in advance.

View 2 Replies View Related

Oracle TimesTen Vs SQL Server 2005/2008

Nov 15, 2007



For a high-performance and time/mission-critical application in the Telecom industry we have a project where uber-low latency is crucial. Oracle has acquired TimesTen in 2005 and I was wondering if there are performance comparisons between SQL Server 2005/2008, Oracle and other DBMS vendors?

I would also like to know from you guys what your experiences are with using SQL Server for time-critical applications.

What can we do to get the maximum out of SQL Server 2005/2008 if the hardware it will run on is not an issue. For example a 16-core machine ...

Any feedback is appreciated.

View 3 Replies View Related

Oracle 10g R2 To Sql Server 2005 - Publisher Set Up Error

Jun 7, 2007

I am receiving the following error message when trying to create a new Oracle Publisher.



Msg 21684, Level 16, State 1, Procedure sp_ORACheckAdminPrivileges, Line 136

The permissions associated with the administrator login for Oracle publisher 'NT02' are not sufficient.



We have added the following permissions manually (not through a role) in Oracle:

CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE ANY TRIGGER.
CREATE TABLE
CREATE VIEW



And we have also tried the Program FilesMicrosoft SQL Server<InstanceName>MSSQLInstalloracleadmin.sql script.



In both cases we are receiving the error message above. The only way we seem to be able to get around the error is to grant the DBA role to the replication schema user, which doesn't seem right.



Has anybody run into this problem before? How did you get around the problem?



Also is there a way to view sp_ORACheckAdminPrivileges because it's doesn't seem to be viewable anywhere in the database? This would at least let us know what it's looking for from an Oracle standpoint.



Help would be appreciated.



Thanks

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved