Connection To Oracle / Sqlserver / Db2 Databases
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
ADVERTISEMENT
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
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
May 2, 2008
I'm trying to implement a configurable way of executing a group of SQL statements using either an Oracle or SQL database as the source for the data.
I'm currently building a connection string with a Script Task and then assigning it to a package variable so that it can be used by a connection manager but this only works with a SQL OLEDB connection.
I'm holding all the connection details in a database table, hence the need for the scripting but would it be possible to extend the task to reuse a single connection manager by changing it's properties?
View 7 Replies
View Related
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
Feb 22, 2006
Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
View 17 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
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
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
Jul 17, 2002
We are looking at consolidating our servers. We would like to have a database server, which housing our databases. However, we have some SQL 6.5 databases and Oracle 8.0.1 databases. Does anyone know if it is possible to have both on one server? If not, then it sounds like we need two database servers. Thank you.
View 1 Replies
View Related
Jul 23, 2005
Hi, does anybody know a nice tool to automatically compare and showonly the structural differences between two databases, this is, thedifferences in the columns of a table, constrains, data type, etc.ThanksJ.A.
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
Jul 20, 2005
Hello,I am starting to examine using SQLServer instead of Oracle. I wentthrough the SQLServer import utility to copy tables from Oracle intoSQLServer. I then checked the size of the database.I then started to specify indices for the tables. I did not see thedatabase file size grow all that much. I kept on using the shrinkcommand to make sure that wasted space was removed. I even made surethat minimal free space was allowed.I was rather expecting (from experience with Oracle) that there wouldbe a large growth in the database when addind indices. So what did Imiss? Are the indices stored elsewhere and I missed them? DoesSQLServer handle indices differently so they dont' bloat the databasesize?The size of our data in SQLServer is a key factor in whether we movefrom Oracle, so I need to under how indices effect storage size.Thanks for any help.Bart TorbertJoin Bytes!
View 7 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
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
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
Jul 20, 2005
I also posted similarly in mailing.database.mysqlI have created a rather large (60 table database) website dealio in PHP thatworks with MS SQL Server, Oracle, MySQL and presumably any other data base Iadd tothe wrapper functions that I created. All the development was done inOracle, but now its time to create that large datastructure again inMySQL -- now I've built this all in notepad for Oracle (twice) and built itin SQL Server using the SQL Server import tools (pretty nice) -- is thereanything that I can do that to get this datastructure and maybe even thedata into MySQL using Oracle/MS SQL Server tools ?James
View 2 Replies
View Related
Sep 12, 2007
It is posible with Entreprise Library to use the SAME QUERY, by example,
"Select isnull(Val,33) from calculs where Col1 is not null" (specific for Sql Server)
to select data from Oracle too ? or the only way is to use specific stored procedures (queries)
wtitten in 2 ways : for Oracle and for Sql Server?
Thanks
View 1 Replies
View Related
