Best Practices/Provider Connecting To An Oracle Database?
Jun 22, 2007
Are there generalized best practices with regards to which method/provider to use when accessing an Oracle database? I have used both the "Native OLE DBMicrosoft OLE DB Provider for Oracle" and the "Native OLE DBOracle Provider for OLE DB" and both seem to have their own quirks (requirement to convert to Unicode, etc) but I also have heard that I shouldn't be using an "OLE DB" source at all, but to set it up as an ADO .Net connection.
We are just beginning to implement SSIS, and are trying to establish Best Practices/Standards etc.
Are there any gotchas - performance and/or otherwise I should know about?
Thanks in advance!
View 1 Replies
ADVERTISEMENT
Sep 12, 2007
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
View 4 Replies
View Related
Jan 22, 2008
I've run into a bit of a database connection problem. I'm trying to connect to a SQL Server database. It compiles okay, but then while IE is loading, I get an exception error pointing out in the code saying 'Keyword not supported: 'provider'.
View 2 Replies
View Related
Oct 10, 2007
I have an ODBC connection setup for Oralce RDB. This works fine in say MSAccess. Now I would like to add a SQL Server Linked Server to this ODBC driver (it is for RDB not regular Oracle), but I am not able to get this to work. What is the Provider String and Location / Catalog for using this type of ODBC? I setup DSN as 'name', Provider String as 'UID=user;PWD=pass;' and it seems it tries to connect but gives an error. "%SQL-F-SCHNOTDEF, Schema USER is not defined"
Any help is greatly appreciated.
View 3 Replies
View Related
Mar 18, 2008
Help, I'm attempting to connect to an Oracle 8 database using the microsoft OLD DB for oracle driver.
It works fine connecting to a 9i database but when I attempt to connect to the 8 database I get ORA-12537 TNS Connection Closed.
I can connect to the database fine using sqlplus on the machine.
Any ideas would be appreciated.
Thanks
Stapsey
View 1 Replies
View Related
Nov 20, 2007
Hi all,
I need to connect with the Oracle Database for the Reports application. But i am unable to do the same. I am getting errors like ORA-12514,ORA-06401 while trying it.
(1) Please tell me about any extra settings need to be done in the listener.ora,tnsnames.ora for getting SSRS connected with the Oracle DB.
(2) Tell me the format of the connection string to be used for the connection with the Oracle database.
(3) Also which datasource has to used.. Oracle / Microsft OLEDB provider for Oracle for the Same.
Please help me with these issues.
With Thanks
M.Mahendra
View 4 Replies
View Related
Feb 22, 2006
Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
View 17 Replies
View Related
Aug 23, 2007
Hi everybody,
I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.
Please suggest me wht should i do know?
Thanks in advance
Regards
Arvind L
View 3 Replies
View Related
Jan 11, 2007
Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
View 3 Replies
View Related
Apr 10, 2008
Hi,I have SQL Server Express Edition. I tried working out some ASP.NET Labs in my local system. Here is the link of the Virtual Lab which I tried. http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032286906&EventCategory=3&culture=en-US&CountryCode=USI recieve this error in my local system. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)I tried working out solutions from various websites. But the no solution is effective. Could anyone help me in solving this issue.
View 3 Replies
View Related
Mar 5, 2007
Facts:1. I am using my account on Godaddy trying to connect to my sql server db.2. I can get into my db using user id and password.3. Godaddy's help file shows this for a connection string: connectstr = "Driver={SQL Server};SERVER=" & db_server & ";DATABASE="
&db_name & ";UID=" & db_username & ";PWD=" &
db_userpassword4. My connection string: "Server=whsql-v09.prod.mesa1.secureserver.net;uid=dbasolutions;pwd=***;database=DB_87972;" & _ "Trusted_Connection=False providerName=System.Data.SqlClient"{password **** out}5. Get error message An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) I tried to email Godaddy's help desk with this information. There first response: We do not allow remote connections to our sql server database.There second response was to read the help file!Does anybody have any suggestions? Thanks in advance,bswanson
View 1 Replies
View Related
Jul 15, 2001
Hi all,
If have faced the following problem when connecting to Oracle server from MS SQL Server 7.0 using OLE DB Provider for Oracle.
The problem is that I am trying to insert a record in a table in Oralce database, when I connect using OLE DB, and execute the INSERT, I have found that before the INSERT takes place, a TABLE SCAN is performed on the target table for the insert statement.
This realy affects the performance I am looking for.
I guiss there is a way to provide the connection with some parameters to overcome this problem of TABLE SCAN.
Thanks for your help in advance.
Regards.
Tareq Lootah
View 2 Replies
View Related
Feb 2, 2007
english:
Hello,
I have got a problem with the a linked server in SQL server 2005 (64 bit) to Oracle 8.0.5. (32 bit).
Further details:
The old existing SQL server 2000 (32 bit) has a linked server to Oracle 8.0.5. The works with the OLE DB provider from Microsoft. The new SQL server 2005 (64 bit) should also have a linked server to this Oralce database. I have installed the Oracle 8.0 clients on the new server. The connection to oracle works with the client tools from Oracle.
But the SQL server 2005 couldn't connect to the oracle database.
The error message from the SQL server sounds (translated form a german error message):
A instance of the OLE DB provider "OraOLEDB.Oracle" for the linked server "xxxxxx" can't create.
Is the Oracle client software / OLE DB provider the problem?
Exist an other OLE DB provider for Oracle, may be from Microsoft, which works?
Thanks for your help
german:
Hallo Zusammen,
Ãch habe ein Problem bei der Migration von SQL Server 2005 auf ein x64 Windows System.
Problem ist folgendes:
Auf dem alten Server Windows x32 lief SQL Server 2000 mit einem Verbindungsserver zum Oracle Server 8.05. Das funktionierte mit dem OLE DB treiber von Microsoft hervoragend. Nun haben wir aber eine neue Windows x64 Maschine und ich möchte die Verbindung zum Oracle Server wieder nutzen. Hatte die Oracle Client Software installiert und bekomme eine Fehlermeldung:
Eine Instanz des OLE DB Anbieters "ORAOLEDB.ORACLE" für den Verbindungsserver "xxxxx" kann nicht erstellt werden.
Ist das ein Problem mit der Oracle Client Software bzw. dem zugrunde liegenden OLE DB Treiber?
Gibt es einen anderen OLE DB Treiber für Oracle, z.B. von Microsoft, der funktionieren könnte?
Bin schon länger damit beschäftigt, das zum Laufen zu bekommen. Die Oracle Version ist nicht die neueste, ich weiß.
Was kann ich tun, damit das endlich läuft.
Für Hilfen wäre ich sehr dankbar.
View 9 Replies
View Related
Mar 2, 2008
connection manager
provider:
missing provider
oracle provider for ole db
i'm not able to see this provider in my ssis connection manager properties.
please help me out in getting the provider restored back in native providers options
View 4 Replies
View Related
Oct 26, 2000
Hi,
I issued the following set of statements against an Oracle 8.0.6 table linked to Microsoft SQL Server 7.0 (SP2):
BEGIN TRANSACTION
DELETE FROM ORACLE_DATABASE..SCHEMA1.TABLE1 WHERE COLUMN2=123.45
ROLLBACK TRANSACTION
and received the following message:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions.
I searched BOL and found this sentence:
To perform distributed transactions between SQL Server and Oracle, you must use Oracle version 8.0.4.1 or later.
What am I missing? Is there a higher version of Microsoft OLE DB Provider for Oracle that supports distributed transactions (we are using the one that comes with Microsoft SQL Server 7.0 installation)?
Maja
View 2 Replies
View Related
Dec 25, 2005
Hi,I created a linked server for MS SQL Server 2005 Express to an Oracledatabase using the OLE DB Provider for ODBC. My ODBC Source uses theMicrosoft ODBC for Oracle driver.I'm using the OLE DB Provider for ODBC instead of the Oracle OLE DBproviders because those don't handle Oracle's Numeric Data Type well.When I set this up yesterday it worked fine. However I log in today andattempt to run sp_tables_exec against my linked server and get thefollowingOLE DB provider "MSDASQL" for linked server "WHATEVER" returned message"[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".OLE DB provider "MSDASQL" for linked server "WHATEVER" returned message"[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".OLE DB provider "MSDASQL" for linked server "WHATEVER" returned message"[Microsoft][ODBC driver for Oracle][Oracle]".Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41Cannot initialize the data source object of OLE DB provider "MSDASQL"for linked server "WHATEVER".I can tnsping the server, and I have no trouble connecting with theOracle OLE DB providerI try to set up ODBC logging but the log file is blank -- same withattempting to set up logging for Oracle's network client. I've beendealing with crap like this the last 4 days, something works one day,then the next nothing (I've done nothing to change the configurationbetween these outages).Can anyone help me?ThanksFrustrated
View 1 Replies
View Related
May 31, 2007
I have been using the Microsoft Oracle Provider (MSDAORA) up until I needed to work with CLOB data types in Oracle. As much as I hate to switch providers at this phase in the project, I can't use MSDAORA due to the CLOB limitation.
So, what other providers are available?
I know that there is a native Oracle provider (OraOLEDB.Oracle.1) that is supported in SSIS. Does anyone have any comments on this?
Are there other options for Oracle?
Any comments, feedback, etc appreciated.
Thanks,
Rick
View 3 Replies
View Related
Jan 9, 2008
Hi,
how do you pass parameters into a SQL statement to run in an Oracle database when you use the OracleClient Data Provider?
thanks in advance
Peter
View 3 Replies
View Related
Dec 8, 2006
I am using SQL Server 2005 on a 64 bit AMD processor machine using the Oracle 10.0.2.0.2.0 and SELECT OPENQUERY will return results from my Oracle table. When I try to do an UPDATE OPENQUERY I get this error message
Msg 7333, Level 16, State 2, Line 1
Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server "ORADB".
the same UPDATE OPENQUERY works on SQL Server 2000 using the Oracle 9 client. The provider is configured with Inprocess turned on. Any help would be appreciated.
View 7 Replies
View Related
Nov 8, 2007
Hi. I am trying to establish a connection from ssis to oracle using oracle provider for ole db on a 64 bit windows box using the 64 bit oracle provider.
Steps done
1) installed the oracle 10g 64 client
2) installed the odac 64 bit
I am able to connect to oracle using sqlplus fine. i can also see oraOLEDB.oracle listed as a provider in the linked server providers in sql server management studio.
when i try to set up a new data connection in ssis I don't see the oracle ole db provider listed. Since then I have registered the OraOLEDB10.dll but I still don't see the oracle provider listed.
Can some one through me a line ? Thanks.
NP
View 4 Replies
View Related
Mar 7, 2008
I wrote a few SSIS packages a while ago that pulled data from an Oracle database and saved them into a SQL database. I had to use the AlwaysUseDefaultCodePage = True to prevent warnings at that time. All of the varchar2 fields were seen as non-unicode (DT_STR) on the OLE DB Source by SSIS.
When I recently moved to Vista and setup the 10.2.0.3 Vista Client for Oracle, I started getting errors from these OLE DB Sources. Now SSIS thinks they should be unicode (DT_WSTR), however; once I make this change and re-deploy to the Win 2003 server they fail on the validation check and report that non-unicode cannot be converted to unicode.
I would really like to move to Vista and not keep an XP computer around to do my SSIS development. If anyone has any ideas it would be great.
Thanks in advance!
View 9 Replies
View Related
Mar 16, 2007
Guys,
I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.
The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:
The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009
After this happens, if I go into an OLE DB Source within a DFT, I get the following:
No disconnected record set is available for the specified SQL statement.
Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:
The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009
The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:
1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.
2. Hardcode the connection string in the dtsx XML-behind.
3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.
4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).
5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.
Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.
Thanks,
Rick
View 3 Replies
View Related
Apr 24, 2008
I've read al lot of the post on this one. Mine seem very simiular.
I create the SSIS package with the wizard and save sensitive data with password. When the SSIS wizard completes and executes the package everything works. However when I attempt to execute the package with dtexec it fails with "
Source - Query [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. "
This even happens also when I attempt to run the package in SSIS after it was created. However in SSIS designmode I can open my query source , parse the query, and prview the data. However when I attempt to execute the package no dice. I'm sure this is a password issue but I have now idea where to fix it.
FYI,
Have tried the following.
SourceConnectionOLEDB check option Allow saving of passowrd
Package Exlporer / Connection Mangers/ SourceConnectionOLEDB shows password save with ******
Have Enabled package configurations with a XML file all values save to file.
Thanks,
Z
View 4 Replies
View Related
Aug 3, 2006
Oracle recently released Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20. It happened on August 1, 2006. Oracle claims that its software is compliant with Microsoft ADO.NET 2.0 and €œ€¦more flexible, faster, and more stable€¦.€?. On top of it Oracle introduced many new features €œ€¦ not available from other .NET drivers€¦€?.
http://www.oracle.com/technology/tech/windows/odpnet/index.html
I decided to give it a shot and try in SSIS.
I installed ODP.NET on my machine and it works just fine in PL/SQL Developer
Next, I opened Data Source Wizard in BIDS for SSIS project.
I see Oracle Data Provider for .NET option is enlisted under .NET Providers node on a tree. But when I select it, I am getting an error dialog:
Failed to find or load the registered .Net Framework Data Provider.
So what could be done in such scenario?
Regards,
Yitzhak
View 5 Replies
View Related
May 16, 2007
I am trying to use the Execute SQL Task to execute a very simple batch against the Oracle database:
Code SnippetTRUNCATE TABLE FactTable1;
TRUNCATE TABLE FactTable2;
TRUNCATE TABLE FactTable3;
TRUNCATE TABLE FactTable4;
TRUNCATE TABLE FactTable5;
I have an Oracle Connection Manager configured to source off of an Oracle 9i database. I have tried both the Microsoft OLEDB provider for Oracle and the native Oracle provider.
When I run the above statement in SQL Plus or SQL Workbench, it works just fine.
In fact, it works in SQL Plus and SQL Workbench like this as well:
Code Snippet
TRUNCATE TABLE FactTable1
/
TRUNCATE TABLE FactTable2
/
TRUNCATE TABLE FactTable3
/
TRUNCATE TABLE FactTable4
/
TRUNCATE TABLE FactTable5
Although both of these would introduce the need to probably do some expressions to add the ";" or "/" conditionally only if the target is Oracle (we are *trying* to have one package support both SQL and Oracle) neither works. I get the following error message from SSIS command window:
...failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Can anyone please provide some guidance on how to accomplish this with the Execute SQLTask task, taking into consideration that I very much generalize on the Oracle side?
Many thanks,
Rick
View 6 Replies
View Related
Nov 23, 2006
Hi,
Previously i was using oracle8i with ssis.ssis was working fine.later i have upgraded 8i to oracle 9i.now when ever i try to establish a new connection using Microsoft OLEDB Provider for Oracle.i am getting the following error "Oracle error occurred, but error message could not be retrieved from Oracle" .but i am able to access the oracle 9i database thru pl/sql and toad. what could be the problem?
Thanks
Jegan.T
View 2 Replies
View Related
Dec 1, 2007
Hi,
I need to get ORACLE client drivers on a 64 bit SQL Cluster.
Where should the 'oracle' directory be, local disk or shared?
Is it necessary to run the install on all nodes?
Thanks!
View 3 Replies
View Related
Jan 23, 2001
Trying to used DTS from SQL Server 7 to import a table from Oracle 8 DB to SQL Server. SQL Server has Oracle 8 connectivity installed and a database instance setup. Connection using the instance works from Oracle Enterprise manager and all Oracle tools. The MS OLEDB provider for Oracle fails to connect : could not resolve service name. I have this working on a different server with the same setup (SQL Server 7 SP 2 + All Oracle 8.0.5 connectivity).
WHAT AM I MISSING?
View 1 Replies
View Related
Sep 18, 2007
Windows 2003(64bit) server, SQL 2005 Server(64bit), Oracle client 10G rel2 (64bit) is installed. But when I am going to create a datasource to the Oracle database the Oracle OLEDB Provider is missing, the only Providers avaliable is from Microsoft.
I have tried to install ODAC (64bit) with no result.
Anyone who have come across this problem and how do I do to resolve it?
BTW! Is there a way to try communication with the OLEDB provider from the commandprompt?
View 2 Replies
View Related
Dec 3, 2015
Oracle linked server. If I query Oracle in my packages using SQL it tooks age if I use an OLE DB Oracle connection it takes minutes, so I started to develop my packages using the OLE DB Oracle. The point is that in order to connect the Oracle db I must set up a password (Oracle requires a password but if I use directly SQL it doesn't need any password). This password changes every month so I do need to set up the password for...if I can come up with another solution? I don't know, maybe a unique connection...just set up the password only once.Â
View 2 Replies
View Related
Jan 16, 2007
Hi all,
I am using MSSQL2005 and created a linked server with Ole DB provider for ORacle to connect to Oracle.
sp_tables_ex linked_server
It shows the tables in oracle, however, it show error with selecting the oracle tables via the linked server,
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA'
reported an error.
[OLE/DB provider returned message: Unspecified error] [OLE/DB provider returned message: ORA-03113: end-of-file on communication channel ] OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBSchemaRowset::GetRowset
returned 0x80004005: ].
Any Idea? Thanks a advance.
View 1 Replies
View Related
Oct 9, 2007
Which one is better to use? Is there a significant difference between the two?
Does someone have the directions to add ODP.Net to my database connections list in SSRS?
Thanks
View 1 Replies
View Related
