SQLServerCentral / SQL Server 7,2000 / Working with Oracle

"ORA-04091" Mutating Error

Minnu — Fri, 29 Mar 2013 08:04:15 GMT

ora

create Linked Server to Oracle DB - Is this TSQL Script correct? - Novice

Mile Higher Than Sea Level — Thu, 11 Apr 2013 10:36:52 GMT

Installed the Oracle 11g client on the SQL Server 2010 server. Using the Select Directory Server - the dengs.eo.com with the port xxxx shows Available = Yes PLMKR5 is the service name[b]Quetion - is the script to use to create a linked table? It receives an error when trying to expand the Tables.[/b]USE [master]GO--Create the Oracle Linked Server: EXEC sp_addlinkedserver 'FinderFile14', 'Oracle', 'OraOLEDB.Oracle', 'PLMKR5.eo.com:1521/dengs'-- Actual names / un/ pw modified EXEC sp_addlinkedsrvlogin 'FinderFile14', 'FALSE', NULL, 'rm', 'rmX'[b]The Oracle DBA sent this script to me:[/b]PLMKR5.DEN.EO.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dengs.eo.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = PLMKR5.DEN.EO.COM) )In SQL Server SSMS -the Providers list OraOLEDB.Oracle(this is a a new server, we didn't quite get the last one connected because it was an oracle verson 6)This server is Oracle 11g

mutation in Trigger

Minnu — Thu, 28 Mar 2013 06:09:26 GMT

Hi Team,Am a sql developer and new to oracle, i was assinged a trigger foram having two tablesTable1 : TestCols : ID | DESCTable2: TEST_CUSTCols : MID | STATUSand i want a update in third table "S_ID" when an insert | Delete | Udpate happends on Table1.Table3 : SIDCols : M_ID | STATUSCREATE OR REPLACE TRIGGER TRG_TESTAFTER INSERT OR UPDATE OR DELETEOF ID,DESCON TESTREFERENCING OLD AS OLD NEW AS NEWFOR EACH ROWBEGIN UPDATE S_ID SET STATUS ='Y' WHERE SID in (SELECT SID FROM TEST_CUST WHERE M_ID=:old."ID"); END;--But am gettting error "A mutation table is defined as a table that is changing"need your help please...

Learning Oracle

Lynn Pettis — Tue, 31 Aug 2010 19:53:32 GMT

Found some resources, SQL Reference Guide for Oracle 10g. Guess what, Oracle actually has CTE's, they just don't call it that in the reference manual.Having access to the reference manual is showing me that I can actually use some of the things I have learned here on SSC.Still looking forward to seeing if I can push for moving toward SQL Server, but we'll have to wait and see.Now, if they would just get me access to an instance of Oracle.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

pavargasq — Fri, 26 Nov 2010 12:28:56 GMT

Good afternoon:There is a process that somestimes fails with the error:Error converting data type DBTYPE_DBTIMESTAMP to datetimeThis error usually happnes when you import data from another sources of data like oracle.The weird thing is that i restart the instance in sqlserver 2000 and execute once again the procedure and it works OK.Thr problem is i cannot restart the instance everyday because there are other procedures running, and i think this is a temporary solution. but i cannot find out the "root of the problem.". dont know if it is a sqlserver problem.is there any better solution to tackle the problem from the root?This a part of the codePRO_ETL_CARGUE_PS_CATEGORIAS_CARGO_TD 2900, 511188, '' INSERT INTO DETALLE_PS_CATEGORIAS_CARGO_TD (COD_CATEGORIA ,MIN_CARGO_REQUERIDO ,ID_CATEGORIA ,DES_CATEGORIA ,FEC_CREACION ,USU_CREACION ,MIN_CARGO_REQUERIDO,ID_CATEGORIA,FEC_MODIFICACION,USU_MODIFICACIONFROM OPENQUERY (SCL,'SELECT TD.COD_CATEGORIA,TD.ID_CATEGORIA,TD.DES_CATEGORIAfrom PS_CATEGORIAS_CARGO_TD TD') (1 row(s) affected)Server: Msg 8114, Level 16, State 8, Line 1Error converting data type DBTYPE_DBTIMESTAMP to datetime.Id appreciate your help

Datetime Formating -

John Paul-702936 — Wed, 27 Jul 2011 09:09:13 GMT

hi, - how to get the Last 30 days records from a table in Oracle if the column is in format string - like Example - the value in the table is - [b]1251807219[/b]the value in formatted is "[b]2009-09-01 08:13:39.000[/b]"and after foramting date I want to use this column in where condition to get the records from last third days IN SQL SERVER , I can using Date Add - but how cani get the Oracle Select col1 from tabel where dateadd(s,convert(int,1251807219)-(4*60*60),'01/01/1970') <getdate()- 30Please help.., Thanks

Cursor use in Oracle

pandeharsh — Fri, 24 Jul 2009 02:24:02 GMT

Hi,I am newbie in oracle.Should we use cursor in oracle store procedure either in select statement or insert statement.Thanks

INVENTORY ONE TABLE CREATE HOW TO BALANCE QTY CARRY IN NEXT ROW

vpatil_vsp — Tue, 22 Feb 2011 12:42:02 GMT

MY DATEITEM_NAME , CODE , DATE , OB_QTY , RECD_QTY , ISSUE_QTY BALANCE_QTY5 PR CABLE 05 01/04/10 25 0 0 255 PR CABLE 05 02/04/10 0 2 0 25 PR CABLE 05 03/04/10 0 0 2 -210 PR CABLE 06 01/04/10 20 0 0 2010 PR CABLE 06 02/04/10 0 0 5 -05 I WANT RESULT ITEM_NAME , CODE , DATE , OB_QTY , RECD_QTY , ISSUE_QTY BALANCE_QTY5 PR CABLE 05 01/04/10 25 0 0 255 PR CABLE 05 02/04/10 0 2 0 275 PR CABLE 05 03/04/10 0 0 2 2510 PR CABLE 06 01/04/10 20 0 0 2010 PR CABLE 06 02/04/10 0 0 5 15 PLEASE HELL ME

Oracle BLOB to SQL Server 2000 ntext

david.buncle — Thu, 03 Apr 2008 08:28:53 GMT

Hi.I'm wondering how I go about importing data from an Oracle BLOB (holds XML) into a SQL Server 2000 field of type ntext. When I import the data normally (using Import wizard), the data is transferred across but it is in a compressed form as seen below.[b]1F8B0800000000000000ED5C5D73E23816FD2B2EF675011B082153842EE298C026030CD0DD354F29B52D40D5C6F2487636D987FDED732519B08D491C4CC2CC2EA914C1B26C5DDD73EE87AEE5B44DC4B0F584BD60827DCA82CAC4BAD39E57AEC7AF4B21F37E59BA9765CA16BF3CD54B1A74622F650FADF075A947D9EA31F41D14[/b]Is there a way to decompress/transform this data when copying it across to SQL Server? Or, alternatively, running a procedure to convert or decompress it at the SQL Server end? Hence the desired data to be stored in the table would be my actual xml. Keeping in mind that I'm using SQL Server 2000. Regards,Dav

oracle 1Z0-053 practice exam

gemxia — Sun, 24 Oct 2010 21:04:30 GMT

Now, Oracle’s 1Z0-053 exam become more and more popular. Recently, I also want to take part in 1Z0-053 exam, but i'm afraid that i can not pass it. A friend recommend me Examkiller, I feel that Examkiller have a lot of free 1Z0-053 practice questions, I can freely download, through a few days of practice, my confidence has increased.Our Examkiller on-site online training experts create all of the Oracle 1Z0-053 exam products. Our main goal is to get your certified with a firm understanding of the core material. Whereas other online distributors only concern themselves with helping you obtain the paper, we strive to educate the certification candidate and better prepare them for their IT career.With the complete collection of Questions and Answers, Examkiller 1Z0-053 is high enough to help the candidates to pass 1Z0-053 exam easily without any other study materials and no need to attend the expensive training class.To match the current real test, the technical team from Examkiller will update the Questions and Answers for any changes in time, and also we are always accepting the feedbacks about 1Z0-053 exam from our users, in specialty, we will mend the 1Z0-053 exam pool with the suggestions from those users who got full scores in 1Z0-053 exam, so to perfect Examkiller 1Z0-053 to make it always have the best quality!Our Oracle 1Z0-053 Exam will provide you with 1Z0-053 practice exam questions with verified answers that reflect the actual exam. These questions and answers provide you with the experience of taking the actual test. High quality and Value for the 1Z0-053 Exam: 100% Guarantee to Pass Your 1Z0-053 exam and get your 1Z0-053 Certification.

Oracle Question

Lynn Pettis — Wed, 20 Oct 2010 10:04:43 GMT

For those watching this forum for Oracle questions, I have one [url=http://www.sqlservercentral.com/Forums/Topic1007856-2790-1.aspx][b]here[/b][/url].

System Tables in Oracle 10g

Lynn Pettis — Fri, 08 Oct 2010 08:26:49 GMT

We all know that in SQL Server that there is a system table that tells you about all the columns in each table in a database, sys.columns (in SQL Server 2005/2008).Is there an equivalent system table or view that provides the same information? I'm not having much luck finding one, but perhaps one of you out there that also are familiar with Oracle may be able to assist.

Locking in oracle

qaz123 90964 — Sat, 09 Oct 2010 03:31:44 GMT

hiI have a select statement in sql 2000, with the select on a linked server (oracle) table.For example select * from linkserver..dbname.my_table in SQL 2000 reportedly caused locking issues in oracle server where the actual table is located. I guess this is probably imposed by the linkedserver driver used.Thought this can be solved by having a view in oracle for the select and calling the view in SQL. While that works, I need to verify if calling the view has indeed prevented the lock from being imposed on the table. I mean, like knowing if lock has been acquired on a table? Any solution ?Also, how can i verify that the initial lock on a select on oracle table was imposed by the driver?Pls helpThanks /* I have posted this in another sub forum too but i am surprised no has replied yet. */

Openquery from Sql Server 2003 to Oracle

gprasanna — Tue, 13 Jul 2010 06:56:02 GMT

Hi All,I am working on a task, where I am connecting to Oracle database to update a row from SQL server 2000. I use OPENQUERY to connect and execute the update query in the Oracle database.UPDATE OPENQUERY(ORACLE_LINKED, 'Select attribute1 from SettingMaster Where status = 1') SET ATTRIBUTE1 = '25'I have a issue when I try to update a record, but if it is locked by other application/user for update, my query waits infinitely.I need a way to set a timeout here of 60 seconds, and proceed on the next record to update.Any ideas are highly appreciated.Thanks,Prasanna.

Passing Date parameters to Oracle

itwhiz — Thu, 06 May 2010 16:49:08 GMT

I have defined in my DTS package several date Global Variables, these hold dates in various formats and types. My problem is that sometimes when I run my query/task the dates do not get past to Oracle in the correct manner and I have to keep messing with the task to get it to work.For example Global Variables defined asGVSysdate Date 01/05/2010GVSysdate_Str String 01/05/2010Various SQL statements that randomly workTrunc(tab1.status_date) = to_date(?,'DD/MM/YYYY') (? is defined as GVSysdate_Str)ORTrunc(tab1.status_date) = to_date(to_Char(?,'DD/MM/YYYY'),'DD/MM/YYYY') (? is defined as GVSysdate)Does anybody know of a definitive method of using dates from SQL2000 to Oracle when they are defined as Global variables and are used as input parameters to tasks, as I'm wasting about 1hr perday trying to get these tasks working !Thanks in advance

Making the leap Sql Sever from Oracle

David O — Tue, 22 Sep 2009 16:09:53 GMT

Hired a solid tech with a great Oracle development background but need him to do Sql Server development now. He is trying and making progress but I'd like to shorten the learning curve. Does anyone know of any training like this except for development:http://msdn.microsoft.com/en-us/dd548020.aspxThanks,David

Jobs (DTS packages) hangs after Oracle Goes down ...

Arpit Goel — Tue, 29 Dec 2009 11:56:27 GMT

We have 4 SQL Server 2000 jobs in production which uses DTS packages to pull data from Oracle. These jobs used to fail and send failure notification repeatedly every weekend whenever Oracle Server goes down during downtime. After oracle comes up , jobs used to resume by itself with out requiring any manual interventions. Since last two weekend , after Oracle goes down - our four jobs gets hung reporting below error. But I don't see any issue with below error which says Oracle listener is not available as we already know it and used to see this error for last 2 years after oracle goes down. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 12541 (30FD) Error string: [Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun: Package execution complete. Process Exit Code 1. The step failed.Now DBAs were not able to stop/restart these hung jobs - and ended up recreating Jobs to resolve the issue. Please advise what could have changed on SQL Server or Oracle suddenly which cause such issue.

Connection problem to Oracle DB with a DTS

laurent_landry — Wed, 11 Nov 2009 06:36:30 GMT

Hi,I have to make a connection to a Oracle DB 11g with a DTS. I add the connection settings in my Oracle client TNSNAMES.ORA.in The DTS, i choose the icone "Microsoft ODBC Driver for Oracle"I gave the values in the fields Server : , Username : , Password : When i push the button ok, the connection is accepted but when i return to the icon, the field "Server : " is empty.Note that i can add a "System DSN" without any problem. I tried also with another DB of Oracle in the DTS and it work but the version of Oracle is 10 or 9 i believe.Please help !

Where is my Object browser icon in sql developer ?

pandeharsh — Wed, 14 Oct 2009 00:26:09 GMT

Hi,I am using oracle sql developer 1.5.1Once my object browser window was close after that I am unable to find the icon of object browser.Where is it ?Is there any online tutorial,from where I can get these things ?Thanks

Working with Oracle and CLOBs

chotmaranirpo — Wed, 23 Sep 2009 02:09:45 GMT

I am using Weblogic 8.1 and Oracle 9i. I simply want to insert a CLOB into an OracletableI pretty much know how to do this with an OracleConnection BUT the Connectionwhich i'm retrieving from my oracle datasource is some sort of Weblogic WrapperConnection which I cannot cast to an OracleConnectionSo:(1) If I have setup an Oracle Datasource (using a thin driver), when I retrievea connection why is this not of type OracleConnection?(2) If I can't get access to the underlying OracleConnection, then can I inserta CLOB using the Weblogic connection which i recieve?

Cursor invalidated during parallel execution

kienway — Tue, 26 May 2009 22:21:13 GMT

Hi,My DTS have 5 parallel transfer data from sql db to oracle db at the same time, it have come up with an error "ORA-12842: Cursor invalidated during parallel execution".Based on the information I get is as per below:Cause: The cursor was invalidated during the parse phase of deferred parallel processing, e.g. when set operands are parallelized. Action: Depends on why the cursor was invalidated. Possible causes include DDL on a schema object and shared pool being flushed. I have checked with all the nested view and the status is valid during that time.FYI, the view have function, materialized view, view and table.Every time the cursor invalidated happen, the query from the DTS will become slower and slower. Any idea what the cause of it or how to trace the problem? Thanks And Best Regards,Kien Way

SQL SERVER SP to ORACLE

Nero-1119276 — Sun, 19 Jul 2009 23:38:25 GMT

hi all,I have to migrate stored procedure created in sql server to ORACLE 10g.I done with searching google but no helpful links.Can any give me hint to do it?Thanks,Neerav

to_char

newbieuser — Thu, 20 Aug 2009 08:47:22 GMT

Hi all, In oracle I use this query to format the date field. chg_dt is a date field in addchg tableselect to_char(chg_dt,'YYYY/DD/MM HH:MI') as date from addchg;DATECHG----------------2002/11/11 12:00How can I do this in sql server?? Seems to be too complicated... I tried using convert but couldnt get this formatselect convert(datetime, chg_dt,112) from addchgI keep getting 2002-11-11 12:00:00:000Please help thanks

Could not fetch a row using a bookmark from OLE DB provider 'MSDAORA'.

kienway — Wed, 19 Aug 2009 18:49:17 GMT

Hi,I currently need to perform updating the link server based on the local table condition. I get the error when i using openquery. Can anyone help? Thanks.The error message is as per below:Could not fetch a row using a bookmark from OLE DB provider 'MSDAORA'. [OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetLocate::GetRowsByBookmark returned 0x80040e21: ].Query:UPDATE OPENQUERY([LS-ORACLE],'SELECT Status, LastUpdate FROM ebh.CliInfo WHERE Src = ''A''') SET Status = 'E', LastUpdate = GETDATE()FROM (SELECT * FROM OPENQUERY([LS-ORACLE],'SELECT * FROM ebh.CliInfo WHERE Src = ''A''')) a INNER JOIN LocalTable b ON a.CliCode = b.ClicodeWHERE a.LastUpdate <= b.LastUpdate

Getting Oracle backup into SQL Server

schleep — Thu, 21 May 2009 11:13:40 GMT

Hi all,I've been handed a backup of an Oracle DB. We need to get that data into a SQL Server DB.I haven't the faintest idea how. I've installed Oracle 9i personal on a spare server.I don't know the Oracle lingo for creating a DB, restoring a backup, etc.It keeps asking me for a Service Name?Can anyone point me in the right direction?Thanks,Paul

Linked server to acces DB file

raty_2004 — Mon, 22 Jun 2009 08:55:54 GMT

Hi all, please help if anyone can, I am sure here are masters :) i create linked server to access file in which I have localtalbes, linked tables to oracle dband linked tables to SQL DB, I can query all locals and SQL talbes from this linked server but when I try to query "SELECT * FROM TEST1...VASO_LOAN_UPDATES"where "VASO_LOAN_UPDATES" is linked table to oracle DBI receive error "Msg 7306, Level 16, State 2, Line 1 Cannot open the table "VASO_LOAN_UPDATES" from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST1". The specified table or view does not exist or contains errors." how can I fix it? Tnx advance

Calculate relative dates in Oracle

Jason Coleman — Tue, 16 Jun 2009 02:30:07 GMT

Hi,I'm doing some work in Oracle on date ranges, and have a calendar table with Activity_date. I'd like to calculate the "relative week no." from this, but I'm not sure of the functions to use...In MS SQL it was just easy to use datediff, but there is not such function in Oracle, as the dates are stored as numbers....Anyone point me in the right directionThx..

Save as in Toad

keywestfl9 — Sun, 07 Jun 2009 08:59:13 GMT

i am trying to save the output of a query to a delimted text file with comma as delimter.if a column contains a column with column (For example John,smith) John goes to one column and smith to other columnhow do i avoid and make it as one column.Please let me know

Openquery with ALTER SESSION SET NLS_DATE_FORMAT = "DD/MM/YYYY HH24:MI:SS

franzambesten — Thu, 13 Nov 2008 06:43:28 GMT

HiI need to know if it is possible to use openquery within a statement to execute the alter session sentence for oracle db.Sorry for my englishIf this is not the rigth place for the question, would you please direct me ?thanks in advance for any help !

DTS to flat file for pickup by Oracle

tosscrosby — Fri, 10 Apr 2009 08:46:41 GMT

I have a table which allows for storing notes about a client. One field is ntext(8000), where the users input the history about a particular client/claim. They have CR/LF in the field, which I can handle in SQL and the application. However, when I export the data to a text file, vertical bar delimited for each field, when our Oracle DBA tries to import it, it breaks that field into multiple rows of data, breaking on the CR/LF. Now, having said all that, I know zip about Oracle and don't know how to help the Oracle DBA. I've tried ansi and unicode as an export option and that doesn't seem to matter. Lastly, we cannot setup a linked server as our version of Oracle (7.32) needs to be at 7.33, minimum, based on my attempts to setup the linked server and there are no current plans to upgrade. Any thoughts on how/what I can try to get around this?

Problems importing data from Oracle to MSSQL Server 2000 using a DTS package

Jesus Perez-457332 — Fri, 20 Mar 2009 11:46:22 GMT

Hi guys,This is my scenario.. I need to synchronize data from oracle server to MSSQL Server 2000, for this purpose I'm using a DTS package (The connection is trough a ODBC Driver), the development was made using a TEST Oracle Server, so that never had this issue (There Wasn't limits for the resources), now, when i ran the DTS package over a real Production Oracle Server, a DTS DataPump Task failed (this task synchronize a table with 2,330,000 registry or more), the error code is ORA-01555, so I asked to a Oracle Administrator and the problem was originated because a concurrent use of the ROLLBACK SEGMENT (so the problem is that could not have a consistent reading), I need to know if exists something in the design of my DTS package thats could help me to solve this issue, i do not know if it is possible to establish a commit point for a determined quantity of information imported, making possible that the readings release the ROLLBACK SEGMENT and start again.Any help will be too appreciatedRegards,Jesus

How to register Oracle Server

Saqib-215297 — Wed, 01 Apr 2009 06:57:01 GMT

hi guys,How to register Oracle DB server in my MSSQL Enterprise manager.I need to work on tables, data of Oracle database sitting on my MSSQL query analyzer or MS Enterprise manager.

DTS Package Error

James W — Thu, 26 Mar 2009 04:26:16 GMT

Hi,I'm having problems with a DTS package that copies data from an Oracle database to a SQL database. The package runs successfully if it is manually executed however fails every time it is scheduled. The error given in the Job History is as follows:Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0)When I try to recreate the package on a test server or try to edit the package on the live server, I get two errors which are:The Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation.You will be unable to use this driver until these components have been installed.andHResult of 0x80004005 9-2147467259) returnedUnexpected error occurred. An error result was returned without an error message.This was using Microsoft ODBC Driver for Oracle and I've tried recreating the package with the OLE DB Provider for Oracle and get the Hresult error but no Oracle driver error. I've also tried updating the Oracle driver on the server to no avail!Does anyone have any ideas what the problem could be?Thanks,James

ORA-01722: invalid number Error occurred When using DTS

Hirotaka_Hibiya — Tue, 17 Mar 2009 00:49:48 GMT

Hi All;I'd like to copy a part of records in Oracle DB to SQL Server 2000 with DTS.DTS's Steps are...1. Run Execute SQL Task to retrieve the data and output to GV with RecrodSet Type. [code] SELECT col1 FROM TableA --> Lists (RecordSet) !! some records are return as result !![/code]2. Run ActiveX Script Task to change data type from RecordSet to String and output to GV with Lists_for_Query.[code] set objRS = CreateObject("ADODB.Recordset") set objRS = DTSGlobalVariables("Lists").value for cnt = 1 to objRS.RecordCount if strList = "" then strList = objRS.Fields("col1").value else strList = strList & ", " & objRS.Fields("col1").value end if objRS.MoveNext next DTSGlobalVariables("Lists_of_Query").value = strList[/code] Lists_of_Query contains a string such as "1200, 1201, 2003"3. Run Transform Data Task to copy a part of records in Oracle DB to SQL Server 2000 with following Query.[code] SELECT * FROM TableB WHERE (col2 IN ([b]?[/b]) )[/code] !! The type of col2 is Number(10) !!When I run this DTS, I got "ORA-01722: invalid number" Error Message.But I can run the following query in Oracle SQL Developer Toole.[code] SELECT * FROM TableB WHERE (col2 IN ([b]1200, 1201, 2003[/b]) )[/code]Do you have any idea?

www.sqlservercentral.com

nevergiveupjohnbenny — Mon, 21 Apr 2008 04:30:49 GMT

sir i have a problem while creating a procedure to insert values with a table containing foregin key please help

Linked Server to Oracle through Microsoft OLEDB provider

praveen voleti — Tue, 05 Aug 2008 15:41:48 GMT

Hi All,I have created a linked server to Oracle database. I have used the below command to create the linked server:sp_Addlinkedserver 'ORACserv','Oracle','MSDAORA','XYZ'where XYZ is the Database name having associated TNS entries. Oracle client tools 9.2 are installed in the system.After the linked server connection is created, I have specified the login credentials with which to connect. I am receiving the below error while querying the data:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].Here are the things that I have tried out till now:Connect through SQL plus locally to the oracle server with the credentials given earlier. The connection succeeded and I am able to query the data.Created one more linked server using Oracle OLEDB provider and the same credentials. The connection is working fine.Checked the registry entries for ORACLE_HOME as specified in other forums. The directory that is being pointed is correct. Tried both the distributed query like - select * from ORACSERV.XYZ.SCHEMANAME.TABLENAME and OPENQUERY statement. Both are failing with the above error.I am not sure why I am receiving this error? The credentials seems to be correct and the Oracle server and SQL are also working fine. Could someone please help in this regard?

