Oracle DBA to learn MS SQL: windows

Showing posts with label windows. Show all posts

Monday, March 19, 2012

Oracle10 ODBC connection

I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures this
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and no
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie
|||"Arnie" wrote:

> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i

> - Arnie
>
>

Oracle10 ODBC connection

I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures t
his
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no
default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get."HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and n
o
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie|||"Arnie" wrote:

> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i

> - Arnie
>
>

Oracle/Unix Environments --> SQL Server 2005/Windows 2003 Server

I was a Oracle Developer / DBA on Unix Environments all along my career, Very recently iam starting to manage a SQL Server 2005/Windows 2003 Server setup.

Part of my new job is to automate to load Huge Data files/Flat Files (3/4 GB in size) into SQL Server 2005 DB.

Have these initial questions..
Since the files are too large to open at once... What sort of Command Line Interfaces people use on the Windows Boxes.. like doing a "wc" (Word Count) / GREP 'ng Files / Massaging Data Files one line at a time (Like using SED / AWK Commands).. Etc

Any Input / Direction is appreciated...

The data load took for SQL Server is called BCP. It's a command line tool.

The typical practice would be to load all the data into a holding table, then massage it within SQL with T/SQL code.

When working with data inside of SQL Server don't forget that SQL Server doesn't like cursors like Oracle does. Do as much as possible with SET based commands.

|||Thanks for your response..

We are using SSIS as our Data Loading Tool for now..
However, if you were to look at the last record in a HUGE data file.. How do you do that in Windows Environment.

Something like.. In Unix..
tail 100 datafile.dat
head 200 datafile.dat
grep "findstring" datafile.dat

How are the above functionalities achieved in a Windows DB Environment in general?
|||

Something like.. In Unix..
tail 100 datafile.dat
head 200 datafile.dat
grep "findstring" datafile.dat
How are the above functionalities achieved in a Windows DB Environment in general?

I google for tail.exe head.exe and grep.exe and do it in the same method. The nice folks at unxutils.sourceforge.net have ported all your favorite unix utilities to DOS commands so that you can use them in Windows. We do like to try to make the transition from Unix to windows as painless as possible.

Oracle to SQL Server Transactional Replication Error

Hi,

I'd appreciate any help i can get here. I'm working with an MS SQL Server 2005 SP2 db on a Windows 2003 OS. I set up replication from an Oracle 10g instance a few months ago, and everything was working until i installed SP2 for SQL Server.

I get the following error:

2007-06-06 16:06:22.488 Status: 4096, code: 20024, text: 'Initializing'.

2007-06-06 16:06:22.488 The agent is running. Use Replication Monitor to view the details of this agent session.

2007-06-06 16:06:23.082 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.

2007-06-06 16:06:23.082 ORA-01008: not all variables bound

2007-06-06 16:06:23.082 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.

I can get snapshot replication to work just fine, but transactional replication throws up the error above.

Anyone with any ideas on how i can proceed?

Thanks in advance!

Regards,

Rama.

Some additonal log info:

2007-06-06 17:46:26.492 Microsoft SQL Server Log Reader Agent 9.00.3042.00
2007-06-06 17:46:26.492 Copyright (c) 2005 Microsoft Corporation
2007-06-06 17:46:26.492
2007-06-06 17:46:26.492 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-06-06 17:46:26.492 User-specified agent parameter values:
-Publisher TCADEV
-Distributor ATDSEADSQL1
-DistributorSecurityMode 1
-Continuous
-OutputVerboseLevel 4
2007-06-06 17:46:26.571 Parameter values obtained from agent profile:
-pollinginterval 5000
-historyverboselevel 1
-logintimeout 15
-querytimeout 1800
-readbatchsize 500
-readbatchsize 500000
2007-06-06 17:46:26.571 Connecting to OLE DB Publisher at datasource: 'ATDSEADSQL1', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI'
2007-06-06 17:46:26.571 OLE DB Publisher: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.196 Status: 4, code: 22261, text: 'Request to publisher TCADEV: select DISTRIBUTOR_PUBLISHERNAME, DISTRIBUTOR_DISTRIBUTORNAME, DISTRIBUTOR_DISTRIBUTIONDB, DISTRIBUTOR_STATUS, DISTRIBUTOR_PRODUCTVERSION FROM MSSQLSERVERDISTRIBUTOR '.
2007-06-06 17:46:27.196 OLE DB Publisher: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.196 OLE DB Publisher 'ATDSEADSQL1': select @.@.SERVERNAME
2007-06-06 17:46:27.196 Connecting to OLE DB DISTOLE at datasource: 'ATDSEADSQL1', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI'
2007-06-06 17:46:27.211 OLE DB DISTOLE: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.211 OLE DB DISTOLE: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'TCADEV')
2007-06-06 17:46:27.211 OLE DB Publisher 'ATDSEADSQL1': sp_MSgetversion
2007-06-06 17:46:27.211 Status: 4096, code: 20024, text: 'Initializing'.
2007-06-06 17:46:27.211 The agent is running. Use Replication Monitor to view the details of this agent session.
2007-06-06 17:46:27.211 Repl Agent Status: 3
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': sp_MSget_last_transaction @.publisher_id = 2, @.publisher_db = N'distribution', @.for_truncate = 0x0
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': sp_MSquery_syncstates 2, N'distribution'
2007-06-06 17:46:27.211 Status: 4, code: 22262, text: 'Request to distributor ATDSEADSQL1: begin declare @.match int, @.retcode int execute @.retcode = sys.sp_checkOraclepackageversion 'TCADEV', @.match OUTPUT if (@.retcode <> 0 or @.@.error <> 0 or @.match = 0) select 0 else select 1 end'.
2007-06-06 17:46:27.227 Status: 4, code: 22262, text: 'Request to distributor ATDSEADSQL1: begin SET DEADLOCK_PRIORITY LOW exec sys.sp_IH_LR_GetCacheData 'TCADEV' end'.
2007-06-06 17:46:27.242 Status: 4, code: 22261, text: 'Request to publisher TCADEV: select Publisher_Name, Publisher_ID, RAWTOHEX(Publisher_LSN), Publisher_HasChanges, Publisher_PollInProcess, Publisher_SyncDones, Publisher_InReconciliation, Publisher_SyncInits, Publisher_RowCntValidations, Publisher_TableChanges, Publisher_XactSetBeginTime, Publisher_Timestamp, Publisher_GUID, Publisher_TracerTokens, Publisher_CurrentPollid from HREPL_Publisher'.
2007-06-06 17:46:27.242 Status: 4, code: 22261, text: 'Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq'.
2007-06-06 17:46:27.242 Status: 0, code: 22261, text: 'Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq'.
2007-06-06 17:46:27.242 Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq
2007-06-06 17:46:27.242 Repl Agent Status: 6
2007-06-06 17:46:27.242 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.
2007-06-06 17:46:27.242 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.
2007-06-06 17:46:27.242 Disconnecting from OLE DB DISTOLE 'ATDSEADSQL1'
2007-06-06 17:46:27.258 Disconnecting from OLE DB Publisher 'ATDSEADSQL1'

|||yeah, it's probably an upgrade bug somewhere, let me see if there are any easy workarounds, if not you might have to start from scratch or call CSS.|||

Before we say it's an upgrade bug can you tell us what happened up to the point you saw the failure? Can you also answer the following questions:

1. At any time, was there any failures during upgrade?

2. At any time immediately before or after upgrade, were there any other logreader agent errors?

3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?

|||

Hi Greg,

Thanks for your time. As for the sequence of events, I set up the Oracle Publisher, Distributor and Publication and the error popped up. No Subscriptions up yet. I noticed that the data is actually copied from Oracle into the replication folder. I can see the .bcp, .idx and .sch files and the .bcp file has records in it.

