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 .
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;
SELECT distinct whse_zone_id, bay_id From prod_geneology WHERE whse_zone_id in ('SH','CU','SG') START WITH unit_id = 'BL7B230811' CONNECT BY PRIOR parent_id = unit_id
And in this case, 'BL7B230811' could be any 10 character string
I am getting a slightly different result set after converting my Oracle query to MS SQL. I am only getting those with invm_cost_code = 'A' and not those that are null as I do when I run the query on Oracle. Note, if I change to left outer join, still get wrogn values. But, if I change to full outer join I get those = 'A' and those that are either null or 'C' as nulls (the 'c' come through as null. Any ideas?
Oracle Query
SELECT CITM_PROPRD, CITM_INV_SEQ, CITM_ITEM_SEQ, INVM_COST_FLAG, (CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = SUBSTR('85',1,2) AND TRIM(CSUM_TERR_CODE) = TRIM('MB2') AND CSUM_PROPRD BETWEEN '200610' AND '200611' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_COMP_CODE = INVM_COMP_CODE(+) AND CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND (INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL)
Converted MS SQL Query
SELECT CITM.CITM_PROPRD, CITM.CITM_INV_SEQ, CITM.CITM_ITEM_SEQ ,INVM.INVM_COST_FLAG,(CITM.CITM_QTY * CITM.CITM_PRICE) FROM ACC_CUST_SUM as CSUM INNER JOIN ACC_CUST_ITEMS as CITM ON CSUM.CSUM_PROPRD = CITM.CITM_PROPRD AND CSUM.CSUM_INV_SEQ = CITM.CITM_INV_SEQ
RIGHT OUTER JOIN ACC_INV_MSTR as INVM ON CITM.CITM_COMP_CODE = INVM.INVM_COMP_CODE AND CITM.CITM_ITEM_CODE = INVM.INVM_ITEM_CODE AND (INVM.INVM_COST_FLAG = 'A' OR INVM.INVM_COST_FLAG IS NULL)
WHERE CSUM.CSUM_COMP_CODE = 'A' AND CSUM.CSUM_EMPL_CODE = SUBSTRING('85',1,2) AND LTRIM(RTRIM(CSUM.CSUM_TERR_CODE)) = LTRIM(RTRIM('MB2')) AND CSUM.CSUM_PROPRD BETWEEN '200610' AND '200611'
SELECT tbl1.evenID, Sum(IIf(IsNull([evpeDateCompleted]),0,1)) AS Completions, Count(tbl2.evpeID) AS Attendees, IIf(Count([evpeID])=0,"No Bookings",IIf(Count([evpeID])=Sum(IIf(IsNull([evpeDateCompleted]),0,1)),"Closed",IIf(Sum(IIf(IsNull([evpeDateCompleted]),0,1))>0,"Closing","In Progress"))) AS Status FROM tbl1 LEFT JOIN tbl2 ON tbl1.evenID=tbl2.evpeEvenID GROUP BY tbl1.evenID;
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
I recently got a new job at a new company. My previous experience had all been with Oracle DB's but the new place uses Sql Server 2k. I have a few general questions about how to do stuff in SqlServer...
1) how do I create a sequence and a trigger? 2) I know SqlServer probably doesnt have anything like Packages, so are procedures local to schemas? and if so, are schema's nestable in any way? 3) Can anyone reccomend a good book on T-SQL?
I am porting some application from AIX/C/Oracle to Windows/C#2005/MS SQL 2000. I have one query which has been giving me problems in translation from Oracle to MS SQL. This query had some decode statements which I changed to CASE statements. It also has Oracle (+) join operator, from what I have found this translates into an *= or =* depending which side the (+) is on.
Here is the query before replacing (+)
SELECT CSUM_TERR_CODE, INVM_COST_FLAG, SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)), SUM(CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = '85' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND CITM_COMP_CODE = INVM_COMP_CODE(+) AND CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND INVM_COST_FLAG = 'C' GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG UNION SELECT CSUM_TERR_CODE, (CASE INVM_COST_FLAG WHEN NULL THEN 'A'ELSE INVM_COST_FLAG END), SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)), SUM(CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = '85' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND CITM_COMP_CODE = INVM_COMP_CODE(+) AND CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND (INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL) GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG
and after replacing with *, I think I have them in the right place
SELECT CSUM_TERR_CODE, INVM_COST_FLAG, SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)), SUM(CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = '85' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND CITM_COMP_CODE =* INVM_COMP_CODE AND CITM_ITEM_CODE =* INVM_ITEM_CODE AND INVM_COST_FLAG = 'C' GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG UNION SELECT CSUM_TERR_CODE, (CASE INVM_COST_FLAG WHEN NULL THEN 'A'ELSE INVM_COST_FLAG END), SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)), SUM(CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = '85' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND CITM_COMP_CODE =* INVM_COMP_CODE AND CITM_ITEM_CODE =* INVM_ITEM_CODE AND (INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL) GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG
The Oracle run fine, but when run on MS SQL I get the following error. The table 'ACC_CUST_ITEMS' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.
Converting Oracle Queries to run on MS SQL 2000. This is query with a sub-query. If I run each query separatly I get results and if I use NOT IN sub-query I get no results. On oracle I get the correct result, but when moved to MS SQL I get nothing. Here is the original Oracle query and my converted query. Am I missing something?
Oracle SELECT INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE, INVM_DESC, INVM_UNIT_TYPE, INVM_LOC, nvl(INVM_REORDER_POINT,0), nvl(INVM_REORDER_QTY,0), nvl(INVM_COST,0), nvl(INVM_SUG_PRICE,0), INVM_DEV_CLS FROM ACC_INV_MSTR WHERE INVM_COMP_CODE||INVM_ITEM_CODE NOT IN (SELECT CITM_COMP_CODE||CITM_ITEM_CODE FROM ACC_CUST_ITEMS WHERE CITM_PROPRD BETWEEN '200706' AND '200710') AND (INVM_DEPT_CODE IN (SELECT DEPT_CODE FROM ACCSR03_DEPT) OR :ws_dept_flag = 1) ORDER BY INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE
MS SQL
SELECT INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE, INVM_DESC, INVM_UNIT_TYPE, INVM_LOC, ISNULL(INVM_REORDER_POINT,0), ISNULL(INVM_REORDER_QTY,0), ISNULL(INVM_COST,0), ISNULL(INVM_SUG_PRICE,0), INVM_DEV_CLS FROM ACC_INV_MSTR WHERE INVM_COMP_CODE + INVM_ITEM_CODE NOT IN (SELECT CITM_COMP_CODE + CITM_ITEM_CODE FROM ACC_CUST_ITEMS WHERE CITM_PROPRD BETWEEN '200706' AND '200710') AND (INVM_DEPT_CODE IN (SELECT DEPT_CODE FROM ACCSR03_DEPT) OR @ws_dept_flag = 1) ORDER BY INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE
I was wondering if anyone would be able to advise on converting oracle PL/SQL features into MSSQL.
For example i have the following sequence + trigger below but cant find any information on creating a sequence in MSSQL, is it possible?
create sequence a_SEQ start with 001 increment by 1
create or replace trigger a_TG before insert a for each row begin select (concat('D',(cast(a_SEQ.nextval as varchar(4))))) into :new.a_id from dual; end;
any links or tutorials would be great, i've got a couple of MSSQL 2005 books which do explain triggers but examples are really needed to understand the full functionality.
Hello,I am new to sql*server, but have used Oracle for years. Can anyonerecommend a good online guide for converting all the Oracle SQL I knowto TSQL?tia,Mike
Forgive me if this question is a bit too generic, if it is, feel free tojust not respond.I have a database which has been running in PostgreSQL for a number ofyears at this stage which I want to port into MS SQL server.It seems that the SQL that Postgre outputs when I do a backup is notsyntactically correct within MS-SQL server.My question is, does anyone have any documentation on how to convert adatabase from the Postgre platform to SQL server? Is it possible usingan ODBC connection to import a database structure including tabledefinitions, views etc into SQL Server?Failing this, does anyone have any suggestions on where I might start -I did attempt to go through the SQL code and modify it to suit SQLserver, but it's about 3,500 lines of code excluding the insertstatements (which themselves are also wrong) and almost every line needssomething changed when comparing SQL syntax from Postgre to MSSQL serverThanks in advance for any comments/suggestions.Engada.--Posted via a free Usenet account from http://www.teranews.com
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?
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.
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 ?
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.
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:
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.
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 ?
Hi, all...I have a sizeable database running under Oracle 9.2.0.4 under AIX5.2.0.I am faced with an impending move to a Windows environment, runningunder SQL 2000.Currently, we are heavy users of shell scripts, for exports, backups,etc.I am looking for viable scripting options under W2K/W2K3. Maybe sh/kshemulators, VB, Perl...?Scheduling of script operations is also required.Anyone been through this kind of move, who could offer some wisdom?Thanks!DW
I want to create a filtered view according to some information provides. for example I want to create in form 2 check boxes male and female and when either both or one of them is unchecked, the query filters out the unchecked content from view according to the other filtering conditions.
the exact thing I need is: a text box for string looking (in a specific column). male & female checkboxes. two datetime textboxes to specify a range to focus in.
it is very important to me, so if you misunderstood my question, please contact me.
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
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
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.
I NEED TO UPDATE THESES RECORDS BASED ON THIS QUERY
select * from property where Lis_key in(select lis_key from property where substring(func_key,1,5)='GEOSS' And substring(func_key,6,8) >=3)and Func_key = 'PV000000' and substring(Attrib_code,5,2)!=16 AND ACTIVE = 1 order by Lis_key
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.
