ORDER BY Clause On Bit Value Failure using SELECT DISTINCT

Hey,
I am trying to construct an ORDER BY clause for a SELECT DISTINCT query that
attempts to order the data based on a particular bit value stored in a
column named [Properties], but I get this failure message:
"ORDER BY items must appear in the select list if SELECT DISTINCT is
specified"
If I remove the "DISTINCT" keyword, then all is fine, but I do not
understand how to properly set this type of query up. Any help is very much
appreciated.
The query I am using is:
SELECT DISTINCT
ScanChangedParams.*, ScanStrategies.StrategyName AS
Expr1, ScanStrategies.TemplateID AS Expr2, ScanStrategies.ProjectID AS
Expr3,
ScanStrategies.RootTemplateID AS Expr4,
ScanStrategies.ScanError AS Expr5, TPSScanTemplates.ParamName AS Expr6
FROM ScanChangedParams WITH (NOLOCK) INNER JOIN
ScanStrategies WITH (NOLOCK) ON
ScanStrategies.ScanTaskID = ScanChangedParams.ScanTaskID AND
ScanStrategies.StrategyID =
ScanChangedParams.StrategyID INNER JOIN
TPSScanTemplates WITH (NOLOCK) ON
ScanChangedParams.ParamID = TPSScanTemplates.CommonParamID
WHERE (ScanChangedParams.ScanTaskID = 68)
ORDER BY ScanChangedParams.Properties & 1You can not use * in this specific case. Write down the list, it is a good
practice.
AMB
"AST" wrote:

> Hey,
> I am trying to construct an ORDER BY clause for a SELECT DISTINCT query th
at
> attempts to order the data based on a particular bit value stored in a
> column named [Properties], but I get this failure message:
> "ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified"
> If I remove the "DISTINCT" keyword, then all is fine, but I do not
> understand how to properly set this type of query up. Any help is very mu
ch
> appreciated.
>
> The query I am using is:
> SELECT DISTINCT
> ScanChangedParams.*, ScanStrategies.StrategyName AS
> Expr1, ScanStrategies.TemplateID AS Expr2, ScanStrategies.ProjectID AS
> Expr3,
> ScanStrategies.RootTemplateID AS Expr4,
> ScanStrategies.ScanError AS Expr5, TPSScanTemplates.ParamName AS Expr6
> FROM ScanChangedParams WITH (NOLOCK) INNER JOIN
> ScanStrategies WITH (NOLOCK) ON
> ScanStrategies.ScanTaskID = ScanChangedParams.ScanTaskID AND
> ScanStrategies.StrategyID =
> ScanChangedParams.StrategyID INNER JOIN
> TPSScanTemplates WITH (NOLOCK) ON
> ScanChangedParams.ParamID = TPSScanTemplates.CommonParamID
> WHERE (ScanChangedParams.ScanTaskID = 68)
> ORDER BY ScanChangedParams.Properties & 1
>
>|||Hey AMB,
I tried this and it still does not work. If I simply remove the DISTINCT
keyword, then all is fine.
Best regards,
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:694E712B-78EB-44ED-8C4F-828649A73A6F@.microsoft.com...
> You can not use * in this specific case. Write down the list, it is a good
> practice.
>
> AMB
> "AST" wrote:
>
that
much
AS|||Include "ScanChangedParams.Properties & 1" in the select list.
AMB
"AST" wrote:

> Hey AMB,
> I tried this and it still does not work. If I simply remove the DISTINCT
> keyword, then all is fine.
> Best regards,
> Bill
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:694E712B-78EB-44ED-8C4F-828649A73A6F@.microsoft.com...
> that
> much
> AS
>
>|||Hey AMB,
Thanks for the assistance!
Yes, this did result in the query executing, but it filters the rowset to
only those that satisfy the bit operation (Properties & 1). What I want to
do is sort or order by this bit value only as this particular bit pattern is
used to display textual data representation on a client window.
Best regards,
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:658B4D3C-28A5-4519-BC06-FD1A185E9AE0@.microsoft.com...
> Include "ScanChangedParams.Properties & 1" in the select list.
>
> AMB
> "AST" wrote:
>
DISTINCT
message
good
query
a
very
ScanStrategies.StrategyName
AS
Expr6|||I think you have to be more specific or post DDL, sample data and expected
result.
AMB
"AST" wrote:

> Hey AMB,
> Thanks for the assistance!
> Yes, this did result in the query executing, but it filters the rowset to
> only those that satisfy the bit operation (Properties & 1). What I want t
o
> do is sort or order by this bit value only as this particular bit pattern
is
> used to display textual data representation on a client window.
> Best regards,
> Bill
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:658B4D3C-28A5-4519-BC06-FD1A185E9AE0@.microsoft.com...
> DISTINCT
> message
> good
> query
> a
> very
> ScanStrategies.StrategyName
> AS
> Expr6
>
>|||<snip>
As AMD indicated, inclusion of the expression should not alter the result
set (beyond the additional column). However, it is possible that there is
something specific to your query that is not directly evident without
knowledge of the underlying DDL and data characteristics. Below is an
example that shows the expression <<shouldn not>> affect the result set.
The last query is another alternative.
set nocount on
create table #test (numval smallint, descr varchar(25))
go
insert #test (numval, descr)
select 1, 'good by'
union all
select 2, 'good by'
union all
select 3, 'goodby'
union all
select 2, 'good by'
union all
select 4, 'good by'
union all
select null, 'good by'
go
select numval, descr from #test
select distinct numval, descr from #test
select distinct numval, descr, numval & 1 as bogus from #test
order by bogus
select distinct numval, descr, numval & 1 as bogus from #test
order by numval & 1
select numval, descr from
( select distinct numval, descr from #test ) as t1
order by numval & 1|||Hey Alejandro,
After further testing, I was able to get this query to work as you described
by adding the same column again in the select query.
Thanks again!
Best regards,
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9D8FF8AD-3060-4C4B-8224-6A479DFA05A1@.microsoft.com...
> I think you have to be more specific or post DDL, sample data and expected
> result.
>
> AMB
> "AST" wrote:
>
to
to
pattern is
message
is a
DISTINCT
stored in
DISTINCT is
not
is
ScanStrategies.ProjectID|||Hey Scott,
Thanks for the feeback and examples. Based on AMD's and your comments and a
little more testing I have now worked this out.
Thanks again!
Best regards,
Bill
"Scott Morris" <bogus@.bogus.com> wrote in message
news:eU%23lFfJCFHA.328@.tk2msftngp13.phx.gbl...
> <snip>
> As AMD indicated, inclusion of the expression should not alter the result
> set (beyond the additional column). However, it is possible that there is
> something specific to your query that is not directly evident without
> knowledge of the underlying DDL and data characteristics. Below is an
> example that shows the expression <<shouldn not>> affect the result set.
> The last query is another alternative.
> set nocount on
> create table #test (numval smallint, descr varchar(25))
> go
> insert #test (numval, descr)
> select 1, 'good by'
> union all
> select 2, 'good by'
> union all
> select 3, 'goodby'
> union all
> select 2, 'good by'
> union all
> select 4, 'good by'
> union all
> select null, 'good by'
> go
> select numval, descr from #test
> select distinct numval, descr from #test
> select distinct numval, descr, numval & 1 as bogus from #test
> order by bogus
> select distinct numval, descr, numval & 1 as bogus from #test
> order by numval & 1
> select numval, descr from
> ( select distinct numval, descr from #test ) as t1
> order by numval & 1
>
>

Oracle to Sql Server conversion

Something went a bit wrong. Converted Oracle 10g to Sql Server 2000.
Now, in the schema there is a column that allows nulls.
In Oracle if you look at the column, you see NULL.
However if I look at the same created in Sql Server it actually does not have a <NULL> value there.
When the conversion was done however, all of these NULLs from Oracle came across into Sql Server with the actual value of <NULL>.
This causes the problem that these objects are now no longer displayed within my application. They show up fine using Oracle, and if I use Sql Server from the start it is fine -- the column is just blank, not acutally NULL. I can force the <NULL> value by doing the ctrl+0 on the field, and that breaks it as well.

The column has to allow nulls, but the actual value cannot be <NULL> (in Sql Server). Any suggestions on getting rid of the NULL - I could do an update, but it actually just has to be blank rather than having a value. I tried an update to set it to ' ' but that didn't really work - here was my statement:
update [table] set [columnname] = '' where [columnname] = '<NULL>'

Any other suggestions besides try again? But if it has to be 'try the conversion again' then that's the answer.
Thanks muchupdate [table] set [columnname] = NULL where [columnname] = '<NULL>'

Oracle Sources on x64 opteron w/64 bit OS & 64 bit SSAS

Hello all..

here is a brief description of my problem.

HP DL585 64 bit Server 2003 R2
x64 (opteron)

Most of our data is housed in Oracle.

When i have a project that sources data from oracle (.net/OracleClient Data Provider) I cannot process the cube.

I found this Document that outlined how to workaround the basic oracle connectivity problem, however it has not fully addressed what I've seen. I worked out how to get the connection to work from within BIDS (by starting from the batch file outlined in the linked page), however I cannot process the cube. I receive the following Error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed..

This looks to me as if the 64 bit SSAS service is trying to use the 32bit oracle client installed on the system, however cannot, since service is 64 bit. That makes sense to me. however both the 64 bit and 32 bit clients are installed. So the questions is:

How to i get BIDS to use the 32 bit client, and get the MSAS service (64bit) to use the 64bit oracle client?

Is this approach totally invalid?

Someone please help.

I haven't ever tried building a cube off an Oracle DB, but I have built SSIS packages off Oracle DBs on x64. The following article helped tremendously:

http://stevemchugh.blogspot.com/2007/02/adventures-of-ssis-and-oracle-in-64bit.html

I won't even try to answer your question other than mentioning that article as something to checkout. Not sure if it will be helpful or not.

|||Thanks for the article reference... I learned a little bit about it.

The article deals with SSIS predominantly, and the issues with SSAS seems related but different. The article also describes using the Oracle OLEDB driver, and in my instance i'm using the .net provider, since the microsoft and oracle oledb driver both don't handle numerics with no defined precision, something which, while explicitly state by oracle is not a best practice, seems to occur quite frequently in our environment.

I tried using the oracle oledb driver just to check and no joy . when i use that, i get the following message when i process the cube on the server. again, everything seems to work (locally) from BIDS, just not when processing.

OLE DB or ODBC error: Class not registered.

So I'll have to wait and see if anyone chimes in who has worked through this problem.

-Eric

Oracle provider error

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.

Hi,

I once has a problem that I had to give a result back from my stored procedure in oracle although I didn#t actually need to return anything. It was just needed for the provider to do some mapping to "a" resultset. perhps you should try that if you are only about to execute a stored procedure with no return values at all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Is the behavior the same when you use MSDAORA? Have you checked Oracle's knowledge base? It's also a good idea to install the latest patches for the Oracle client.

From the description of the problem it sounds as the Provider is having a problem fetching a row from the Oracle DBMS specified by the SQL engine by a bookmark. If it worked with Oracle 9/SQL 2k, the cause could be either the SQL engine requesting the row from OLEDB in a different manner or an issue within the OraOLEDB provider. Using MSDAORA instead of OraOLEDB would determine where the issue resides.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

We have the same problem here. I don't have any answer yet...

For info, this is for the 64 bit server. With 32 bit and msdaora everything works perfectly...

Where is the native oracle driver for sqlserver 64 bit entreprise version as said in the doc/promo/site ?

Should we downgrade to 32 bit because the 64 bit can't even work with oracle ?

I hope to find an answer soon...

greetings

|||I had the same problem on my x64 box and resolved it (mostly) by installing the Oracle ODAC (available on the Oracle Technology Network) and a patch (#5043675 available on Metalink) that fixes a bug that causes updates and deletes to fail.

Oracle provider error

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.

Hi,

I once has a problem that I had to give a result back from my stored procedure in oracle although I didn#t actually need to return anything. It was just needed for the provider to do some mapping to "a" resultset. perhps you should try that if you are only about to execute a stored procedure with no return values at all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Is the behavior the same when you use MSDAORA? Have you checked Oracle's knowledge base? It's also a good idea to install the latest patches for the Oracle client.

From the description of the problem it sounds as the Provider is having a problem fetching a row from the Oracle DBMS specified by the SQL engine by a bookmark. If it worked with Oracle 9/SQL 2k, the cause could be either the SQL engine requesting the row from OLEDB in a different manner or an issue within the OraOLEDB provider. Using MSDAORA instead of OraOLEDB would determine where the issue resides.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

We have the same problem here. I don't have any answer yet...

For info, this is for the 64 bit server. With 32 bit and msdaora everything works perfectly...

Where is the native oracle driver for sqlserver 64 bit entreprise version as said in the doc/promo/site ?

Should we downgrade to 32 bit because the 64 bit can't even work with oracle ?

I hope to find an answer soon...

greetings

|||I had the same problem on my x64 box and resolved it (mostly) by installing the Oracle ODAC (available on the Oracle Technology Network) and a patch (#5043675 available on Metalink) that fixes a bug that causes updates and deletes to fail.

Oracle provider error

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.

Hi,

I once has a problem that I had to give a result back from my stored procedure in oracle although I didn#t actually need to return anything. It was just needed for the provider to do some mapping to "a" resultset. perhps you should try that if you are only about to execute a stored procedure with no return values at all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Is the behavior the same when you use MSDAORA? Have you checked Oracle's knowledge base? It's also a good idea to install the latest patches for the Oracle client.

From the description of the problem it sounds as the Provider is having a problem fetching a row from the Oracle DBMS specified by the SQL engine by a bookmark. If it worked with Oracle 9/SQL 2k, the cause could be either the SQL engine requesting the row from OLEDB in a different manner or an issue within the OraOLEDB provider. Using MSDAORA instead of OraOLEDB would determine where the issue resides.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

We have the same problem here. I don't have any answer yet...

For info, this is for the 64 bit server. With 32 bit and msdaora everything works perfectly...

Where is the native oracle driver for sqlserver 64 bit entreprise version as said in the doc/promo/site ?

Should we downgrade to 32 bit because the 64 bit can't even work with oracle ?

I hope to find an answer soon...

greetings

|||I had the same problem on my x64 box and resolved it (mostly) by installing the Oracle ODAC (available on the Oracle Technology Network) and a patch (#5043675 available on Metalink) that fixes a bug that causes updates and deletes to fail.

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!

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.

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

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!

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.

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

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!

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.

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

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!

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.

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

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!

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.

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

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!

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.

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 Linked Server on SQL Server 2005 64-bit

Hello,
we are trying to migrate from SQL Server 2000 to SQL Server 2005.
On an 64 bit Windows 2003 SP1 Server we have installed an SQL Server 2000
Enterprise Edition and an SQL Server Enterprise Edition 2005 (64bit), both as
named instances.
On both SQL Servers we have configured Oracle Linked Servers. While
distributed queries on the SQL Server 2000 are running fine, we get the
following error message on the SQL Server 2005:
Msg 7403, Level 16, State 1, Procedure v_extendedcustomer2, Line 6
The OLE DB provider "MSDAORA" has not been registered.
What could be the problem?
Thanks in advance
Detlef Herchen
Hi Detlef,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you could quer
1. Is your SQL Server 2000 also 64 bit version?
2. Does your Oracle OLE DB provider supports 64-bit? You must download the
latest provider that supports 64 bit for SQL Server 2005 x64
3. You may also try using 32bit ODBC Configuration in the Windows Pro X64
to configure Access ODBC driver for your application. Launch the Panel with
command C:\WINDOWS\SysWow64\odbcad32.exe and you will see the Configuration
Dialog. (Note that this will run on WOW)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Oracle Linked Server on SQL Server 2005 64-bit

Hello,
we are trying to migrate from SQL Server 2000 to SQL Server 2005.
On an 64 bit Windows 2003 SP1 Server we have installed an SQL Server 2000
Enterprise Edition and an SQL Server Enterprise Edition 2005 (64bit), both a
s
named instances.
On both SQL Servers we have configured Oracle Linked Servers. While
distributed queries on the SQL Server 2000 are running fine, we get the
following error message on the SQL Server 2005:
Msg 7403, Level 16, State 1, Procedure v_extendedcustomer2, Line 6
The OLE DB provider "MSDAORA" has not been registered.
What could be the problem?
Thanks in advance
Detlef HerchenHi Detlef,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you could quer
1. Is your SQL Server 2000 also 64 bit version?
2. Does your Oracle OLE DB provider supports 64-bit? You must download the
latest provider that supports 64 bit for SQL Server 2005 x64
3. You may also try using 32bit ODBC Configuration in the Windows Pro X64
to configure Access ODBC driver for your application. Launch the Panel with
command C:\WINDOWS\SysWow64\odbcad32.exe and you will see the Configuration
Dialog. (Note that this will run on WOW)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Michael Cheng,
I'm using SQL Server 2005 64 Bit and having a problem when I try to
connect to Oracle 10G 64 Bit (installed on the same server). I'm using
"Oracle Provider for OLE DB" to connect but from what I've read in
Books Online that the provider doesn't support for Oracle 64 Bit. Can
you please tell me how to fix it ? Maybe I should install another
provider such as "Microsoft OLE DB Provider for Oracle" ? If so, where
can I find or download that provider for SQL 2005 64 Bit. Thanks.
Regards,
Albert
4lb3rt
---
4lb3rt's Profile: http://www.dbtalk.net/m925
View this thread: http://www.dbtalk.net/t155721