MS SQL Server :: Error Connecting To Oracle DB When Executed Within SQL Job

Connecting To Oracle DB (connection Error)

Feb 6, 2007

While upgrading several packages from DTS 2000 to SSIS I came across a couple of packages where the source data comes from an Oracle DB. Until now I have not migrated data from an Oracle source.

When I tried to create a new connection I got the error:
"System.Data.Oracle client requires Oracle client software version 8.1.7 or greater."

This is being developed on a machine that would not have any oracle drivers before now, so I THOUGHT it would be as simple as downloading the drivers, but the resolution seems to be much more complex than that. When I researched it, it appeared that I needed to download the Oracle 9i software. The installation is about 10 GB and this seemed like overkill to just be able to connect to a database.

Is this in fact the correct solution? If so, or even if not, can I get a quick run down of the steps involved, and anyone's experience (problems, etc..) on the topic?

View 5 Replies View Related

OleDb Source Error - Connecting To Oracle 9i

Nov 14, 2006

Hi,
I have my datasource in Oracle9i and destination is Sql2005. I am connecting to Oracle 9i through OLEDb provider and when I connect to my DataSource using OLE DB Data Source, I get the following error:

Warning at "guid code": Cannot retreive the column code page info from the oledb provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale Id will be used.

Could anyone please help me trouble shoot this problem.

Regards,
Sudhakara.T.P.
sudhakaratp@hotmail.com

View 3 Replies View Related

Issues Using Parameterised Reports Connecting To Oracle Using ODBC And Microsoft OLE DB Provider For Oracle

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

Connecting To Oracle

Jun 28, 2006

Hi,

I want to connect to Oracle from MS SQL Server.

Is it possible?

If yes, please guide me in this issue.

SALIH KM

View 2 Replies View Related

Connecting Oracle To SQL 2000?

Jan 28, 2005

Hi, i need to build some way of connecting Oracle to SQL Server in order to pull data off Oracle and house it temporarily on SQL 2000 for searching.

Basically we've built an intranet using ASP and SQL 2000 and which also pulls data off Oracle, however our connection to Oracle is very slow so an idea to speed it up would be to have SQL store a temporary snapshot of this data and the ASP site query off the fast connection it has to SQL rather than use our slow connection to Oracle.

This temporary snapshot of Oracle data would be stored on SQL 2000 and updated on a scheduled basis.

Any ideas how to do this, or a better way to work this?

Thing is I tried installing the Official Oracle ODBC & Client software on our SQL 2000 server before and it just took out SQL 2000 so would really like to hear other options.

Thanks in advance,

:confused:

View 2 Replies View Related

SSIS - Connecting To Oracle

Jan 19, 2006

Hi,

I am new to using SSIS (after using DTS on SQL Server 2000 for many years).

I am trying for the first time to connect to an Oracle 9.2 database and export data from it into SQL Server 2005.

I used the Microsoft OLE DB Oracle Provider to connect to the Oracle instance. The test connection works fine, but when I try and preview a table or copy data from Oracle I get the error....

The component reported the following errors

Cannot retrieve column code page

Has anyone got any ideas how I fix this?

Many thanks

Pete

View 11 Replies View Related

Connecting To Oracle RDB From SSIS

Oct 24, 2006

Hi Gurus,

I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.

Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.

Provider is unable to funtion until these components are installed.

I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.

Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)

Thanks,
SK

View 8 Replies View Related

Connecting To Oracle DB Using SSIS

Jul 7, 2006

Can anybody point me how to connect to an Oracle DB using SSIS? I use the ole DB Connection Manager and pick the Microsoft OLE DB Provider for Oracle but it doesn't seem to work. Any suggestions are greatly apprecaited.

Thanks.

Sam.

View 9 Replies View Related

Connecting To Oracle On 64-bit (x64) Machine

Dec 6, 2005

Hi,

View 81 Replies View Related

Question On Connecting To Oracle

Jan 11, 2006

Hello all,
I'm using a OLE DB Connection Manager to Oracle (I've installed the client) and using Oracle Native OLE DB Provider.