1. At any time, was there any failures during upgrade?

No failures, the upgrade went through smoothly.

2. At any time immediately before or after upgrade, were there any other logreader agent errors?

Well, i wouldnt know. i basically deleted the existing replication, upgraded and then attempted to get replication going. This means everything in the set up is new - the Oracle publisher, Distributor (plus db) and Publication.

3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?

I did not stop the writes, so there may have been writes happening. But the writes would have aborted when SP2 stopped the instance at some point. But that shouldnt be a factor - i'm setting replication up from scratch.

Regards,

Rama.

|||

Hi,

As a workaround, i've implemented an ETL job that periodically transfers data from Oracle into SQL Server. This works for the time being, but i dont think my tech lead will be happy without the real time replication between the databases.

I'd appreciate any help i can get.

Regards,

Rama.

|||I'm stumped with your issue especially since you started from scratch, it looks like you may have to call CSS and open a support case to dig deeper into the problem. If I find anything new, I'll post here.|||

Hi Greg,

Thanks. I may not have a solution, but i at least have a way forward. The problem's on my development box (I work for Intel by the way), so i'm not sure what kind of mileage i have. Is there a standard procedure i should follow in contacting CSS? I am sure Intel has some support arrangement with Microsoft, but how do i even begin?

Regards,

M. Ramanathan

Oracle to SQL Server Transactional Replication Error

Hi,

I get the following error:

2007-06-06 16:06:22.488 Status: 4096, code: 20024, text: 'Initializing'.

2007-06-06 16:06:22.488 The agent is running. Use Replication Monitor to view the details of this agent session.

2007-06-06 16:06:23.082 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.

2007-06-06 16:06:23.082 ORA-01008: not all variables bound

2007-06-06 16:06:23.082 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.

I can get snapshot replication to work just fine, but transactional replication throws up the error above.

Anyone with any ideas on how i can proceed?

Thanks in advance!

Regards,

Rama.

Some additonal log info:

|||yeah, it's probably an upgrade bug somewhere, let me see if there are any easy workarounds, if not you might have to start from scratch or call CSS.|||

Before we say it's an upgrade bug can you tell us what happened up to the point you saw the failure? Can you also answer the following questions:

1. At any time, was there any failures during upgrade?

2. At any time immediately before or after upgrade, were there any other logreader agent errors?

3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?

|||

Hi Greg,

1. At any time, was there any failures during upgrade?

No failures, the upgrade went through smoothly.

2. At any time immediately before or after upgrade, were there any other logreader agent errors?

3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?

Regards,

Rama.

|||

Hi,

I'd appreciate any help i can get.

Regards,

Rama.

Hi Greg,

Regards,

M. Ramanathan

Friday, March 9, 2012

Oracle OLEDB in SSIS - problem with 64bit

I have a 64bit Windows 2003 Server with SQL 2005 (64bit) installed. I would want to fetch data from an Oracle database that resides on a different server and load it into SQL2005 through SSIS. This is part of migration from SQL2000.

I have tried to run Oracle client 10G (64bit) with no success, since SSIS is a 32bit application.

I uninstalled the 64bit Oracle client and installed the 32bit version and can create an Oracle OLEDB Provider as Connection Manager and preview the data from the Oracle source, but when I run the package it displays an error almost directly and the transfer stops.

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "BOFLEV.bofinc" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

I have tried to change the path for SSIS from Program Files (x86) to ProgramFilesx86 with no luck.

I have changed the Run64BitRunTime to false.

Is there anyone that has managed to resolve this, I really do not want to use a third party software for this!

SSIS has both 32-bit and 64-bit flavors. The dev environment (BIDS) only uses the 32-bit version, though.

Can you connect to Oracle through the 32-bit driver from any other application (like Access)? If not, it sounds lile the driver installation is corrupt.

|||

Yeah we managed to get this one sorted after much searching.

