Hi all, real basic question: I can't seem to find a basic reference that tells me the syntax and allowable values of parameters in common functions such as Format or CDate when editing a Field Expression in Visual Studio (Report Definition Language?). Where would I start?
For example, I've discovered that the Format function "Returns a string formatted according to instructions contained in a format String expression.", and the string expression can have values like "D" and "d" which produce different results, but where would I find out what the allowable string expressions are and their meaning?
I have also been guessing at the syntax of the CDate command with no luck. I need a reference that tells me what the different function parameters mean.
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
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.
Hi, I've been tasked with investigating how we can migrate from oracle to sql server. I have successfully converted a typical schema with 97% success using SSMA and have a query regarding ADO and oracle syntax.
I have heard that the SQL server provider can interpret oracle syntax into its own syntax, but i am unable to find any reference/examples on the web. Is this possible? and if so could any kind soul please post some links.
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,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;
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 ?
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 .
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
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 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.
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?
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.
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.
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.