The problem was the view In Oracle I'm returning a column that uses a fuction on a numeric field in the the select part of the statement, and by this, the Precition is not displayed when I'm trying to look at the data types of the fields in the view.
The Precision is shown as 0 in the external field part of the component.
I have read the previous post concerning the problem connecting to Oracle Using the Oracle provider, and solved it by using the Microsoft provider, but still I have a question about the oracle provider:
I changed the precision on the External column (from 0 to 15) and also an the Output column, but now I'm getting an error on about error output not matching:
Error 6 Validation error. Data Flow Task: OLE DB Source 1 [8970]: The output column "UPDATE_TIME_NUM" (9333) on the non-error output has no corresponding output column on the error output. Dim_registered_user.dtsx 0 0
I cannot change the error output datatype, cannot delete it or do anything at all.
Is there a way to fix the problem?

View 2 Replies View Related

Problem With SCD Connecting To Oracle

Aug 18, 2007

Hello Everyone,
I��m getting a problem in SCD connecting to oracle DB.

I had set the OLE DB Input Output Properties Tab
And mapped the columns in Column Mappings Tab
Every thing is taken care according to Type 2 load
But every time I run the job its inserting the records rather than updating it or ignoring if not find any new or changed records from source.

Can any one help me in sorting this issue.

Please.

View 1 Replies View Related

Same DTS Fails Executed As Job ,but Run Fine When Executed From DTS Designer

Mar 13, 2002

I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months)
2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!

1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin)
2. SVCSQL service use DomainSVCSQL2000 to run
3. SVCSQL agent use DomainSVCSQL2000 to run
4. DTS use 'osql -E

Where should look for reference to sa ?

Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.

View 5 Replies View Related

Connecting SQL Server With Oracle Forms 9i/10g

Dec 7, 2007

Could anyone please guide me in detail to connect Oracle Forms 9i/10g with MS SQL Server database?

To connect with Oracle Database, we need to copy and paste the tnsnames.ora file from the Oracle Database folder to the Oracle Forms folder.

But how do i connect with other databases like SQL Server, MS Access, and so on?

Thanks in advance.

View 6 Replies View Related

Connecting SQL Server With Oracle Forms 9i/10g

Dec 7, 2007

Could anyone please guide me in detail to connect Oracle Forms 9i/10g with MS SQL Server database?
To connect with Oracle Database, we need to copy and paste the tnsnames.ora file from the Oracle Database folder to the Oracle Forms folder.
But how do i connect with other databases like SQL Server, MS Access, and so on?
Thanks in advance.

View 3 Replies View Related

Connecting To Oracle 8 Database Using OLEDB

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

Connecting To Oracle Datasource For Import

Nov 29, 2006

Now this is the first time i have tried this with SQL 05 I am trying to Import data from an Oracle DB. I have the connection info:

Xtx20xxx = (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =urlpart1.part2.part3.com)(PORT = 1521)))(CONNECT_DATA =(SID = Xtx20xxx ))
)

I used the Microsoft OLEDB provider for oracle from the dropdown,

The username and password I have also. I tried to enter in the Xtx20xxx as the server name, clicked "Properties"

put in the username and password I can given and clicked "Test Connection". No matter what I get the infamous ORA-12154: TNS:could not resolve service name error message. I am sure I am missing something obvious here but I have absolutely no clue and all the googling in the world has not given me any help. I do have the Oracle 10g client tools installed on this workstation (running Windows XP Prof SP2 and SQL 2005 STD).

View 5 Replies View Related

Connecting SSRS With The Oracle Database

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

Connecting To An Oracle Databse Server Using SQL Express

Jan 10, 2006

Hello everyone,

I am working on a project for a professor. The project entails
designing and creating a database. We
were given individual space on the schools database server and we were given
SSH secure shell to connect and manage our respective databases. I took it upon myself to try out SQL Server
Express. Using the management studio I
am trying to connect to my school database and can��t seem to figure it
out. Can someone please give a noob some
simple and quick instructions on how to achieve this?

Thank you,

Your Friendly Neighborhood Programmer,

This is the SSH product in case any one was interested, ftp://ftp.ssh.com/pub/ssh/SSHSecureShellClient-3.2.9.exe

