InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 02:38:34 GMT20http://www.sqlservercentral.com/Forums/Topic1453446-1044-1.aspxHi,first of all, i'm biginner in Bi Consulting with Microsoft tools.I want to compare two databases : Oracle Database and Sql Databaseboth of databases have the same data but not the same naming of Table or Columns for sure !I thought to create a linked Database in SSMS, but i'm not administrator and i can't modify the Production Database May be i can use PowerPivot in Excel but I don't know how ?so any idea please ? Thank youBest regards.SalmitaThu, 16 May 2013 05:39:01 GMTsalmitahttp://www.sqlservercentral.com/Forums/Topic1453012-1044-1.aspxI'm trying to get a step-by-step approach to installing Oracle 11g Client in a sql server 2008r2 Active/Passive Clustered Environment. I have found some articles stating that you have to intall both the Oracle 11g 32 and 64 bit.Any help would be greatly appreciated.Thanks in advance.Wed, 15 May 2013 05:02:42 GMTericwenger1http://www.sqlservercentral.com/Forums/Topic1449795-1044-1.aspxI have a number of jobs that fail because of a linked server message.[code="other"]Executed as user: Domain\User. Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "Oracle". [SQLSTATE 42000] (Error 7303). The step failed.[/code]Sometimes these jobs complete successfully, though they fail the majority of the time. Any ideas?Mon, 06 May 2013 10:36:55 GMTjshurakhttp://www.sqlservercentral.com/Forums/Topic1441790-1044-1.aspxI get Data from some Oracles Database. We own the Data but the system was designed by an outside firm.They took away out permissions to browse the tables in a tool such as SQLDeveloper.I can get a list of table by executing the following command:[code="sql"]select dbms_metadata.get_ddl('TABLE','TRANSACTION_FACT','RDB_DWH') from dual; [/code]I can get a tables column definitions by executing the following:[code="sql"]DESCRIBE RDB_DWH.TRANSACTION_FACT[/code]But I need the Primary Key, Indexes as well, Unique Constraints, Foreign Key Constraint, etc. I execute the following command in an attempt to Column information for a specific table.So I try the dbms_metadata.get_ddl Package and I get the following error. RDB_DWH is the owner name and TRANSACTION_FACT is the table name.[code="sql"]select dbms_metadata.get_ddl('TABLE','TRANSACTION_FACT','RDB_DWH') from dual; [/code]ORA-31603: object "TRANSACTION_FACT" of type TABLE not found in schema "RDB_DWH"ORA-06512: at "SYS.DBMS_METADATA", line 4018ORA-06512: at "SYS.DBMS_METADATA", line 5843ORA-06512: at line 131603. 00000 - "object \"%s\" of type %s not found in schema \"%s\""*Cause: The specified object was not found in the database.*Action: Correct the object specification and try the call again.But even if that worked I would not get the PK, Indexes as well as all the constraints.I'm trying to get permissions. Currently no one in the company has permissions to brows tables, etc. SUbmitted a request but in the even that that does not happen I need to do it in SQL.Any help would be greatly appreciated.:-)Fri, 12 Apr 2013 10:16:39 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1437228-1044-1.aspxI maintain BI that needs to import the data from Oracle every day. I use the import wizard to create package using .Net provider for Oracle. I've over 1400 tables, so I just choose all of them and let the wizard create the package for me, which I'll put it inside the job later on. It takes over 2 hours to create the package. Now the problem Oracle team do the changes in their database all the time, which lead to failure of my package. It happens almost every week or two, then I've to create the package all over again. It's happening almost 2 years. I've tried to ask them notify me their change logs several times, but no luck.The logic of my import is I do drop all the tables every day, and package creates tables and imports the data. Is there any solution that will work in this case that I'll do it once and works forever. All I want is latest data with structure from Oracle to SQL Server. I don't know any people from Oracle team, all i have is their database name and one user id to access the Database.Sat, 30 Mar 2013 13:09:34 GMTDehqon D.http://www.sqlservercentral.com/Forums/Topic1438512-1044-1.aspxI posted on forums.oracle a couple hours ago but haven't heard anything yet. Figured I'd try this board as well.I have a query that I pulled from a Hyperion report and it is going to be used to create a report in SSRS. The query that I exported from the report does not include how the data was massaged in the report. There are several columns and two of them are SUBCASE_NUMBER and DURATION. The report sums the duration for each subcase number. In some cases, there is only one subcase number with one duration time, in other cases there are several of the same subcase numbers with varying duration times. When I run the following query, I get the results I want.[code="sql"]SELECT AL21.SUBCASE_NUMBER, SUM(AL31.DURATION/60) TOTAL_DURATIONFROM SUBCASE AL21, ONSITE_TIME_LOG AL31WHERE AL21.SUBCASE_OBJID = AL31.SUBC_ONSITE2SUBCASE (+) AND ((AL21.SUBCASE_NUMBER LIKE '2049356%' OR AL21.SUBCASE_NUMBER LIKE '2049462%' OR AL21.SUBCASE_NUMBER LIKE '2057852%' OR AL21.SUBCASE_NUMBER LIKE '2057877%' OR AL21.SUBCASE_NUMBER LIKE '2057897%' OR AL21.SUBCASE_NUMBER LIKE '2057930%' OR AL21.SUBCASE_NUMBER LIKE '2057948%' OR AL21.SUBCASE_NUMBER LIKE '2057963%' OR AL21.SUBCASE_NUMBER LIKE '2057984%' OR AL21.SUBCASE_NUMBER LIKE '2057996%' OR AL21.SUBCASE_NUMBER LIKE '2058015%' OR AL21.SUBCASE_NUMBER LIKE '2058032%' OR AL21.SUBCASE_NUMBER LIKE '2058049%' OR AL21.SUBCASE_NUMBER LIKE '2066771%') OR (AL21.SUBCASE_TITLE LIKE '%CITS Audit%' OR AL21.SUBCASE_TITLE LIKE '%CITS Audits%' OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDIT%' OR AL21.SUBCASE_TITLE LIKE '%CITS_Audit%' OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDITS%' OR AL21.SUBCASE_TITLE LIKE '%CITS_Audits%' OR AL21.SUBCASE_TITLE LIKE '%CITS-Audit%' OR AL21.SUBCASE_TITLE LIKE '%CITS-Audits%' OR AL21.SUBCASE_TITLE LIKE '%CITSAUDIT%' OR AL21.SUBCASE_TITLE LIKE '%CITSAudit%' OR AL21.SUBCASE_TITLE LIKE '%CITSAUDITS%' OR AL21.SUBCASE_TITLE LIKE '%CITSAudits%'))GROUP BY AL21.SUBCASE_NUMBERORDER BY AL21.SUBCASE_NUMBER[/code]When I try to combine that query to the original query to include the other columns, I'm not entirely sure how to go about it. I am getting 'no rows selected'. Could someone tell me where I'm going wrong? (All of those OR subcase title like '% %' were copied exactly from the Hyperion report query. I realize that the CITS Audits are redundant.)[code="sql"]SELECT AL2.REGION_GFS, AL2.DIVISION_GFS, AL2.GFS_SITE_TYPE, AL1.CASE_TYPE_GROUP, AL1.X_CASE_TYPE_LEVEL1, AL1.X_CASE_TYPE_LEVEL2, AL1.X_CASE_TYPE_LEVEL3, AL2.SUBCASE_NUMBER, trunc((AL2.SUBCASE_CREATION_TIME)), trunc((AL2.SUBCASE_FIRST_CLOSE_DATE)), AL2.FIRST_ON_SCHEDULE_DT, AL3.DURATION, AL3.START_TIME, AL3.OWNER_FIRST_NAME, AL3.OWNER_LAST_NAME, AL3.OWNER_WORK_GROUP, AL2.SUBCASE_STATUS, AL3.TIME_TYPE, AL1.CASE_NUMBER, AL2.SUBCASE_CONDITION, AL1.CASE_OWNER_FIRST_NAME, AL1.CASE_OWNER_LAST_NAME, AL1.CASE_OWNER_WORK_GRP, AL2.SUBCASE_TITLE FROM CASE AL1, SUBCASE AL2, ONSITE_TIME_LOG AL3, (SELECT AL21.SUBCASE_NUMBER, SUM(AL31.DURATION/60) TOTAL_DURATION FROM SUBCASE AL21, ONSITE_TIME_LOG AL31 WHERE AL21.SUBCASE_OBJID = AL31.SUBC_ONSITE2SUBCASE (+) AND ((AL21.SUBCASE_NUMBER LIKE '2049356%' OR AL21.SUBCASE_NUMBER LIKE '2049462%' OR AL21.SUBCASE_NUMBER LIKE '2057852%' OR AL21.SUBCASE_NUMBER LIKE '2057877%' OR AL21.SUBCASE_NUMBER LIKE '2057897%' OR AL21.SUBCASE_NUMBER LIKE '2057930%' OR AL21.SUBCASE_NUMBER LIKE '2057948%' OR AL21.SUBCASE_NUMBER LIKE '2057963%' OR AL21.SUBCASE_NUMBER LIKE '2057984%' OR AL21.SUBCASE_NUMBER LIKE '2057996%' OR AL21.SUBCASE_NUMBER LIKE '2058015%' OR AL21.SUBCASE_NUMBER LIKE '2058032%' OR AL21.SUBCASE_NUMBER LIKE '2058049%' OR AL21.SUBCASE_NUMBER LIKE '2066771%') OR (AL21.SUBCASE_TITLE LIKE '%CITS Audit%' OR AL21.SUBCASE_TITLE LIKE '%CITS Audits%' OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDIT%' OR AL21.SUBCASE_TITLE LIKE '%CITS_Audit%' OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDITS%' OR AL21.SUBCASE_TITLE LIKE '%CITS_Audits%' OR AL21.SUBCASE_TITLE LIKE '%CITS-Audit%' OR AL21.SUBCASE_TITLE LIKE '%CITS-Audits%' OR AL21.SUBCASE_TITLE LIKE '%CITSAUDIT%' OR AL21.SUBCASE_TITLE LIKE '%CITSAudit%' OR AL21.SUBCASE_TITLE LIKE '%CITSAUDITS%' OR AL21.SUBCASE_TITLE LIKE '%CITSAudits%')) GROUP BY AL21.SUBCASE_NUMBER ORDER BY AL21.SUBCASE_NUMBER) GROUPEDWHERE ((AL2.SUBCASE_OBJID = AL3.SUBC_ONSITE2SUBCASE (+) AND AL1.CASE_OBJID=AL2.SUBCASE2CASE) AND (GROUPED.SUBCASE_NUMBER = AL2.SUBCASE_NUMBER AND GROUPED.TOTAL_DURATION = AL3.DURATION)) AND (((AL2.SUBCASE_NUMBER LIKE '2049356%' OR AL2.SUBCASE_NUMBER LIKE '2049462%' OR AL2.SUBCASE_NUMBER LIKE '2057852%' OR AL2.SUBCASE_NUMBER LIKE '2057877%' OR AL2.SUBCASE_NUMBER LIKE '2057897%' OR AL2.SUBCASE_NUMBER LIKE '2057930%' OR AL2.SUBCASE_NUMBER LIKE '2057948%' OR AL2.SUBCASE_NUMBER LIKE '2057963%' OR AL2.SUBCASE_NUMBER LIKE '2057984%' OR AL2.SUBCASE_NUMBER LIKE '2057996%' OR AL2.SUBCASE_NUMBER LIKE '2058015%' OR AL2.SUBCASE_NUMBER LIKE '2058032%' OR AL2.SUBCASE_NUMBER LIKE '2058049%' OR AL2.SUBCASE_NUMBER LIKE '2066771%') OR (AL2.SUBCASE_TITLE LIKE '%CITS Audit%' OR AL2.SUBCASE_TITLE LIKE '%CITS Audits%' OR AL2.SUBCASE_TITLE LIKE '%CITS_AUDIT%' OR AL2.SUBCASE_TITLE LIKE '%CITS_Audit%' OR AL2.SUBCASE_TITLE LIKE '%CITS_AUDITS%' OR AL2.SUBCASE_TITLE LIKE '%CITS_Audits%' OR AL2.SUBCASE_TITLE LIKE '%CITS-Audit%' OR AL2.SUBCASE_TITLE LIKE '%CITS-Audits%' OR AL2.SUBCASE_TITLE LIKE '%CITSAUDIT%' OR AL2.SUBCASE_TITLE LIKE '%CITSAudit%' OR AL2.SUBCASE_TITLE LIKE '%CITSAUDITS%' OR AL2.SUBCASE_TITLE LIKE '%CITSAudits%')))[/code]Wed, 03 Apr 2013 11:39:37 GMT5280_Lifestylehttp://www.sqlservercentral.com/Forums/Topic1367248-1044-1.aspxI've been tasked with setting up an Oracle linked server on a new SQL 2008 R2 install. I have installed the Oracle win64 11gr2 client on the windows 2008 r2 server. I then used Oracle Net Manager to create Service Naming for the connection I want. Do I then use that Service Naming for my linked server in SSMS? It looks like that is what was done on the old server I'm replacing but I didn't set that one up. Is this the most effecient way to do it?Tue, 02 Oct 2012 13:05:30 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1434963-1044-1.aspxWe are running into a issue with our development SSIS server. We have a Oracle 10g driver loaded and now i loaded the 11g driver for an SSIS package. When i try to create the ODBC connection i only see the 10g driver. Any ideas?Mon, 25 Mar 2013 09:05:31 GMTmmgyrionhttp://www.sqlservercentral.com/Forums/Topic1433328-1044-1.aspxI'm trying to get month and year output for an SSRS report using an Oracle data source for use as a multiple value parameter; the user can select dates such as February 2013, January 2013, October 2012. My current query does that, but it does not start at February 2013, it starts at December 2013. The MONTH_YEAR field is populated with month/year up to December 2050, which is why I'm trying to limit the parameter's starting date to choose from the current month and year.(I was going to post this in Reporting Services, but since it's the Oracle code that I want altered this seemed a more appropriate location.)[code="sql"]SELECT DISTINCT MONTH_YEAR FROM EDL.V_CLNDRWHERE SUBSTR(MONTH_YEAR,1,INSTR(MONTH_YEAR,' ')-1) <= TO_CHAR(SYSDATE,'Month') AND SUBSTR(MONTH_YEAR, -(LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' ')), (LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' '))) <= TO_CHAR(SYSDATE,'YYYY')ORDER BY SUBSTR(MONTH_YEAR, -(LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' ')), (LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' '))) DESC, TO_DATE(UPPER(SUBSTR(MONTH_YEAR,1,INSTR(MONTH_YEAR,' ')-1)),'MONTH','nls_date_language=american') DESC[/code]Wed, 20 Mar 2013 10:11:37 GMT5280_Lifestylehttp://www.sqlservercentral.com/Forums/Topic1419467-1044-1.aspxIF OUT_PAT_VISIT_ID_V = 'Collected' and NO_OUT_PAT_VISIT_ID_V = 'Collected' THEN :NEW.PAT_VISIT_ID := 'Collected'; ELSIF OUT_PAT_VISIT_ID_V = 'Collected' THEN :NEW.PAT_VISIT_ID := NO_OUT_PAT_VISIT_ID_V; ELSIF NO_OUT_PAT_VISIT_ID_V = 'Collected' THEN :NEW.PAT_VISIT_ID := OUT_PAT_VISIT_ID_V; ELSE IF OUT_PAT_VISIT_START_DTTM_V > NO_OUT_PAT_VISIT_START_DTTM_V THEN :NEW.PAT_VISIT_ID := OUT_PAT_VISIT_ID_V; ELSE :NEW.PAT_VISIT_ID := NO_OUT_PAT_VISIT_ID_V; END IF; END IF;END IF;Wed, 13 Feb 2013 06:24:43 GMTekant_alonehttp://www.sqlservercentral.com/Forums/Topic1428329-1044-1.aspxHello, I have what I hope is a simple question. I have setup a linked server using a System ODBC DSN that connects to a Netezza device. I'm currently testing, and while the performance is solid, I'm having trouble understanding what is happening when I try to "SELECT TOP [X] FROM ..."My understanding is that this is supposed to be interpolated to "SELECT * FROM TABLE LIMIT [X]" but the query continues to give me all the records. I've tried both of these:SELECT TOP 10 *From [LINKED_SERVER].[TABLE].[SCHEMA].[TABLE]SELECT TOP 10 *FROM OPENQUERY([LINKED_SERVER], 'SELECT * FROM [TABLE].[SCHEMA].[TABLE]')Both return all Records.If I try this:SELECT *FROM OPENQUERY([LINKED_SERVER], 'SELECT * FROM [TABLE].[SCHEMA].[TABLE] LIMIT 10')I get an error that says "Incorrect syntax near "SELECT", found "10" "If I Alias the table then I get the same error except it says found 'Limit'Any ideas?Thu, 07 Mar 2013 17:10:08 GMTDavid Fhttp://www.sqlservercentral.com/Forums/Topic1423199-1044-1.aspxI'm having a strange SSIS (SQL Server 208 R2) issue using OPENQUERY. I have created a Linked Server to our Oracle DB and it works great. I created a view that has a JOIN using an OPENQUERY statement that uses that Linked server, and that view also works just fine when queried in Mgmt Studio. I then tried creating an SSIS package that has an OLEDB source control that queries the view, but it fails with an ORA-01403 error when I try to Preview or run the package. The really odd thing is that if I take the SQL from the View definition and paste it into the OLEDB sourse as a SQL statement (instead of trying to use the view), the Preview and the package runs just fine.So, it appears that the issue is that accessing a view that has a JOIN to a table using OPENQUERY inside of it doesn't seem to work.Has anyone seen this scenario before?Fri, 22 Feb 2013 11:51:44 GMThenry 66733http://www.sqlservercentral.com/Forums/Topic1412626-1044-1.aspxI am trying to load tables from sql server to oracle using import/export wizard in SQL Server. Only 2 tables could be loaded successfully. Remaining tables failed. I have attached the error messages below. Mon, 28 Jan 2013 13:29:59 GMTlg123http://www.sqlservercentral.com/Forums/Topic1420325-1044-1.aspxI had a nightly SSIS package running on Server 2008 R2 that used the Oracle Provider for Ole DB. We have the Oracle Database as a linked server and the package was running fine. One night a couple of weeks ago, we got a error that the SQL server could not initialize the data source object of Ole DB Provider "OraOleDB.Oracle" for linked server "NAME". Microsoft SQL Error 7399.We had put a patch on the evening before and attributed that to getting the error. We unistalled Oracle and reinstalled it and set up the TNSNames file again. And the package started running again. Last night, the same thing happened, however, I have been told that no changes were made to the SQL server or to Oracle.Has anybody else had this issue and knows what causes this? Obviously to fix it, we will uninstall Oracle and reinstall it, however, we need to know how to prevent this from happening in the future.Thanks for any help.Thu, 14 Feb 2013 15:24:12 GMTkeith_stramlerhttp://www.sqlservercentral.com/Forums/Topic1419084-1044-1.aspxHi All,I need an urgent help to build some logic in Oracle DB.I have a table called TREE and in that we have data based on hierarchies.We have columns like DataID and ParentID and DataID column is unique one.And the data for DataID and ParentID will be this is like parent and child relation ship.I need to get the count of all DataID's and Sub-dataID's of ParentID.I need to get the count of this without using Start With connect by option.Is there a way to get this done...ThanksAnilkumar BTue, 12 Feb 2013 10:18:46 GMTanilkumar04_bhttp://www.sqlservercentral.com/Forums/Topic1419600-1044-1.aspxHi All,I need an urgent help to build some logic in Oracle DB.I have a table called TREE and in that we have data based on hierarchies.We have columns like DataID and ParentID and DataID column is unique one.And the data for DataID and ParentID will be this is like parent and child relation ship.I need to get the count of all DataID's and Sub-dataID's of ParentID.I need to get the count of this without using Start With connect by option.Is there a way to get this done...ThanksAnilkumar B Wed, 13 Feb 2013 09:11:03 GMTanilkumar04_bhttp://www.sqlservercentral.com/Forums/Topic1413103-1044-1.aspxI have an oracle linked table in an access 2007 database that is linked via an odbc connection.I have not used access much and am unsure of the structure and syntax of a select statement to a linked table. Could someone please provide and example of one.ThanksTue, 29 Jan 2013 08:53:20 GMTlewis.rayhttp://www.sqlservercentral.com/Forums/Topic1412699-1044-1.aspxHi, Sorry coming from the SQL world something as easy as highlighting the sp name and viewing the results in management studio is very difficult for me to find in SQL developer. I have the below sproc created in Oracle db, now i want to execute it and view the results to test the sproc. How do you do this? I tried using the "run" option when i right click the sp name in SQL developer and when i highlight the pl/sql block chunk of code after passing in a correct input parameter i get s "pls-00306:wrong number or types of arguments in call to '||'. Appreciate the help.CREATE OR REPLACEPROCEDURE GET_UNPROCESSED_SETREQ (vInstance IN pmcs_setreq_out.instance%type,retval out sys_refcursor )ASBEGIN open retval for select pmcs_seq, instance, mach_key, reel_key, set_num, reel_lm, set_lm, set_offset, diam, pmcs_pos_one_side, flipped, pmcs_action, ct_rolls, grade, trim_key_xref,pat_xref parent_pos, pmcs_setreq_values_00, pmcs_setreq_values_01, pmcs_setreq_values_02, pmcs_setreq_values_03, pmcs_setreq_values_04, pmcs_setreq_values_05, pmcs_setreq_values_06, pmcs_setreq_values_07, pmcs_setreq_values_08, pmcs_setreq_values_09, pmcs_setreq_values_10, pmcs_setreq_values_11, pmcs_setreq_values_12, pmcs_setreq_values_13, pmcs_setreq_values_14, pmcs_setreq_values_15, pmcs_setreq_values_16, pmcs_setreq_values_17, pmcs_setreq_values_18, pmcs_setreq_values_19, pmcs_setreq_values_20, pmcs_setreq_values_21, pmcs_setreq_values_22, pmcs_setreq_values_23, pmcs_setreq_values_24, pmcs_setreq_values_25, pmcs_setreq_values_26, pmcs_setreq_values_27, pmcs_setreq_values_28, pmcs_setreq_values_29 from pmcs_setreq_out where pmcs_processed = 0 and instance = vInstance order by pmcs_seq; EXCEPTION WHEN no_data_found THEN NULL;END GET_UNPROCESSED_SETREQ;Mon, 28 Jan 2013 16:44:48 GMTJP10http://www.sqlservercentral.com/Forums/Topic1411929-1044-1.aspxHey Guys,I am having a problem connecting to an oracle database using oracle8 client.It is installed on windows7.I have the same tnsnames.ora and sqlnet.ora files as everyone else here.The only difference is that everyone else is on XP and they are working fine, no problems.The client on Windows7 gets the error: ORA-12203: TNS:unable to connect to destination.I have also tried to connect to oracle from an access database on the same windows7 pc with an odbc and I get the same error as above.Any and all help would be appreciated.ThanksFri, 25 Jan 2013 13:54:49 GMTlewis.rayhttp://www.sqlservercentral.com/Forums/Topic1412285-1044-1.aspxHi ,I have a requirement where i have a table (EMP) with fields id, name, statusWhat i want is after each insert write the data to another table(EMP_BACKUP) and update the status of EMP table to 1.I have written a trigger------------------------------------------------------------------create or replacetrigger fetched_records_triggerafter insert on EMP for each rowDECLARE--v_username varchar2(10);BEGINinsert into EMP_BACKUP values (:new.id , :new.COUNT_PER_FETCH, :new.SUCCESS_COUNT, :new.FAILURE_COUNT, :new.FAILURE_DES, :new.STATUS);update EMP set status=1;END;Am able to insert the data into EMP_BACKUP table but cannot update the status column in EMP table to 1Am getting the below errror-------------------------------SQL Error: ORA-04091: table KCBUSER.FETCHED_RECORDS is mutating, trigger/function may not see itORA-06512: at "KCBUSER.FETCHED_RECORDS_TRIGGER", line 6ORA-04088: error during execution of trigger 'KCBUSER.FETCHED_RECORDS_TRIGGER'04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"*Cause: A trigger (or a user defined plsql function that is referenced inthis statement) attempted to look at (or modify) a table that wasin the middle of being modified by the statement which fired it.*Action: Rewrite the trigger (or function) so it does not read that table.Mon, 28 Jan 2013 02:54:06 GMTrajasekhar.cnhttp://www.sqlservercentral.com/Forums/Topic1404050-1044-1.aspxI want to migrate from Oracle 9i to Sql server 2008 using SSMA. Please let me know what steps will I have to follow.Our Oracle 9i database contain following objects :Tables Views Indexes Packages ProceduresFunctions Triggers Sequence Materialized ViewsPublic SynonymDatabase link JobsDirectorieswe need to do following changes after using SSMA:(1) change code for oracle cursors,built in functions,triggers as syntax is different in sql server(2) convert package into storeprocs/functions(3)convert sequence into database identity column(4) convert Materialized Views into indexed viewsPlease give me information about Public Synonym and Directories.How will it convert into Sql server Synonym and Directories.Is it possible?and also provide me information if I am missing anything for above mentioned all oracle database objects for conversion.Tue, 08 Jan 2013 01:47:36 GMTnitin.varshneyahttp://www.sqlservercentral.com/Forums/Topic1411331-1044-1.aspxHi,I want to export some tables from sql server 2008 to oracle 11g. I am planning to use SQL Server Import and Export tool available in SSIS, SQL Server. In order to export and import from SQL Server database to Oracle database, should I pre-create the table structure in Oracle database? Please help.Thu, 24 Jan 2013 13:45:48 GMTlg123http://www.sqlservercentral.com/Forums/Topic1411905-1044-1.aspxI have a query that pulls back both a Time and a Date column. I'm trying to figure out how I can search by the date and then by the time.Currently, I have a statement to search by the date but when I add a time statement, no results are pulled back and I know they should be there.Idealy, an example i'd like to see are all results on the 18th from 05:00 until 17:00 that day.Any ideas?Thanks,jess.[size="6"]This is a sample result set:TIME_C LANE ITEMS DATE_C BU CUST BAL05:00:00 1 1 11/18/2012 1234 1 1.5910:00:00 1 2 11/18/2012 1234 1 1.0922:00:00 1 3 11/18/2012 1234 1 1.42 This is my code:SELECT TO_CHAR(TO_DATE(CASE WHEN TO_NUMBER(TO_CHAR(TIME_C, 'mi')) > 59 THEN TO_CHAR(TRUNC(TIME_C, 'hh') + 1 / 48, 'hh24:mi:ss')ELSE TO_CHAR(TRUNC(TIME_C, 'hh'), 'hh24:mi:ss') END,'hh24:mi:ss'),'hh24:mi:ss') AS TIME_C, LANE, DTL_TY AS ITEMS, DATE_C, BU, COUNT(DATE_C) AS CUST, SUM(BAL_AMT) AS BALFROM ( SELECT DTL_QTY, DATE_C, BU, BAL_AMT, LANE, TIME_C FROM TRANS_C WHERE BU IN (329) AND LANE < 30 AND DATE_C BETWEEN TO_DATE ('11/18/2012','mm/dd/yyyy') AND TO_DATE ('11/24/2012','mm/dd/yyyy') )GROUP BY TIME_C, LANE, DTL_TY, DATE_C, BUORDER BY BU, DATE_C, LANE[/size]Fri, 25 Jan 2013 12:58:15 GMTjess.http://www.sqlservercentral.com/Forums/Topic1411834-1044-1.aspxI'm working on changing an Oracle CASE statement into a SQL Server CASE statement. I having a spot of difficulty in trying to figure out how to alter the code. It seems as though I am making it harder than it really needs to be. The Oracle code is...[code="sql"](CASEWHEN TO_CHAR(H.effdt, 'YYYY') IN ('2001','2002') THEN DECODE (H.review_rating, '1','Exceeded Way, Exceeded Results', '2','Exceeded Way, Achieved Results', '3','Exceeded Way, Did not meet Results', '4','Achieved Way, Did not meet Results', '5','Achieved Way, Achieved Results', '6','Achieved Way, Exceeded Results', '7','Did not meet Way, Exceeded Results', '8','Did not meet Way, Achieved Results', '9','Did not meet Way, Did not meet Results', 'Empty') WHEN TO_CHAR(H.effdt, 'YYYY') IN ('2003','2004', '2005') THEN DECODE (H.review_rating, '1','Does not Meet Expectations', '2','Partially Meets Expectations', '3','Meets Expectations', '4','Exceeds Expectations', '5','Far Exceeds Expectations', 'Empty') ELSE 'Empty'END) as review_rating[/code]My attempt to correct...[code="sql"]CASE WHEN YEAR(H.effdt) IN ('2001','2002') AND (CASE H.review_rating WHEN '1' THEN 'Exceeded Way, Exceeded Results' WHEN '2' THEN 'Exceeded Way, Achieved Results' WHEN '3' THEN 'Exceeded Way, Did not meet Results' WHEN '4' THEN 'Achieved Way, Did not meet Results' WHEN '5' THEN 'Achieved Way, Achieved Results' WHEN '6' THEN 'Achieved Way, Exceeded Results' WHEN '7' THEN 'Did not meet Way, Exceeded Results' WHEN '8' THEN 'Did not meet Way, Achieved Results' WHEN '9' THEN 'Did not meet Way, Did not meet Results' ELSE 'Empty' END)OR WHEN YEAR(H.effdt) IN ('2003','2004','2005') AND (CASE H.review_rating WHEN '1' THEN 'Does not Meet Expectations' WHEN '2' THEN 'Partially Meets Expectations' WHEN '3' THEN 'Meets Expectations' WHEN '4' THEN 'Exceeds Expectations' WHEN '5' THEN 'Far Exceeds Expectations' ELSE 'Empty') END) END) AS review_rating[/code]Fri, 25 Jan 2013 10:32:58 GMT5280_Lifestylehttp://www.sqlservercentral.com/Forums/Topic1411369-1044-1.aspxI've migrated some Oracle databases into some SQL Server databases and now I'm working on building reports from the Oracle queries. Some of the queries reference views, which, in turn, reference functions. I've managed to fare well up until this point. The function I'm trying to convert is using Oracle's 'rowid' data type, and I don't know much about cursors. I've included the messy looking Oracle function query below. How can this be converted to SQL Server?[code="sql"]create or replace function getlong( p_tname in varchar2, p_cname in varchar2, p_rowid in rowid ) return varchar2 as l_cursor integer default dbms_sql.open_cursor; l_n number; l_long_val varchar2(4000); l_long_len number; l_buflen number := 4000; l_curpos number := 0; begin dbms_sql.parse( l_cursor, 'select ' || p_cname || ' from ' || p_tname || ' where rowid = :x', dbms_sql.native ); dbms_sql.bind_variable( l_cursor, ':x', p_rowid ); dbms_sql.define_column_long(l_cursor, 1); l_n := dbms_sql.execute(l_cursor); if (dbms_sql.fetch_rows(l_cursor)>0) then dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos , l_long_val, l_long_len ); end if; dbms_sql.close_cursor(l_cursor); return l_long_val; end getlong;[/code]Thu, 24 Jan 2013 15:27:46 GMT5280_Lifestylehttp://www.sqlservercentral.com/Forums/Topic1407693-1044-1.aspxHi,is it possible to use a ms sql trigger to insert a row in Oracle Table ? I use this way an get error Error 7391: The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. I reconfig the MSDTS on the Server and allow all connection in and out but still get the error !?Is there other way to do this ?? I want to write the triggered Data in to a Oracle Table.THX MS SQL Trigger !CREATE TRIGGER [dbo].[trg_log_changes_table] ON [dbo].[TEST] FOR INSERT, DELETE, UpdateASBegin TranBEGIN IF EXISTS(SELECT * FROM Inserted) BEGIN INSERT INTO OPENQUERY ( [xxx], 'select dt_status,prs_dt_birth, DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from owner.T_SI_CHANGED') SELECT GETDATE () AS DT_Status, GETDATE () AS prs_dt_birth, 'I' AS DS_TYPE, DWDOCID AS DW_DOC_ID, PRODUNIT AS DW_ARCHIVE, DWMODDATETIME AS DW_MOD_DATE FROM Inserted ENDWed, 16 Jan 2013 02:22:36 GMTxoomhttp://www.sqlservercentral.com/Forums/Topic1400858-1044-1.aspxHi Please help me...I want join 4 tables in the Oracle.Table 1 is having 4 different where conditions.Which one is better (Performance wise)1.Join all 4 tables and write table1 where conditions globally (After Joining 4 tables)2.Make Table1 as Derived Table with all where conditions and joining with 3 tables.Please help meFri, 28 Dec 2012 03:45:42 GMTRaghu.Khttp://www.sqlservercentral.com/Forums/Topic1409269-1044-1.aspxhello i have a rather novice question but i cant figure it out myself. when i try to modify web.config file i put a connectionstring like this[code="vb"]<add name="ConnStr" connectionString="Data Source=<DB ServerName>;User ID=<SchemaName/UserName>;Password=<Password>;Persist Security Info=True;Connection Lifetime=10" providerName="System.Data.OracleClient"></add>[/code]what is 'DB ServerName' here?also when i try to connect via server explorer i get this message "ORA 12560 TNS: Protocol Adapter Error" but i have oracle connected via sqlplus.what can i do with these issues?Sun, 20 Jan 2013 05:44:03 GMTlamiajoyeehttp://www.sqlservercentral.com/Forums/Topic1408773-1044-1.aspxHi guys, recently i need to start to look after some of the linked server query . i got a instance of SQL 2005 and linked over to oracle server. i can run the pl-sql on the server without a problem (the query returns 200000 rows) on sql plus* in my local machines (the result come back within 25 sec) , when i ran the same query through linked server, it has no responses for about 25 minutes and sometimes it came back the error messages .here are the followings :OLE DB provider "OraOLEDB.Oracle" for linked server "REB_REPSER" "ORA-03113: end-of-file on communication channelOLE DB provider "OraOLEDB.Oracle" for linked server "REB_REPSER" returned message "ORA-12592: TNS:bad packet".but if i run a query which return less than 3000 rows, it brings the result back without a problem .i have look into different options on linked server setting (and i tried to modify the timeout) without any luck.anyone came across this error before ? and how do you solve it ? or where should i start ?please let me know if you require any more information ?i really appreciated with all your helpFri, 18 Jan 2013 02:49:31 GMTrayan98http://www.sqlservercentral.com/Forums/Topic1053539-1044-1.aspxI have been working on migrating three of our old server to a new single instance. The new instance is 2008R2 and I am using the 64bit OraOLEDB.Oracle provider.I have a couple of queries that are used in a DataFlow Task as the source(oracle). Both of these queries will run against the 32bit MS Oracle provider. Using the 64b Oracle provider, I have gotten both to parse, and provide results with the 'Preview'. Both will run in TOAD. Neither is successful when executing the package/task. Both fail with "ORA-00936: missing expression". Has anyone else suffered anything similar. Resolution?I have found that the OraOLEDB.Oracle provider is very particular in what it will accept. Queries that ran fine with the 32b MS Oracle provider must be massaged (or optimized ;-) ) to work.Tue, 25 Jan 2011 14:26:48 GMT_Louie_http://www.sqlservercentral.com/Forums/Topic1403162-1044-1.aspxHi, I have a push transactional replication set up that pushes to a Oracle 11g db. What used to take around 9 to 10 minutes now takes around a hour. There are about 3.5 million rows in this table (well call it table1 for this purpose) on the sql server. The oracle table (well call it table2) also has about 3.5 mill rows. There are only 2 indexs on each table. I ran a trace to try and find any long duration queries hanging, used sp_who2 to look for locks, cpu, i/o, memory and they all look fine.We also looked at the Virtual Log Files and they were around 3500 so we shrinked them to around 400 and right now were are going to check if that helped as I type this. Anything else I am missing your help is much appreciated? p.s. sorry i originally posted this same topic in another section..Fri, 04 Jan 2013 17:05:03 GMTJP10http://www.sqlservercentral.com/Forums/Topic1309881-1044-1.aspxHi all,I'm having an issue to create a linked server connection to netsuite on the cloud.They supplied me with an ODBC driver... They say that the odbc drive supports 32bit and 64 bit systems?!?!?With the same ODBC, I was able to connect with Excel 2010 but I can't figure out how to get the linked server working... I'm thinking, If it works for Excel it should work for MS-SQL... They're both microsoft products!!! right?My Excel data connection resembles this:DSN=NetSuite.com;UID=mylogin;SDSN=NetSuite.com;HST=odbcserver.netsuite.com;PRT=1708I'm on Windows 7, I see the odbc driver in my system DSN and user DSN in c:\windows\sysWOW64\odbcad32.exe and only see the odbc driver in the user DSN in the systems odbcad32I have a feeling that this has to do with a 32bit vs 64bit problem... but am not sure.I'm sure I'm not the first one with this problem and also sure that there must be a solution... Any help would be greatly appreciated as it's been practically a week that I'm trying to sole this problem... ThanksFri, 01 Jun 2012 11:20:33 GMTjghalihttp://www.sqlservercentral.com/Forums/Topic1400847-1044-1.aspxhi @all,i've got a problem and feel a little out of ideas:i'va a website where i upload attachments via html input tag (wihout any conversion) into an uploadfile-object on apache myfaces. Using .getBytes i get an java bytearray which will get sent into an oracle 11g database in an BLOB column. Works perfectly fine if it stays like this.now i got an sql server 2008 database where i'd like to query the data including my blob-column:bcp "select blob_column from <linked-server>.table where id = 487294" queryout "c:\share\File.pdf" -T -nworks perfectly fine for PDF files. but when i do the same with XLS or XLSX files, it creates the files properly, but when i try to open it, it fails with type problems....i realized when i compared the files, that in the BCP file there is an 4-byte prefix at the beginning of the file. the rest seams the same. interesting to see, that even in the PDF file, there are 4 heading prefix-bytes. the pdf editor doesnt care...anyhow.i also tried to create a format file and used it instead of the -n option. the file looked likes this:[quote]10.011 SQLBINARY 0 0 "" 2 FileData ""[/quote]Didnt work either...The oracle db runs on an AIX machine. any ideas would be highly appreciated!! :-)Fri, 28 Dec 2012 03:09:58 GMTmunzohttp://www.sqlservercentral.com/Forums/Topic1401685-1044-1.aspxHi All,I have SQL Server 2008 R2 installed on Windows server 2008 R2. I have a requirement to fetch the data from Oracle using SSIS, so I have installed Oracle client (32-bit and 64-bit) along with ODAC for Oracle client. Using OraOLEDB.Oracle provider I created a Linkedserver and able to pull the data using OPENQUERY.But when I tried to pull the data from Oracle using SSIS (Microsoft OLEDB provider for Oracle), it is throwing the following error:[size="1"][Connection manager "OracleSourceDB"] Error: SSIS Error Code DTS_E_OLEDBERROR. 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 client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.".[/size]Please suggestWed, 02 Jan 2013 01:47:07 GMTTarun Jaggihttp://www.sqlservercentral.com/Forums/Topic1395843-1044-1.aspxHi. I have a view defined in SQL Server 2008 R2 and that view "is looking" a table in Oracle 10g using linked server.I need to do this (using a SqlServer-side Store Procedure):1- get a numerator from the view.2- do some things affecting SQL Server tables.3- update the numerator in the view.This takes a small time, but I want to lock the oracle table during all my process because other programs may use the table.So.. what I want to do is something like this:1- lock the oracle table 2- get a numerator from the view.3- do some things affecting SQL Server tables.3- update the numerator in the view.4- release the lockI don´t know how to do steps 1 and 4.Tks for the helpWed, 12 Dec 2012 12:33:46 GMToscarbolsohttp://www.sqlservercentral.com/Forums/Topic1392229-1044-1.aspxHello,I'm doing text mining using Oracle SQL Developer: ODMiner.. I imported the data "WEBLOG" into a table.. This weblog data consist of users activity, date, time, url, etc. The first step I took was to use a function to transform date and time that I have in the data table, into a number representing the 40 mins since 01-01-1990. I did this by dividing it by 2400 (seconds in 40 mins). The main purpose is to have a time frame for the sessions.I used the following code,CREATE OR REPLACE FUNCTION ssnDate(DATE IN VARCHAR2 DEFAULT 03-01-18,TIME IN VARCHAR2) RETURN NUMBERASBEGINRETURN TRUNC((to_date(DATE||' '||TIME, 'DD-MM-YY HH:MM:SS')- to_date('01-JAN-1990','DD-MON-YYYY')) * (86400/2400);END ssnDate;This was what appeared in the log after running the statement,FUNCTION ssnDate compiledWarning: execution completed with warningAfter this, I tried to create a VIEW to transform the DATE and TIME with the ssnDate that was created earlier on, and concatenate the CS_URI_STEM (which is the resource accessed), and CS_URI_QUERY (which is the the query, if any, the client was trying to perform)into a new field called WEB_LINK.This is the code used,CREATE OR REPLACE VIEW WEBLOG_VIEWS("C_IP", "WEB_LINK", "CS_USER_AGENT", "SESSION")ASSELECT ssnDate(LOG_DATE, LOG_TIME) AS 'SESSION',C_IP,CS_USER_AGENT,(CS_URI_STEM||'?'||CS_URI_QUERY) AS WEB_LINKFROM WEBLOG;Now from this I got the following error..Error starting at line 1 in command:CREATE OR REPLACE VIEW WEBLOG_VIEWS("C_IP", "WEB_LINK", "CS_USER_AGENT", "SESSION")ASSELECT ssnDate(LOG_DATE, LOG_TIME) AS 'SESSION',C_IP,CS_USER_AGENT,(CS_URI_STEM||'?'||CS_URI_QUERY) AS WEB_LINKFROM WEBLOGError at Command Line:3 Column:38Error report:SQL Error: ORA-00923: FROM keyword not found where expected00923. 00000 - "FROM keyword not found where expected"*Cause: *Action:I don't get where I'm going wrong with this.. This is the data preparation stage which requires me to prep the data before applying modeling techniques or algorithms.. The next step would be grouping the data, based on the session time, ip and the user agent of each session along with the web_links fields visited by the user in that session.I would really be grateful for any inputs on where I'm going wrong and any solutions for that!Mon, 03 Dec 2012 20:25:08 GMTsap0698http://www.sqlservercentral.com/Forums/Topic1389584-1044-1.aspxI have transferred one table from sql server 2008 r2 to oracle 11g. using import export wizard.all permissions in oracle are set proper. while exporting it shows me records are transferred.then I connected to oracle & search my transfer table using select * from tab;It shows me table is there but when I want to see data using select * from mytable_name;then it gives me an error that "table or view doesnot exist".I tried to build an SSIS package & transferred table but same thing happen.DBA permission is assigned to oracle user. Please help me why I can't see table in oracle, if SQL shows "rows are transferred".Wed, 28 Nov 2012 02:23:17 GMTmssqlsrvhttp://www.sqlservercentral.com/Forums/Topic1391452-1044-1.aspxHello,I've a database in primary server that log ships to a database in secondary server. I want to capture only deltas after log shipping is done. To do that, I'm wondering if I could implement materialized view log on each table in database on secondary server. pls let me know your 2 cents. Thanks.Note:I cannot have access to primary server.Fri, 30 Nov 2012 10:31:18 GMTsqlbi.vvamsihttp://www.sqlservercentral.com/Forums/Topic1391532-1044-1.aspxOn a SQL Server instance I have a linked server to an Oracle database. If I run query using a date it is ok from SSMS master database. But if I try to run it in a user database the date conversion fails. Is this the way it is, I have to use master? Or can I fix the conversion?Query has:... where Linkname..databasename.datefield = '2011-01-01 00:00:00.0000000'It runs fine in master but when run in a user database I get error:"Conversion failed when converting date and/or time from character string"Thanks.Fri, 30 Nov 2012 13:15:20 GMTDenise McMillanhttp://www.sqlservercentral.com/Forums/Topic1198283-1044-1.aspxHey guys, I am trying to setup a linked query to an oracle server on my machine. I am using Sql Server 2008 and a 64-bit OS. I have installed the ODAC for windows and ODBC for RDB drivers for 64-bit windows. However I am getting the following error.Msg 7308, Level 16, State 1, Procedure sp_testlinkedserver, Line 1OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.'Any ideas what I am missing? Thanks.Mon, 31 Oct 2011 13:30:22 GMTcoder_t2