Sql Server 2000 Openquery insert takes too long

Stewart Fay — Tue, 27 Jan 2009 06:31:45 GMT

To whom can help,I am using Sql Server 2000 and have a linked server to Oracle.I am doing an insert into the oracle from data on the Sql Server 2000.it is taking 26 minutes to process 2210 records. at 85 records per minute this appears to be a long time for a relatively small amount of records.Someone has mentioned that I may be inserting each record row by row instead of all 2210 at the same time. is this possible and if so how can I check.Basically I want to speed this up (hopefully using setup) and not have to give the answer to the client that their network is rubbish.Any suggestions or pushes inthe right directions would be useful.Provider using: Microsoft OLE DB Provider for Oracle (Currently Can't get Oracle Provider for OLE DB to work (error 7399 returned) should I get this working?)Query is:Insert into OpenQuery (FinPRUAT, 'Select Field1 From Table1 Where Field1 1=0') Select Field1 From Table1 Inner Join Table2 On Table2.Field1 = Table1.Field1 Where Table2.Field2 = 10001228Thanks

How to synchronise data between SQL Server and Oracle

rajeev_vandakar-782919 — Sat, 13 Dec 2008 00:17:28 GMT

Hi all, Rajeev here from Bangalore. This is the present scenario with us. In some multiple sites (say Bangalore, Chennai, Pune, Delhi etc) separate MS SQL servers were deployed and running for some couple of Months. Each SQL server holds same data structure (software etc). Now they want to introduce a central Oracle server and want to update (synchronize) database from each of these distinct SQL servers periodically, say on each day by 6:00 pm, so that any pint of time in Oracle Central server previous day’s transaction can be found. I don’t have much work experience with Oracle, but in MS SQL I know DTS to do this. But how to synchronies Oracle with each distinct MS SQL only for last modified/inserted data? What feature/function available in MS SQL or in Oracle to do this job smoothly? Please help me in this regard. Thanks in advance.

Problems : DTS with ORACLE as destination

Kaushik Majumder — Fri, 12 Dec 2008 10:48:55 GMT

Hi,Currently in a spot of bother and would greatly appreciate any advice with respect to the folowing..We do need to transfer some data from SQL Server (2000) Source to the ORACLE (9i) detination table. The source has a datetime stamp which to be used to transfer the most recent data since the last transfer. In destination, only the unique rows need to be appended while the existing rows need to replaced.The initial solution did contain a couple of temporary tables in both the environment, but unfortunately, the client is not very comfortable with the new tables...Need to know...A. If it is possible to update a global variable from TSQL (For example, whether it is possible to update a global variable with 'SELECT MAX(Dt) FROM ORACLE.TABLE')B. If the entire activity can be accomplished by activex scripting. ie using vbscripts, creating sql and oracle connection and recordset objects, looping through the recordset, and calling an ORACLE SP (as done so widely in vb) with the field values as SP parameters.Thanks in advance.Regards,

SQL 2000 Oracle Linked Server - Divide by Zero

ron.bracale — Tue, 11 Nov 2008 08:56:25 GMT

Situation: SQL Server 2000 with a Linked Server to an Oracle table: in production, but not in Development or Quality about four months ago started returning a divide by zero error in the DTS package when the table was empty, but not when there were rows to join on. Then last month it started returning the error even when there was data to pull.-------------------------------------------------DTS ERROR with 'MSDAORA':Executed as user: AD\SVC.SQL. ...un OnStart: DTSStep_ProcessAllBatches DTSRun OnError: DTSStep_ProcessAllBatches, Error = -2147217900 (80040E14) Error string: OLE DB provider 'MSDAORA' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'MSDAORA' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7300 (1C84) Error string: OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80004005: ]. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7312 (1C90) Error string: [OLE/DB provider returned mes... Process Exit Code 1. The step failed.Query analyzer returned the same error if I selected from the Linked Server Oracle table with a WHERE clause, but not without a WHERE clause:Server: Msg 7399, Level 16, State 1, Line 18OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: ORA-01476: divisor is equal to zero] OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80004005: ].I tried creating a SQL Server view on the Oracle table. Again it worked in Development and Quality, but not production.Here is the view:-- --------------------------------------------------------CREATE VIEW PAT_ISProjAS SELECT I.EMP_NO AS EMP_NO, I.HOURS AS HOURS, I.HOUR_TYPE AS HOUR_TYPE, I.ENTRY_DATE AS ENTRY_DATE, I.COMPANY_NO AS COMPANY_NO, I.PAY_FREQUENCY AS PAY_FREQUENCY, I.EXEMPT_FLAG AS EXEMPT_FLAG, I.SOURCE_NAME AS SOURCE_NAME FROM ISPROJ..ISPROJ.ISPROJ_TIME_EXPORT_T AS IGO-- --------------------------------------------------------Here is the error:Server: Msg 7399, Level 16, State 1, Procedure PAT_ISProj, Line 28OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: ORA-01476: divisor is equal to zero]Obviously selecting with a where clause or creating a view where no division exists indicates that the error is something internal to processing, rather than a data error.-------------------------------------------------The DBA could find no server differences and DTC ping did not identify any issues.We then tried changing the provider, which changed the error we were getting, but still did not allow executing of the stored procedure, nor adding a basic view, but the where clause issue went away.DTS Error with provider 'OraOLEDB.Oracle':Executed as user: AD\SVC.SQL. ... Executing... DTSRun OnStart: DTSStep_ProcessAllBatches DTSRun OnError: DTSStep_ProcessAllBatches, Error = -2147217900 (80040E14) Error string: The operation could not be performed because the OLE DB provider 'OraOLEDB.Oracle' was unable to begin a distributed transaction. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 7391 (1CDF) Error string: The operation could not be performed because the OLE DB provider 'OraOLEDB.Oracle' was unable to begin a distributed transaction. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7300 (1C84) Error string: OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ITransactionJoin::JoinTransaction returned 0x8004d00a]. Error source: Microsoft OLE DB Pr. The step failed.-------------------------------------------------Query Analyzer Error (same thing happens with Create View):DECLARE @intRtn INTEXECUTE @intRtn = dbo.sp_ISPAT_SourceSELECT @intRtn AS SourceRtn(0 row(s) affected)Server: Msg 7391, Level 16, State 1, Procedure sp_ISPAT_LoadRemoteBatches, Line 42The operation could not be performed because the OLE DB provider 'OraOLEDB.Oracle' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ITransactionJoin::JoinTransaction returned 0x8004d00a].