View 1 Replies View Related

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 View Related

SSRS 2005 Connecting To Oracle Problem

Feb 29, 2008

I'm using Reporting Services 2005 and I can view the report in Visual Studio using the preview tab. After I deploy the report and datasource, when I try to view the report on the report server is says that I must have Oracle Client version 8.xx or higher.

I have the same Oracle Client Version on my local machine and the Report Server with the same DataSource name set up for Oracle on both machines.

Any ideas why this would work on my local machine but not the Report Server?

View 1 Replies View Related

Connecting To Oracle From Windows 64bit IA Server

Jan 23, 2007

I am trying to pull data from an Oracle 9i database to my SQL Server 2005 sp1 database on Windows 2003 64bit Itanium server. Each time I try to use the import wizard, select the Microsoft OLE DB driver for Oracle, I get an error message telling me that the Oracle client tools are not installed. This happened after I had already installed the 9i tools, tested the connection using TNSPING and confirmed the tnsnames file was correct. So, I uninstalled the 9i tools and then installed the 10g client tools. Tested everything after the install and then tried the import again selecting the MS OLE DB driver for Oracle. I again got the error message that the Oracle tools were not installed.

has anyone ran into this problem and if so, were you able to fix it?

View 2 Replies View Related

Oracle Publication Error:The Permissions Associated With The Administrator Login For Oracle Publisher 'test1' Are Not Sufficient

Jan 12, 2006

Hi,

I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.

from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->

the oracle publisher is displayed in the list of publisher but when press ok i got the following error :

TITLE: Distributor Properties
------------------------------

An error occurred applying the changes to the Distributor.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Any idea about this error ?

Thanks

Tarek Ghazali
SQL Server MVP.

View 2 Replies View Related

DTS Execution From Client Machine Fails Connecting To Oracle

Jul 20, 2005

I have created a DTS package that pulls data in from Oracle into SQLServer. When I run it directly on the server (from EnterpriseManager), it works fine. When I run it from Enterprise Manager on aclient machine that does not have the Oracle client software, it doesnot run, giving me the error: "The Oracle client and networkingcomponents were not found...". I was hoping I wouldn't need to havethe client software installed on a machine other than the server whereSql Server is running. The problem here is that there are a number ofmachines from where I would like to execute this DTS package that I donot want to install/configure the Oracle client software. I don'tquite understand why the configuration of the client is importanthere. In my mind, when I am using enterprise manager from a clientmachine, I am using it sort of like a terminal services client toconnect to the server. I guess there is a lot more happening in thebackground.Thanks for any feedback,Marcus

View 2 Replies View Related

Connecting To Oracle 9i Server Using ODBC From An SSIS Package

Sep 7, 2006

I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.

I have tried OLEDB connection and that does not work either.

Any inputs will be appreciated.

Thanks,

Monisha

View 6 Replies View Related

Getting Error Context From Executed Package

Aug 7, 2006

Background:

I am executing a package programmatically from ASP.NET using C#. The package is being loaded from SQL Server at runtime and then it is executed from a custom class written in C#.

Problem:

When a task fails, I'd like information on why it failed. I read about implemeting the IDTSEvents interface as a way to have my package call back to code. This works fine (I have code in the OnTaskFailed event handler), but I'm not sure how to find out why a task failed. Since my task is loading a flat file to SQL Server, failure will likely come in one of two flavors: either the file format will be wrong or the data will violate a database constraint. In etiher case, I'd like that feedback. I am looking that the TaskHost object that gets passed to my event handler and I haven't figured out how to access this information. Am I going about this the right way? If yes, how do I access the error information I am looking for?

View 2 Replies View Related

Problem Connecting To Oracle Using Sql Server 2005 Reporting Services

Sep 3, 2007

Hi,
I'm new to SSRS but have a problem connecting to Oracle. I have placed my reports upon a reporting server but the shared data connection isn't working and I'm confused as to why. I have specified the name, connection type as "Oracle", set the correct user id and password for the credentials and the connection string as for example 'Data Source=oracleExample;Unicode="True"'. I also have set the correct entry in the tnsnames.ora file for this datasource, example:
"oracleExample =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 12.23.45.23)(PORT = 3425))
)
(CONNECT_DATA =
(SID = oracle)
)
)"
Unfortunately I receive the following error when trying to connect.
"