Be very careful with the version of oracle 10g you are using, if memory serves me right it needs to be 10.2..0.1.8 or higher, it is not sufficient to have only 10g 64 bit, it must be patched also.

We used the microsoft oracle driver for the 32bit development of the packages using BIDS, we used configurations for the connections thus allowing us to change the provider type from msdaora to oraoledb.oracle.1 for when we transfered and scheduled the package on the SSIS 64 bit. So far we have used this scenario for about 80 packages without fail.

Regards

John

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

God morning!
I 've just installed SQL Server 2005 on Windows 2003 Server 64 bit. One of my databases need to connect to seveal Oracle databases.
- I tried installing 64 bit Orcale 9 on this server. Got a strange message that the application (Oracle 64 bit) could not be installed on a 32 bit server! Strange!
- I installed Oracle 9 32 bit on the server, but I could not find ODBC driver in the Data sources! Neither the oledb under providers in MSS Manager Studio!
- Same result efter several desintall and installation. A college copied the .dll files och run a script to register the dll files. We could then se the Oracle driver in the Database sources, but we could not create any connection.
- We have even tried an 10G version of Oracle with the same result.

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

I have posted this issue on several forums including Oracles site. We are going to leave that database which demand linked servers to Oracle on the 32 bit server. I am going to install SQL Server 2005 on that server later. But I am still hoping for some solution to this delicate problem.

