Oracle ,Sqlserver Table Sychronization
Jun 3, 2008
Hi All,
I am Currently doveloping a Asp.Net application.
I Need to Use two databases one is Sql server and another one is Oracle.
My Primary database is Sqlserver . But I need to get data from two tables from the oracle database also.
I would like to create the same table in Sqlserver and Update data from oracle (synchronize) automaticaly .
Is there any way to syhcronize these table to get the uptodate data.
What is the best solution to solve this situation.
Please show me example code.
By sqlserver way or .Net way
Please Help me .
Thanks you
M.S
View 2 Replies
ADVERTISEMENT
Oct 29, 2005
need a clue about how to migrate the data from an Oracle applications 11.03 and underlying Oracle 8.05 database to navision 4.0 running sql server 2000
tia
View 1 Replies
View Related
Jul 20, 2005
Hello,I need to copy a table from an 8i oracle database to a sqlserver 2000 database.Is it possible to use the command "COPY FROM ... TO ..." ?So, what is the correct syntax ?Thanks for your helpCyril
View 1 Replies
View Related
Jun 2, 2006
we have a oracle replication to mssql2k5 with push transactional replication.
We had massive transactions in oracle and encounter spacing error
ORA-01652: unable to extend temp segment by 12137 in tablespace SYSTEM
Batch processing is in progress. The current batch consists of 17356 commands and 0 transactions. The last transaction was 0x000000000000000070f5, '0x000000000000000070f5'.
And I found many locking in replication process in oracle side and I stopped the replication is mssql2005 but no much help until kill session in oracle.
After massive rollback jobs in oracle and seems return normal in oracle publisher. I tried to re-initial the sychronization with a new snapshot.
After the snapshot created, the monitor stated that 'the initial snapshot for publication 'JHBHK-batch' is not yet available.'
The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.
Any idea? Thanks in advance!
View 4 Replies
View Related
Jul 23, 2005
I feel like the answer to this should be blinding obvious. I alsofeel like it's probably an exercise in an undergraduate databasedesign course. I'm off to google for an answer, but I figure I'dthrow it out here to see if there's a quick and dirty solution.I have in my database a table of every position we have here calledHoldings. It would be equivalent to an Inventory table in everybasic design course.There are many users who could update that table, and also many userswhose display programs wish to have correct values. Here's whatI currently do:There's a timestamp column in that file. My display apps keep trackof what the max timestamp value they've seen is. Every time theywant to do a sweep of the database, they go and "get everythingthat's newer than timestamp X", update their quantities, and display.Now, the procedure "get everything newer than timestamp X" is wheremy question lies.....It requires (of course) a scan of the entire Holdings table, sincehaving an index on a timestamp field would be (I think) suicidal.There's got to be a better way to do that.I've played around with another solution, which is to have a triggeron Holdings for insert/update, and in that trigger, I save intoanother table the PK of the Holdings which change. That "updated"table also has an identity field, which is the PK of "updated".Then, instead of keeping track of the max timestamp and scanningthe Holdings table, I keep track of the max identity in "updated",and just grab the new rows from there, which are usually only a few(and are easy to find using the PK clustered index), and then grabindividual rows from Holdings using the stored PK.As I said in the intro, I feel this should be a completely solvedproblem. I have a "correct" solution, but I'm just wondering whatthe "correct, and most efficient" solution is.Thanks,Eric.
View 15 Replies
View Related
Apr 2, 2004
Develop on both Oracle 9i and SQLServer 2000 back ends and would like to set up a new test/development server. Are there any issues with running both systems on one box?
View 3 Replies
View Related
Jul 20, 2005
Questo Statement in SQL Server funziona.In Oracle PL/SQL se lo lancio funzionaQuando lo devo far funzionare da Vb.net mi si pianta e non va avanti . Stalì a pensare.Come mai ?UPDATE proc_aziendaSET cod_fase_sign = (SELECT MAX(pfa.COD_FASE)FROM PROC_FASE_RIGA pfaWHERE (COD_GRUPPO = 'x09') AND((TIP_DATI = 'I') OR (TIP_DATI = 'S')) ANDpfa.cod_processo = '02')WHERE cod_gruppo = 'x09' ANDcod_processo = '02'C'è un'altro Statement:UPDATE proc_aziendaSET cod_fase_sign = FaseFROM (SELECT ep.cod_processo,app.cod_azienda,MAX(cast(ep.cod_fase as int)) as FaseFROM esp_proc_prospetti ep,APP_PROSP_AZ_8377 app,(SELECT pa.cod_processo,pa.cod_azienda,pa.cod_fase_signFROM proc_azienda paWHERE pa.cod_gruppo = 'x09' andpa.cod_processo = '05' andpa.cod_fase_sign is nullGROUP BY pa.cod_processo,pa.cod_azienda,pa.cod_fase_sign) pnullWHERE ep.cod_gruppo = 'x09' andep.cod_processo = app.cod_processo andep.cod_processo = pnull.cod_processo andep.cod_prospetto = app.cod_prospettoGROUP BY ep.COD_PROCESSO,app.COD_AZIENDA) FaseAzienda ,proc_azienda paWHERE pa.cod_gruppo = 'x09' andpa.cod_processo = FaseAzienda.cod_processo andpa.cod_azienda = FaseAzienda.cod_aziendaQuesta sintassi sembra regolare per SQL Server ma non per ORACLE.Come deve essere ... ?Grazie milleS.
View 1 Replies
View Related
May 3, 2002
DO you know how to convert the following SQLSERVER to ORACLE syntax:
SELECT *
FROM (ITEM_DATA RIGHT JOIN ITEMS ON ITEM_GUID = ITEM_GUID)
LEFT JOIN
IN_SHOP_HISTORY.ITEM_DATA_GUID = ITEM_DATA_GUID
ORACLE does not support JOINs
Thanks in advance,
Kathy
View 1 Replies
View Related
Nov 11, 2001
Hello,
How can this query be written in SQLSERVER 7 as it is written in Oracle.
SELECT rownum, column1 from XYZ;
This table returns two columns, first being the auto-generated sequence and second has the values in column1 of XYZ table.
I need to do similar in SQLserver 7 and I am new to it.
Please treat it as urgent..
Thanx
View 2 Replies
View Related
Mar 5, 2007
Hi,
In Oracle we have a datatype called 'ROWID' - Oracle uses this datatype to store the address (rowid) of every row in the database. Do we have any equivalent datatype in SQLServer similar to this ?
Regards,
Sn
View 11 Replies
View Related
Oct 8, 2007
Hi
I am a Oracle DBA who is going to embark upon a
oppurtunity to migrate Oracle to sqlserver.
Can somebody give me tips for
Learning sqlserver2k5 for a Oracle DBA Books
Or Beginner DBA/Development Books for sql2k5
How good is the migration assistant from m$$ for
migrating from oracle to sql2k5
Does it migrate data as well as procedural code ?
Do you anything for me to watch out for best parctices
migration guides
You opinion would be higly appreciated
regards
db2hrishy
View 2 Replies
View Related
Aug 14, 2007
select pippofrom(select person.name as pippofrom person)This query run in oracle....in sqlserver no!what can i do!?thank's
View 1 Replies
View Related
Jul 20, 2005
Hello,Has anyone a small tool or somekind of document which could help meto convert Oracle SQL scripts to SQL Server?Scripts are not very Oracle specified.Thanks,Below is a Script that I would e.g convert to MS SQLServer:SET SCAN ONPROMPT Enter the password and TNS name.PROMPT Enter the oracle SID for TNS name if you are running a local database.CONNECT system/&systempassword@&&tnsname-- Drop the user and all other related objects.DROP USER webstore CASCADE;-- Creating the schemaCREATE USER webstore IDENTIFIED BY welcome;-- Grant the permissions to the user.GRANT RESOURCE, CONNECT TO webstore;ALTER USER webstore DEFAULT TABLESPACE usersQUOTA UNLIMITED ON users;ALTER USER webstore TEMPORARY TABLESPACE temp;CONNECT webstore/welcome@&&tnsnamePROMPT Creating Tables-- Create the category table which contains the data for the categories.CREATE TABLE category (id NUMBER(10) CONSTRAINT pk_vsm_country PRIMARY KEY,name VARCHAR2(20) NOT NULL);-- Create category attributes table which has attributes for the categories in-- the category table.CREATE TABLE category_attributes (category_id NUMBER(10) NOT NULL,label VARCHAR2(20) NOT NULL,CONSTRAINT pk_vsm_cat_attr PRIMARY KEY(category_id,label),CONSTRAINT rk_vsm_catattr_cat FOREIGN KEY(category_id) REFERENCEScategory(id) ON DELETE CASCADE);-- Create country table to hold the country names.CREATE TABLE country (id NUMBER(4) CONSTRAINT pk_country PRIMARY KEY,country_name VARCHAR2(50) CONSTRAINT uniq_country UNIQUE);-- Create users table to hold user details.CREATE TABLE users (user_name VARCHAR2(20) CONSTRAINT pk_users PRIMARY KEY,first_name VARCHAR2(20) NOT NULL,last_name VARCHAR2(20),e_mail VARCHAR2(50) NOT NULL,address VARCHAR2(200) NOT NULL,city VARCHAR2(20) NOT NULL,state VARCHAR2(20) NOT NULL,country NUMBER(4) NOT NULL ,zip NUMBER(8) NOT NULL,phone VARCHAR2(20) NOT NULL,role VARCHAR2(10) NOT NULL,password VARCHAR2(200) NOT NULL,card_provider VARCHAR2(30),card_number VARCHAR2(200),card_expiry_date DATE ,CONSTRAINT rk_usr_cntry FOREIGN KEY(country) REFERENCEScountry(id) ON DELETE CASCADE);-- Create shops master table.CREATE TABLE shops (id NUMBER(10) CONSTRAINT pk_shops PRIMARY KEY,shop_name VARCHAR2(50) NOT NULL,user_name VARCHAR2(20) NOT NULL,description VARCHAR2(4000),category_id NUMBER(10) NOT NULL,reg_date DATE NOT NULL,status VARCHAR2(20) NOT NULL,CONSTRAINT chk_shops CHECK( status IN ('Approved', 'ApprovalPending','Rejected','Discontinued') ),CONSTRAINT rk_shop_cat FOREIGN KEY (category_id) REFERENCES category(id)ON DELETE CASCADE,CONSTRAINT rk_shop_user FOREIGN KEY(user_name) REFERENCES users(user_name)ON DELETE CASCADE);-- Create sub_category table which has the sub categories listed for each shop.CREATE TABLE sub_category (id NUMBER(10) CONSTRAINT pk_subcat PRIMARY KEY,category_id NUMBER(10) NOT NULL,shop_id NUMBER(10) NOT NULL,name VARCHAR2(20) NOT NULL,CONSTRAINT rk_subcat_cat FOREIGN KEY(category_id) REFERENCES category(id)ON DELETE CASCADE,CONSTRAINT rk_subcat_shop FOREIGN KEY(shop_id) REFERENCES shops(id)ON DELETE CASCADE);-- Create item master table.CREATE TABLE item (id NUMBER(10) CONSTRAINT PK_VSM_ITEM PRIMARY KEY,name VARCHAR2(50) NOT NULL,category_id NUMBER(10) NOT NULL,shop_id NUMBER(10) NOT NULL,description VARCHAR2(4000),unit_price NUMBER(15,2) NOT NULL,image VARCHAR2(50),CONSTRAINT rk_item_subcat FOREIGN KEY (category_id ) REFERENCESsub_category(ID) ON DELETE CASCADE ,CONSTRAINT rk_item_shop FOREIGN KEY(shop_id) REFERENCES shops(id)ON DELETE CASCADE);-- Create item_attributes table which has the item attributes for the itemsCREATE TABLE item_attributes (item_id NUMBER(10) NOT NULL,label VARCHAR2(20) NOT NULL,description VARCHAR2(4000),CONSTRAINT pk_item_attr PRIMARY KEY(item_id , label),CONSTRAINT rk_itemattr_item FOREIGN KEY(item_id) REFERENCES item(id)ON DELETE CASCADE);-- Creat inventory table which maps the quantitly against the item.CREATE TABLE inventory (item_id NUMBER(10) CONSTRAINT pk_inventory PRIMARY KEY,quantity NUMBER(4) NOT NULL,CONSTRAINT RK_INVNTRY_ITEM FOREIGN KEY(item_id) REFERENCES item(id)ON DELETE CASCADE);-- Create orders master table which stores all the completed orders.CREATE TABLE orders (id NUMBER(10) CONSTRAINT pk_orders PRIMARY KEY,order_date DATE NOT NULL,user_name VARCHAR2(20) NOT NULL,shop_id NUMBER(10) NOT NULL,ship_to_address VARCHAR2(100),city VARCHAR2(20),state VARCHAR2(20),country NUMBER(4),zip NUMBER(8),phone VARCHAR2(20),CONSTRAINT rk_ordr_usr FOREIGN KEY(user_name) REFERENCES users (user_name)ON DELETE CASCADE,CONSTRAINT rk_ordr_shop FOREIGN KEY(shop_id) REFERENCES shops(id)ON DELETE CASCADE);-- Create order_items table to store the detailed ordersCREATE TABLE order_items (order_id NUMBER(10) NOT NULL,item_id NUMBER(10) NOT NULL,quantity NUMBER(4) NOT NULL,unit_price NUMBER(15,2) NOT NULL,status VARCHAR2(20) NOT NULL,CONSTRAINT chk_oi check ( Status in ('Pending','Shipped')),CONSTRAINT rk_oi_ordr FOREIGN KEY(order_id) REFERENCES orders(id)ON DELETE CASCADE ,CONSTRAINT rk_oi_item FOREIGN KEY(item_id) REFERENCES item(id)ON DELETE CASCADE);-- Create guest_book table to store all the guest_book entriesCREATE TABLE guest_book (id NUMBER(10) CONSTRAINT pk_guest_book PRIMARY KEY,user_name VARCHAR2(50) NOT NULL,email_id VARCHAR2(50),rating NUMBER(1),comment_date DATE NOT NULL,comments VARCHAR2(4000));-- create sequences.CREATE SEQUENCE category_seq MAXVALUE 9999999999;CREATE SEQUENCE shops_seq MAXVALUE 9999999999;CREATE SEQUENCE sub_category_seq MAXVALUE 9999999999;CREATE SEQUENCE item_seq MAXVALUE 9999999999;CREATE SEQUENCE inventory_seq MAXVALUE 9999999999;CREATE SEQUENCE orders_seq MAXVALUE 9999999999;CREATE SEQUENCE guest_book_seq MAXVALUE 9999999999;
View 1 Replies
View Related
Nov 30, 2004
Hi,
Could anyone tell me what is the MSSQLServer equivalent of the following Oracle query?.
SELECT v1.cat, v1.gnr, TA1.desccde, v1.type, v1.cde
FROM view1 v1, table1 TA,
table1 TA1, table1 TA2, table1 TA3
WHERE
TA.txtcde (+) = TRIM(v1.cat)
ANDTA1.txtcde (+) = TRIM(v1.gnr)
ANDTA2.txtcde (+) = v1.cde
ANDTA3.txtcde (+) = TRIM(v1.type)
Thanks,
Gopi.
Follow your DREAMS...
View 3 Replies
View Related
Jul 23, 2005
Hello,I would like to know if the equivalent Oracle rownum exist inSQLServer. Here is a sample SQL code to explain what I want to do :selectjobs.name,jobs.job_id,jobs.description,hist.message,hist.step_name,hist.step_id,hist.run_status,hist.run_date,hist.run_time,hist.run_durationfrommsdb.dbo.sysjobs jobs,msdb.dbo.sysjobhistory histwherejobs.job_id=hist.job_idand hist.job_id='E71CCB97-81C3-46E2-83FA-BFFCB66B47F8'order byrun_date, run_timeI just want the first or second row returned by this query. In Oracle Ican simply add rownum=1 or rownum=2 in the where clause to obtain thedesired answer. I don't know how to do in SQLServer.Thank in advance,Pierig.
View 4 Replies
View Related
May 23, 2006
I am an oracle savvy developer looking to switch to SS. Know of anygood books that compare/contrast the two?Thanks
View 3 Replies
View Related
Jan 23, 2007
Hi
I am trying to switch between oracle and sqlserver databases to read the source data. I have used a parameter file to specify the connection parameters. For SQLserver the connection looks like in the file as below:
<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="abvsh" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=dl;Initial Catalog=PM_DW;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>dl</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration></DTSConfiguration>
the pacakge runs fine. But when i change the connection to Oracle, it gives me error.
The config file looks like when i put oracle connection is as follows:
<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="I2vshrivas" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=pm62;User ID=pcm_62;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>i2pm62</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue>pcm_62</ConfiguredValue></Configuration></DTSConfiguration>
The error which i get when running from oracle source is this:
Information: 0x40016041 at Package: The package is attempting to configure from the XML file "C:oraTOsql-DataTXIntegration Services Project1Integration Services Project1Integration Services Project1 estConfig.dtsConfig".
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package, Connection manager "dl.PM_DW": An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: The AcquireConnection method call to the connection manager "dl.PM_DW" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
The program '[7264] Package.dtsx: DTS' has exited with code 0 (0x0).
Please let me know if you guys have any suggestion in what i am doing wrong.
Thanks,
Vipul
View 3 Replies
View Related
Jan 23, 2007
Hi All,
Is there a way in SSIS to change the database connection to sqlserver or oracle or db2 without choosing the connection manager in the source.
For e.g. I ran a package which is reading data from sqlserver and later i want to connect to oracle to read the data but without changing the connection manager in the source.
Basically, i want to find out from you gurus is about a way to switch between databases without changing the connection managers. OR the only way is to make different sets of packages for oracle / db2 and sqlserver sources ?
Thanks,
Vipul
View 2 Replies
View Related
Jan 14, 2001
Hi ,
I have a question for all , please help me out.
the question is : i have a query in Oracle like this
select count(*), to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy') from ISL_USERS
group by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy')
order by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy');
i want to convert this into SQLserver but in SQL server equlent function for TO_CHAR is Datename ..when i have given this i am getting error . please give me the tip on this issue .
thanks
shekhar
--------------------------------------------------------------------------------
|
View 1 Replies
View Related
Jul 20, 2005
Hey all,Recently we had a small re-org which combined DBA teams,specifically Oracle and SqlServer. Just wondering if anyone hasdocumentation/presentations, etc that show's how to admin a SQLServerenvironment from an Oracle DBA perspective?I guess, something that map's each DB's concepts to each other. Asan Oracle DBA how to troubleshoot/support the environment, etc....Thanks!Dave
View 4 Replies
View Related
Jul 20, 2005
The UPDATE table FROM syntax is not supported by Oracle.I am looking for a syntax that is understood by both Oracle and SqlServer.Example:Table1:id name city city_id1 john newyork null2 peter london null3 hans newyork nullTable2:id city23 london24 paris25 newyorkUPDATE table1SET city_id = table2.idFROM table1, table2WHERE table1.city = Table2.cityIf possible I do not want to have two different statements for Oracle andSqlServerPlease do not tell me that these tables are not normalized, it's just anexample!Thanks for any hints.Jan van Veldhuizen
View 8 Replies
View Related
Nov 8, 2007
OK so there is some data in an Oracle DB that I have to summarize based on grouping info stored in a SqlServer DB. How can I import the Oracle data into a SqlServer temp table using SqlServer Express? Thanks.
View 2 Replies
View Related
Nov 9, 2005
I have to copy a large (3000) amount of different tables from a Oracle machine into an
SQLServer machine.
I am able to do this using a (VB) script.
I use now several methods:
1) INSERT INTO TABLE1 SELECT * FROM SID1..DB.TABLE1 (SID1 is a linked server)
2) INSERT INTO TABLE1 SELECT * FROM OPENQUERY(SID1,'SELECT * FROM DB.TABLE1')
3) Also used OPENROWSET method (similar to 2)
For small tables this is fine, however for BIG tables (15M Rows/150Cols) the methods above are too slow.
If I compare the same copy action with a simple DTS, the DTS is 3 times faster.
Also, the DTS seems to bulk copy the data directly into the desired database while the
mentioned methods first fill the tempdb, then the transaction log of the desired database and
then finally the desired table (need very much extra space on your filesystem).
The total size of data is about 300GB.
Can anyone supply me with a simple example how to copy data from an Oracle table into a
SQLServer table in script (or SQL) that is as fast as the DTS and not filling my logfiles??
I read the bcp (which I use for import/export files) and bulk insert commands, but
I do not understand how to use them in this question.
View 14 Replies
View Related
Feb 15, 2007
I'd like to have Oracle's empty string behavior in SQLServer 2k5. Oracle treats an empty string as NULL's.
In PL/SQL can do:
SELECT * FROM TABLE WHERE TABLE.FIELD IS NULL
... and it'd return rows containing NULL's as well as empty strings.
Can this be done? I couldn't find a setting for it.
Thanx
Peter
View 13 Replies
View Related
Mar 12, 2008
Hello everybody.
Recently my boss decide migrate reports in Oracle Discoverer to SqlServer 2005 using Reporting Services..because SqlServer is less expensive than Oracle. ..
Well I don't know exactly How do I began? because it's new for me.
Any has experienced? Can you give any recomendations to migration?
Thanks.
View 1 Replies
View Related
Jun 18, 2007
This is what I did:
The instructions for changing the registry entries per previous MSDN postings were:
For Windows 2000
Oracle
8.1 [HKEY_LOCAL_MACHINESOFTWARE
MicrosoftMSDTCMTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
Changed the registry entries on my WindowsXP machine:
entry name old value new values
"OracleXaLib" xa80.dll oraclient10.dll
"OracleSqlLib" sqllib80.dll orasql10.dll
"OracleOciLib" oci.dll oci.dll
Then created the Linkserver definition:
EXEC sp_addlinkedserver 'ORA10G', 'ORACLE', 'MSDAORA', 'ORA10G'
Command(s) completed successfully.
EXEC sp_addlinkedsrvlogin 'ORA10G', 'FALSE',NULL, 'scott', 'tiger'
Command(s) completed successfully.
Note:
scott/tiger is given as an example here; used my real userid/pw instead. BTW, I am able to connect
to the Oracle database using the userid/pw from the same host that is running the Sqlserver db.So, the
Oracle client software is installed and is working correctly.
Then attempted to query a table on the Oracle database:
select count(*) from ORA10G..<schemaname>.<tablename>
Got the following error message(s)
OLE DB provider "MSDAORA" for linked server "ORA10G" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "ORA10G".
All the previous discussions and suggested solutions are all for SqlServer 2000 and Oracle8. But I did not see much discussions on SqlServer2005 and Oracle10g. Any help to resolve this problem will be appreciated.
Thank you.
View 15 Replies
View Related
Feb 26, 2008
Hi pals,
I have a small problem in Datflow task.
Basically i am having two tables(similar to my current requirement). One in sql server 2005 and another in ORacle
SQL Table
create table t1
( c1 numeric(6),
c2 numeric(6),
c3 varchar(50),
year numeric(4),
district varchar(10)
)
Oracle Table
create table app_t1
( c1 number(6),
c2 number(6),
c3 varchar2(50),
year number(4),
district varchar2(10),
constraint cpk primary key (c1,year,district)
)
I am using 2 global variable for passing values to c4 and c5 columns in oracle table.
The variable names are g_year and g_district.
i am using a dataflow task to move data from sql server 2005 to oracle 9i database
I am able to load the data from sql server to oracle.
Till here it is fine.
But the requirement is if i get same data i.e same dist and same year then we need to delete the data
in oracle table as
"delete from app_t1 where year = ? and district = ?"
Immediately i need to issue a commit statement.
and thereafter i need to load data for that particular district and year.
To accomplish this requirement i am using ,
An Execute SQL Task to Delete the data from ORacle
An Execute SQL Task to COMMIT the transaction
Finally i am loading data from sqlserver to oracle using a dataflow task.
But i am getting an error as
"Value violated the integrity constraints for a column or table.".
I think the deletion operation is not getting committed.
So what i did was i connected to oracle and deleted the data using above DELETE statement
and again come back to SSIS package and ran the package once again, this time it is successfull.
Why is it so. Any thoughts on this?
Any help would be greatly appreciated.
Thanks!
View 13 Replies
View Related
Jan 14, 2008
Hi,
I am new to SQL Server 2005 SSIS Packages. I want to transfer data from multiple tables from sql server to oracle database. I cannot use export wizard as it creates new tables in the destination (oracle) DB. I already have tables created in the destination DB. When I created an SSIS package, it allowed me create package to tranfer data for only for one table from source to destination. I have created DTS packages in 2000, where you have source db and destination db and just add links for multiple tables. Is there a way I can do it in SSIS. Please let me know.
View 3 Replies
View Related
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
Apr 23, 2007
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
View 5 Replies
View Related
Nov 6, 2004
Hi,
i am trying to convert the following oracle sql,
SELECT TO_CHAR(SYSDATE,'DDMMYYYYHHMMSS')
(oraclequery)
into a MSSQLServer sql query.
Could you help me to get the equivalent of the above query in sqlserver.
PS: i tried using the following query, but i cannot get the month equivalent as 01/02/03/04 instead i get january/febrauary/march, etc
SELECT
(DATENAME(d, GETDATE())+
DATENAME(m, GETDATE())+
DATENAME(yyyy, GETDATE())+
DATENAME(hh, GETDATE())+
DATENAME(mi, GETDATE())+
DATENAME(ss, GETDATE()))
AS "Month Name"
(ms sqlserver)
The above query gives output as "6November2004174837"
what i need as result is "06112004174837"
Thanks,
Gopi.
Follow your DREAMS...
View 3 Replies
View Related
Nov 7, 2007
OK so there is some data in an Oracle DB that I need to query and analyze. Unfortunately, the criteria for selecting/grouping the data is stored in a MS Sql Server DB. This cannot be changed.
SqlServerGroup Name ID# Item ConditionAAA123 1 a 1AAA123 2 a 1AAA123 3 a 1AAA123 4 a 2AAA123 5 a 2AAA123 1 b 3AAA123 2 b 4AAA123 3 b 3AAA123 4 b 4AAA123 5 b 3BBB123 1 a 1BBB123 2 a 1BBB123 3 a 2BBB123 4 a 2BBB123 5 a 2
OracleGroup Name ID# ValueAAA123 1 50%AAA123 2 55%AAA123 3 60%AAA123 4 80%AAA123 5 70%BBB123 1 35%BBB123 2 45%BBB123 3 50%BBB123 4 50%BBB123 5 80%
I need to be able to get this:Group Name Item Condition ValueAAA123 a 1 55%AAA123 a 2 75%AAA123 b 3 60%AAA123 b 4 67.5%BBB123 a 1 40%BBB123 a 2 60%
Any idea how I can get the data from these two DBs to talk to each other? Thanks.
View 6 Replies
View Related
Jun 11, 2007
I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.
I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:
Imports System.Data.SqlClient
Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.
any idea?
thanks
View 16 Replies
View Related