An error has occurred during report processing.

Cannot create a connection to data source 'oracleDataSource'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

"

I'm mystified as to what the issue is. Any ideas? Would restarting the report server pick up the tnsnames.ora file? I have this working locally using report designer.
Any help, much appreciated.

View 4 Replies View Related

Problems Connecting From Reporting Services To Oracle Using Odbc Connection And Asp.net 2.0

Sep 5, 2007

Hi,

Anybody have a clear idea why I might have problems connecting from reporting services to Oracle 9i database using the Microsoft ODBC driver? Getting following error
"Cannot create a connection to data source 'oracleName'.

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

"

I have the data source correctly set-up thought. Is it a permissions problem?

Thanks.

View 1 Replies View Related

ISSUE WHILE CONNECTING TO ORACLE SOURCE WITH 64 BIT Processor SQL SERVER SSIS

Nov 1, 2006

Hello All,

I have a unique problem while connecting to oracle source with a 64 bit processor. I can connect to the oracle from the command prompt in the 64 bit processor but not from SSIS.

The acutal problem is, when check the properties of the connection manager and provide a provider for oracle, and then provide username and password and click on test connection. I get the following error:

"Test Connection failed because of an error in initializing provider.ORA-06413: Connection not open"

Regards,

Raju

View 3 Replies View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

May 26, 2008

Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View 4 Replies View Related

Begin Catch Block Executed When Try Completes Without Error

Dec 10, 2007

I was just debugging a stored procedure visual studio and I was surprised with what I was watching.

I'm running SQL 2005 and visual studio 2005. I have a set of nested try/catch blocks.

It fails on the first try and drops into the catch block just as it should. Within the catch block a dynamic sql statement is created. A try block begins and attempts to exec(@sql). It then drops to the catch block. However, I copy the statement into SQL Management Studio and it runs without error everytime.

Why is it dropping into the catch block? Logically it should just complete executing the statement continue without going into the catch block.

Do I have to reset the raiserror values between try blocks or something?

--Thanks--

View 1 Replies View Related

Oracle Error ORA-12154 From An Application Which Never Uses Oracle

Mar 14, 2008

My application which ran perfectly well yesterday suddenly stopped working in this morning with following error message. I didn't change anything, at least I believe. Interesting thing is that I don't use any Oracle connection but connects to MS SQL Server 2005 Express version locally. Error messages are different according to the connection string though I don't think it makes difference:
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;SSPI=true" />ErrorMessage from VS2008 ="Unable to get records. Object reference not set to an instance of an object."
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;User Id=handtool_DB;Password=mypassword" />ErrorMessage from VS2008 = "Unable to get records. Unable to connect to database. ORA-12154: TNS:could not resolve the connect identifier specified." By the way, the application was generated from IronSpeed 5.1 Enterprise. My development environment is : VS2008, SQL2005 EXPRESS, Windows XP SP2, ASP.NET 2.0
We have actually an Oracle server in the network though. My gut feeling is that something might have changed in my network server during the night but how come it can possibly affect my application running on my local PC. I checked the connection of local SQL Express Server from SQL Server Management Studio and it connected well with UserID=handtoo_DB and showed all tables and stored procedures required for the application. So there is no problem in SQL Server database side.
I would welcome and appreciate any input.
fudata

View 1 Replies View Related

Need Help --&&> Connecting To Oracle Throught SSIS Package(Failed To Decrypt An Encrypted XML Node Because The Password Was Not S)

May 14, 2007

Hi,

In BI Tool SSIS Packages run fine and get data From Oracle and Save it in SQL Server.

Package Protection Level is EncryptSensitivewithPassword.

In BI tool when i open the package it ask password and then run fine.

If i change the Protection Level to Dont save Sensitive,

It does not run fine in even BI tool.

It is fine if i use EncryptSensitivewithPassword.in BI Tool and run it.

Now the problem is that i need to run this package through SQL Job.

so Job give error

"Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information."

Please i need help ?

Thanks

View 7 Replies View Related