|||On 64 bit Windows you have seperate 32 bit and 64 bit ODBC worlds. There is no thunking between 32 and 64 bit components as there was between 16 and 32 bit. To work with 32 bit drivers you need to run the 32 bit ODBC administrator C:\WINDOWS\SysWOW64\odbcad32.exe|||

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I managed fixing thanks to tips from Ron (http://www.dbforums.com/showthread.php?p=6242791#post6242791) by downloading and installning "Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (x64)" from Oracle download page.

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

Then I tried installong the 32 bit version. After that couldn't I see the odbc driver for Oracle in 64 bit odbc. At that time could I however fix my odbc source by running the 32 version av odbc! C:\Windows\SysWOW64\odbcad32.exe

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

I deinstalled Oracle 32 bit, downloaded x64 version av Oracle (after a tip from some one in the forum). The 64 bit version which I tried to install was for Itanium. I had AMD! I manage downloading X64 which is for AMD ("Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (x64)") from Oracle download page.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

I did not install the Oracle database however. I ran the custom installation together with our Oracle expert. We insatalled ODBC, Oledb, ... After that I could find both drivers under the 64 bit version of ODBC and under linked servers in SQL Server 2005.

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

1. Install 64-bit 10.2.0.1 Database into OraDb_home.
2. Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home.
3. Apply patch 5500954 for 64-bit - this is a Critical Bug patch 8 for 64-bit.
4. Apply patch 5337014 for 32-bit - this is a patchset to upgrade the Client to 10.0.2.0.3.
5. Contrary to what Oracle note 334528.1 says (which is to patch the 64-bit Server), you must patch/upgrade the 32-bit client as well (otherwise, it still fails).

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

Lastly, the note above describing the use of the 32-bit 10.3.0.1 does NOT note that there has turned out to be a bug in the sqora32.dll ODBC driver (and two others). This can be fixed by patch 5699495 from MetaLink - metalink.oracle.com.

- Will

|||

Dear Sir,

I have ASP v3 installed on Windows Server 2003 x64 which is connected to Oracle9i Database server. As you know ASP works under 32 bit Tech. So i need to install 32bit oracle client on the server to let ASP connect to Oracle. I tried to install oracle9i client but it didnot work ( the ODBC test connection works but the ASP cannot connect). I Also tried ODBC 32-bit Driver & ODAC all-in-one for 64bit & InstantClient .... all of the failed.

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek

|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Wednesday, March 7, 2012

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?

Regards

I am in the same situation. Have you been able to find a solution?

Thanks,
Derek|||

Unfortunetly not :(

Oh!

Was it that simpel! Thanks for your answer. I could set up my odbc links, but I could't find Oracle oledb provider under linked servers.

Do you have a tip on that?

|||

Please note that there is no 64bit version of the Microsoft Oracle OLEDB provider but there is a 32bit version on the box that can be used by 32bit applications.

To use linked servers on 64bit, you might want to consider installing/using the Oracle supplied OLEDB provider if any.

Regards,

Uwa.

|||

Hi!

I could set up odbc links and linked servers in my x64 box.

Thanks, everyone who replied to my questions.

|||

Maybe I did this wrong, but what I tried (on WindXP 64-bit) was...

1. Load Oracle 10.2.0.1.0 64-bit Enterprise in OraHome and create a sample database.

2. Load Oracle 10.2.0.1.0 32-bit Client in OraClient, with base install and Windows extensions (ODBC, OLE, etc.)

However, when I open C:\Windows\SysWOW64\odbcad32.exe (the 32 Bit ODBC administrator, I saw the Oracle driver for OraClient, but could not create a DSN for the sample OraDB in the 64-bit OraHome.

Can someone point me in the right direction?

Thanks.

- Will

|||

When you say you couldn't set up a DSN, could you give a bit more detail about how you tried to do this and what happened (esp in terms of error messages). Thanks.

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

|||

Hi!

I hade a wrong version av Orcale installation at that time!

When I couldn't install the 64 version av Oracle. I got a meeage that said something like my Windows installation was not 64 bit!

But installing the 32 version av Orcale didn't help my fixing linked servers. There was no oledb driver avaible for 64 bit version of SQL server 2005!

Try that!

Good luck!

|||

This *IS* possible and have now done it with assist from Oracle tech support.

Also, DataDirect's Connect Ver 5.2 ODBC Oracle "wire-driver" (32-bit version) will work without installing the OraClient at all.

- Will

|||

Hi!

Thanks for your reply. I wonder why I should "Install 32-bit 10.2.0.1 Client (needs Windows ODBC extensions) into OraClient_home."?

|||

You need to install two Oracle homes and a database to access, like...

1. The 64-bit database into C:\Oracle\Database - and call the OracleHome instance OraDatabase.

2. The 32-bit client (a whole administrative install) into C:\Oracle\Client - and call the OracleHome instance OraClient.

3. The 64-bit Oracle database ORCL.

(Do NOT try to install into "C:\Program Files\Oracle"... or its "short" equivalent C:\Progra~1\Oracle as the OPatch utility will NOT parse either of these properly)

Take the sqlnet.ora, listener.ora, and tnsnames.ora files from C:\Oracle\Database\Network\Admin and copy them to C:\Oracle\Client\Network\Admin.

At a minimum, the following Services need to be operating from the database...

1. OracleOraDatabaseTNSListener

2. OracleServiceORCL

- Will

|||

Dear Sir,

i would like to ask you about your steps, if it works well with ASP. Also i need to know from where i can download "32-bit 10.2.0.1 Client and 5337014 for 32-bit patch.

thank you in advance.

Alaa

Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I have
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
SQL Server Enterprise Manager
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
SQL Server Enterprise Manager
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance.
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David
|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:

> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>
|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David

Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I have
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
--
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance."Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:
> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> > We are attempting to create a linked server to an Oracle database and it
> > isn't working.
> >
> > We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> > installed the Oracle 9.2.0.1 client and am able to successfully connect to
> > the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> > creating an ODBC DSN using the Oracle driver provided by Oracle, and
> > hitting
> > the "test connection" button. Both ways work, so I am fairly certain I
> > have
> > the tnsnames.ora file configured correctly.
> >
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David

Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I hav
e
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection
"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
--
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance."Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:

> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David

Oracle Linked Server on Windows 2003

We have SQL 2000 SP3a installed on a Windows 2003 server.
We are trying to add an Oracle 8.1.7 linked server.
On W2003, the error message below appears when trying to view tables with th
e exact same connection parameters work on several servers that are running
SQL 2000 on Windows 2000.
Parameters are
Other Data Source: Microsoft OLE DB Provider for Oracle
Product name: Oracle
Data Source: <same as on working W2K server>
Data Source: <same as on working W2K server>
Server Options: Data Access, RPC, RPC Out, Use Remote Collation enabled
Be made in this security context: <same Username/PW that are working on W2K
server>
Error message:
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
How can we get SQL 2000 on W2003 to link to the Oracle server?Did you restart the server after installing the Oracle client?
The recommended steps are:
1. Install Oracle client
2. Reinstall MDAC
3. Reboot the server
Also you cannot use multiple Oracle homes, this is not supported from
the OLE DB provider.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I am having an issue while trying to install the Orcale Client 8.1.7 on a
Windows 2003 Server Standard Edition...Nothing happens and the install
doesn't complete? Is there a switch I need to check to allow the
installation?
Thanks,
Warren
"mt69clp" wrote:

> Did you restart the server after installing the Oracle client?
> The recommended steps are:
> 1. Install Oracle client
> 2. Reinstall MDAC
> 3. Reboot the server
> Also you cannot use multiple Oracle homes, this is not supported from
> the OLE DB provider.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Why do you choose Client 8.1.7? Try installing the newest 9.X client. It
is backward compatible and you will have less trouble wit Windows 2003.
The 10.X should do it also but I think it is not tested by Microsoft
until now.
I use the 9.0.1.0 client to connect to a 8.1.7.4 database and it works
fine.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I tried to install the 9.x Client and still can't. I don't get an error, it
just says press next once the installation is complete and it just stops? I
s
there a setting in Windows 2003 server that I need to address to allow the
install?
Thanks,
Warren
"mt69clp" wrote:

> Why do you choose Client 8.1.7? Try installing the newest 9.X client. It
> is backward compatible and you will have less trouble wit Windows 2003.
> The 10.X should do it also but I think it is not tested by Microsoft
> until now.
> I use the 9.0.1.0 client to connect to a 8.1.7.4 database and it works
> fine.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Hello, I was wondering if anyone was able to run the Oracle 8i v8.1.7 databa
se on the Windows 2003 Server? Also, do you know where i could find additio
nal information for other applications (such as Websphere 5.0.2 and the J2EE
Architecture) running on Windows 2003 Server? Any help is appreciated.
Thanks,
Jason

quote:
Originally posted by mt69clp
Why do you choose Client 8.1.7? Try installing the newest 9.X client. It
is backward compatible and you will have less trouble wit Windows 2003.
The 10.X should do it also but I think it is not tested by Microsoft
until now.
I use the 9.0.1.0 client to connect to a 8.1.7.4 database and it works
fine.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

|||I just opened a crit sit with MS with regards to this issue. Here is
how to fix it with Windows 2003, SQL 2000, and Oracle 9i:
This is discussed in Q193893 and according to this the registry key "
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSDTC\MTxOCI] " should have
following
entries.
"OracleXaLib"="oraclient9.dll"
"OracleSqlLib"="orasql9.dll"
"OracleOciLib"="oci.dll"
I hope this helps.
Carrie
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Monday, March 19, 2012

Oracle10 ODBC connection

Oracle10 ODBC connection

Oracle/Unix Environments --> SQL Server 2005/Windows 2003 Server

Oracle to SQL Server Transactional Replication Error

Oracle to SQL Server Transactional Replication Error

Friday, March 9, 2012

Oracle OLEDB in SSIS - problem with 64bit

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Wednesday, March 7, 2012

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle odbc driver on windows 2003 64 bit, SQL Server 2005

Oracle Linked Server Problems

Oracle Linked Server Problems

Oracle Linked Server Problems

Oracle Linked Server on Windows 2003

Oracle DBA to learn MS SQL

Blog Archive

About Me