OleDb Source Error - Connecting to Oracle 9i

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

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

Could anyone please help me trouble shoot this problem.

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

Hi,

Click on the OLE DB Source component and check for the properties, you have to keep the property "Alwaysusedefaultcodepage" to true.

Hope this will help you.

Regards,

Raju

|||

Hi Raju,
Thank you very much for your help.
Well, I was not aware of this option and it helped me a lot to finish my work.

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

Oledb Source adapter exception Error code: 0x80040E21

Hi

I am using Oledb Provider for Db2 from IBM. (There is another one from microsoft)

Through this provider I am constructing a oledb connection manager.

This connection manager I am Using in Oledb Source adapter.

Now when I set Data Access mode as Table or view I Am able to preview the data.

But when I use Data Access Mode as SQL Command try to preview I get this Error

TITLE: Microsoft Visual Studio

Error at s_NEWMAPPING10 [EMP [1]]: An OLE DB error has occurred. Error code: 0x80040E21.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

I am not able to figure out how to resolve this.

I am receiving the exact same error. Can't run custom queries against the database and it is frustrating. Someone from Microsoft needs to respond to this immediately...it's not acceptable.sql

Oledb Source adapter exception Error code: 0x80040E21

Hi

I am using Oledb Provider for Db2 from IBM. (There is another one from microsoft)

Through this provider I am constructing a oledb connection manager.

This connection manager I am Using in Oledb Source adapter.

Now when I set Data Access mode as Table or view I Am able to preview the data.

But when I use Data Access Mode as SQL Command try to preview I get this Error

TITLE: Microsoft Visual Studio

Error at s_NEWMAPPING10 [EMP [1]]: An OLE DB error has occurred. Error code: 0x80040E21.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

I am not able to figure out how to resolve this.

I am receiving the exact same error. Can't run custom queries against the database and it is frustrating. Someone from Microsoft needs to respond to this immediately...it's not acceptable.

OLEDB Providers

Hi,

I have a DTS package which is using OLEBD provider for Oracle, it was working fine. I tried to open this package and I got this error message "There was a problem retrieving the list of OLE DB Providers"

I tried to intall MDAC8.2, it didn't help, should I reinstall SQL Server?

Thanks!Where was the package working fine from, and where you were trying to open it from when you received an error?|||It was working from SQL Server 2000, I am trying it from same machine, same sql server and its not working.|||So you tried to install MDAC 8.2 on the SQL Server box? MDAC level on the server is controlled by service packs and should not be changed by any other way, like direct installation. MDAC is what SQL uses to talk to itself. If this is what you did, - at a minimum you need to have a reliable backup of your master, msdb, and user databases, because you may have to reapply SP3 (not sure that would do it), reinstall SQL (possibly will fix it), or reload the box.|||I tried to reinstall sql 2000 and service pack 3a but problem still exists. Is there any other option besides reloading the box?

Thanks!

Oledb Provider having problem in MS SQL Server 2005

Hi,

I try to access the table "UserGroup" in my local sql server 2005 database. However I get this error message
"Invalid object name 'UserGroup'. "

I'm using MS Visual Studio 2005 and MS Sql Server 2005. I did install the latest Sql Native. In fact I just migrate from ASP.NET to ASP.NET 2.0 . It is working if my connection string is point to my "online host" without any error.

'Online Connection String - Working
<addkey="OLEDB"value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=abc;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx;Connect Timeout=3600"/>

'Local Connection String - Not working
(SQL Authentication)
<addkey="OLEDB"value="Provider=SQLNCLI;Trusted_Connection=No;UID=calvin;Initial Catalog=mydatabase_db;Data Source=CALVINNB; Password=xxxxxx"/>
OR
(Windows Authentication)
<addkey="OLEDB"value="Provider=SQLNCLI;Server=CALVINNB;Database=mydatabase_db;Trusted_Connection=yes"/
Is the Oledb provider having problem in MS SQL Server 2005?

Any help would be more appreciate.

Calvin

First thing I would try it to make sure you do have a table named UserGroup, and also try prefixing with with the owner.

Next, you should be using the SQL provider and not the OLEDB providers for SQL Server.

|||Yes, I do have a table named UserGroup. If I change the OLEDB provider to SQL provider, then I have a lot of code need to be change and debug. Is there any way to solve this problem? And I don't understand why I use my "Online" connection string(bottom) without error? Even the provider isSQLOLEDB.1?

<addkey="OLEDB"value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxx;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxxxx;Connect Timeout=3600"/>

The difference to my local connection is just the "UID", "Password" and Data Source only. I'm sure that the login account for local is created perfectly. The host and my computer also using MS SQL Server 2005.

|||

EDUStreet:

Yes, I do have a table named UserGroup. If I change the OLEDB provider to SQL provider, then I have a lot of code need to be change and debug.

OLEDB provider is meant to be used for older data sources. For SQL Server 7.0+ you should really be using the SQL Providers, that is what it is there for. In terms of coding changes there should not be too many apart from renaming all the declarations and instantiations from Oledb to Sql.

One of the best ways to make sure you got the right connection string is to drag and drop something like a gridview or sqldatasource and go through the wizard to connect to your datasouce and use the auto generated connection string

Here is a web site that has all connection string formats

http://www.connectionstrings.com/?carrier=sqlserver2005

|||

Finally, I change all the code which is from OLEDB provider to SQL provider

This is the connection string i'm using now.

<addkey="OLEDB"value="User ID=xxxx;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx"/>

|||

EDUStreet:

"The ConnectionString property has not been initialized. "

When you create the SqlConnection object you need to supply the connection string. Please ensure you have done this.

|||

Thanks Jimmy :)

OLEDB Provider for SQL Server Errors

Hi Yosh,
I saw a number of posting relating to the Jet database but I am not using
the Jet database in any way do I doubt that is the source. This app has
been installed on 20 Windows XP PCs for the last six months and the today is
the first time we have seen this happen. It is also occuring on multiple
PCs.
Thanks for your help,
Darrel
"Yosh" <yoshi@.nospam.com> wrote in message
news:Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Actually it sounds more like a JET error. Download and install the latest
> JET 4.0.
> That should fix the problem. It sounds like a JET 3.5 problem.
> Yosh
>Hello Darrel,
It seems to be a timeout issue. For example:
-3633 - Microsoft OLE DB Provider for SQL Server: Timeout expired
Error : 3633 : Microsoft OLEDB provider for ODBC drivers : Oracle ODBC ORA
ORA-01013 : User requested cancel of the current operation.
It is usually caused by performance or network issue. You could set
CommandTimeout of ADODB.command in client application to 0 if you never
want a command to timeout.
Also, it could be a driver related issue though it does not seem to be true
for this specific case.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
| From: "Darrel Miller" <darrel@.tavis.ca>
| References: <OQo8uXrwFHA.2228@.TK2MSFTNGP11.phx.gbl>
<#h6aRbrwFHA.612@.TK2MSFTNGP10.phx.gbl>
<#4wDOoswFHA.2312@.TK2MSFTNGP14.phx.gbl>
<eGkZa0twFHA.1996@.TK2MSFTNGP10.phx.gbl>
<Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: OLEDB Provider for SQL Server Errors
| Date: Mon, 26 Sep 2005 20:27:49 -0400
| Lines: 23
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <enHbMpvwFHA.3756@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: toronto-hse-ppp3944778.sympatico.ca 70.49.61.98
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:120894
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi Yosh,
|
| I saw a number of posting relating to the Jet database but I am not using
| the Jet database in any way do I doubt that is the source. This app has
| been installed on 20 Windows XP PCs for the last six months and the today
is
| the first time we have seen this happen. It is also occuring on multiple
| PCs.
|
| Thanks for your help,
|
| Darrel
|
| "Yosh" <yoshi@.nospam.com> wrote in message
| news:Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl...
| > Actually it sounds more like a JET error. Download and install the
latest
| > JET 4.0.
| >
| > That should fix the problem. It sounds like a JET 3.5 problem.
| >
| > Yosh
| >
|
|
||||You may want to check the SQL Server "Maximum concurrent user connections"
value under the SQL Server Properties Connection tab.
This variable states the following:
"Specify the maximum concurrent user connections. Entering zero means there
can be an unlimited number of concurrent user connections."
If the number of users trying to connect to the SQL Server instance exceeds
this value, you will have experience sporadic errors like the one you've
described. This includes the connections your application makes along with
all the other applications connecting to the SQL Server instance. Also,
think about all the developers or users using the query analyzer. Each query
window is a seperate connection.
One thing developers do is open multiple connections within their
application. If your application opens 3 connections and you have 20 users,
that will be 60 SQL Server connections.
Yosh
"Darrel Miller" <darrel@.tavis.ca> wrote in message
news:enHbMpvwFHA.3756@.tk2msftngp13.phx.gbl...
> Hi Yosh,
> I saw a number of posting relating to the Jet database but I am not using
> the Jet database in any way do I doubt that is the source. This app has
> been installed on 20 Windows XP PCs for the last six months and the today
> is the first time we have seen this happen. It is also occuring on
> multiple PCs.
> Thanks for your help,
> Darrel
> "Yosh" <yoshi@.nospam.com> wrote in message
> news:Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Hi Peter,
Thanks for the info. I was starting to expect it to be a timeout problem.
Now I just have to find out what is causing it. I don't suppose you can
point me to a place where I can see a list of the errors that the Microsoft
OLE DB Provider for SQL Server can generate? I would like to set traps for
these errors so that I can handle them properly.
Thanks,
Darrel
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:Ty7XIMxwFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hello Darrel,
> It seems to be a timeout issue. For example:
> -3633 - Microsoft OLE DB Provider for SQL Server: Timeout expired
> Error : 3633 : Microsoft OLEDB provider for ODBC drivers : Oracle ODBC ORA
> ORA-01013 : User requested cancel of the current operation.
> It is usually caused by performance or network issue. You could set
> CommandTimeout of ADODB.command in client application to 0 if you never
> want a command to timeout.
>
> Also, it could be a driver related issue though it does not seem to be
> true
> for this specific case.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>|||Hi Yosh,
On the recommendation of another forum member I checked the max connections
property and it is set to zero.
Thanks for the suggestion,
Darrel
"Yosh" <yoshi@.nospam.com> wrote in message
news:uvhYDkxwFHA.3904@.TK2MSFTNGP10.phx.gbl...
> You may want to check the SQL Server "Maximum concurrent user connections"
> value under the SQL Server Properties Connection tab.
> This variable states the following:
> "Specify the maximum concurrent user connections. Entering zero means
> there can be an unlimited number of concurrent user connections."
> If the number of users trying to connect to the SQL Server instance
> exceeds this value, you will have experience sporadic errors like the one
> you've described. This includes the connections your application makes
> along with all the other applications connecting to the SQL Server
> instance. Also, think about all the developers or users using the query
> analyzer. Each query window is a seperate connection.
> One thing developers do is open multiple connections within their
> application. If your application opens 3 connections and you have 20
> users, that will be 60 SQL Server connections.
> Yosh
>
> "Darrel Miller" <darrel@.tavis.ca> wrote in message
> news:enHbMpvwFHA.3756@.tk2msftngp13.phx.gbl...
>|||Hello Darrel,
Sorry there are no document on this error at present, and your feedback on
this is routed to the proper channel. Also, if you want to trace the error
via live debug or user dump, we recommend that you contact Microsoft
Product Support Services and open a support incident and work with a
dedicated Support Professional.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
| From: "Darrel Miller" <darrel@.tavis.ca>
| References: <OQo8uXrwFHA.2228@.TK2MSFTNGP11.phx.gbl>
<#h6aRbrwFHA.612@.TK2MSFTNGP10.phx.gbl>
<#4wDOoswFHA.2312@.TK2MSFTNGP14.phx.gbl>
<eGkZa0twFHA.1996@.TK2MSFTNGP10.phx.gbl>
<Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl>
<enHbMpvwFHA.3756@.tk2msftngp13.phx.gbl>
<Ty7XIMxwFHA.768@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: OLEDB Provider for SQL Server Errors
| Date: Tue, 27 Sep 2005 01:34:48 -0400
| Lines: 41
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#MwkvUywFHA.2960@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: locweld.com 69.70.2.194
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:120918
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi Peter,
|
| Thanks for the info. I was starting to expect it to be a timeout
problem.
| Now I just have to find out what is causing it. I don't suppose you can
| point me to a place where I can see a list of the errors that the
Microsoft
| OLE DB Provider for SQL Server can generate? I would like to set traps
for
| these errors so that I can handle them properly.
|
| Thanks,
|
| Darrel
|
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:Ty7XIMxwFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > Hello Darrel,
| >
| > It seems to be a timeout issue. For example:
| >
| > -3633 - Microsoft OLE DB Provider for SQL Server: Timeout expired
| >
| > Error : 3633 : Microsoft OLEDB provider for ODBC drivers : Oracle ODBC
ORA
| > ORA-01013 : User requested cancel of the current operation.
| >
| > It is usually caused by performance or network issue. You could set
| > CommandTimeout of ADODB.command in client application to 0 if you never
| > want a command to timeout.
| >
| >
| > Also, it could be a driver related issue though it does not seem to be
| > true
| > for this specific case.
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
|
|
|

OLEDB Provider for SAS

Is there an OLEDB provider for SAS? Does anyone know where I could get one? Does it ship with the SAS installation? Thanks!You could try searching SAS's Website for OLE DB/ODBC providers... Install them and you should be able to use them inside SSIS.

If using an ODBC provider, you'll have to use a DataReader Source.

A quick search turned up: http://support.sas.com/rnd/eai/oledb/

OLEDB Provider Errors

I have an asp page that is using OLEDB to connect to SQL
Server 2K (We'll call it 2k1). It requests data from a
view on 2k1 which via linked server points to a an
instance of SQL Server 7.0 on a different box (We'll call
it 702). In running this command we receive very
sporadic errors. There are two in particular. Both are
regarding the transactions being used for the open
connection. Every thing I read points to using explicit
transactions which we are not. The error will last for
about 20 minutes and just disappear. The developers
stating that they are definately closing connections. I
don't know how to proceed.
Here are the errors.
1.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Connection is busy
with results for another command]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IOpenRowset::OpenRowset returned 0x80004005: ].
2.
Server: Msg 7392, Level 16, State 2,
Could not start a transaction for OLEDB
provider 'SQLOLEDB'. [OLE/DB provider returned message:
Only one transaction cant be active on this session.Are you using Transaction Object
try use the connection's tranaction objectsql

OLEDB provider error.

Hi:

I have created a cube and deployed in sql server. I am trying to connect to analysis services to access cube with linked server. I am getting this error...how do i fix this.?

OLE DB provider "MSOLAP" for linked server "(null)" returned message "Errors in the OLE DB provider. The Extended Properties property is set to a value that is not supported.".

Msg 7373, Level 16, State 2, Line 1

Cannot set the initialization properties for OLE DB provider "MSOLAP" for linked server "(null)".

Thanks,

Pramod

What's your Extended Properties? Please check whether there is any typo.|||

Xinwei:

Extended properties for my database is null...

Thanks,

Pramod

|||

Just leave it blank like this: Extended Properties="".
What's your whole connection string?
Thanks.

|||

Xinwei:

This is the query i am using...

SELECT a.* FROM OPENROWSET( 'MSOLAP','DATASOURCE=PKUMAR\PKUMAR;

Initial Catalog=Analysis Services Project1;', 'SELECT Measures.members ON ROWS,

[Product Category].members ON COLUMNS FROM [Adventure Works Internet Sales]') as a

go

Thanks,

|||The problem is on the Extended Properities as the error msg stated. If you have nothing to specify, please try delete it.|||Yep...it didnt help|||I am moving this to the SQL Server Analysis Services Forum.

OleDB Connection Class in Custom Task?

I have OLE DB Connections set up in my connection manager (Native OLE DB\Microsoft OLE DB Provider for SQL Server). I would like to reference and query these connections from a custom task, written in C#. I currently reference it as follows:

using System.Data.OleDb;

...................................

OleDbConnection connection = (OleDbConnection) connections["MyConnection"].AcquireConnection(null);

What may be obvious to some (though wasn't to me, as I am new at this), when I run the task, I get an error saying that I cannot make this cast. After perusing the boards, I understand that this is because I am not making a cast to the right connection type. Well, that is where I am lost. What connection type (and corresponding library) do I need to reference? I want to continue to use the "Native OLE DB..." connection.

Thanks!

The OleDb connection manager is for tasks and data flow components that use unmanaged OleDb API.
Since you want managed connection object, use ADO.NET connection manager, select the same provider (Native OLE DB\Microsoft OLE DB Provider for SQL Server).

|||Makes sense. I don't know much about connection managers, but this helps. I'll give it a shot. Thanks!

OLE/DB provider returned message: Invalid authorization specification

Hello,
I'm trying to import a table from a MSDE database (databaseB) into a SQL
server database (database A).
Using to following sql statement:
insert tableA
select a.*
from openrowset(sqloledb,'Provider=sqloledb;Password=pw d;User ID=usr;Initial
Catalog=databaseA;Data Source=server', select * from [dbo].[tableB]') as a
I'm getting the following error:
[OLE/DB provider returned message: Invalid authorization specification]
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
Everything runs fine if I use integrated security!? The used usr/pwd is a
MSDE login account.
A UDL file connection test directly to the MSDE database runs fine.
Thanx!
CHU! Eric
Hello Eric,
I have reproduced the issue on my side. Based on my research, the following
command works well:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','sophietest\msdeinstance';'s a';'password',
'SELECT * FROM test.dbo.tableB ') AS a
GO
or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=test;S
erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
Therefore, I recommend you perform the following commands:
1. Make sure the Authentication Mode of MSDE is mixed mode.
The following article is for your reference:
INFO: MSDE Security and Authentication
http://support.microsoft.com/default...en-us;325022#3
2. Run the following command to test:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
'SELECT * FROM databaseA.dbo.tableB ') AS a
GO
Or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=databa
seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]') as a
Note:
1. You need to replace the <your MSDE instance name> with your MSDE
instance name.
For more detailed information about OPENROWSET, please refer to the
OPENROWSET topic in SQL Books Online(BOL).
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||Hello Sophie,
Thank you! It works fine now
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:n2c8bveKFHA.2876@.TK2MSFTNGXA02.phx.gbl...
> Hello Eric,
> I have reproduced the issue on my side. Based on my research, the
> following
> command works well:
>
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','sophietest\msdeinstance';'s a';'password',
> 'SELECT * FROM test.dbo.tableB ') AS a
> GO
> or
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=test;S
> erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
>
> Therefore, I recommend you perform the following commands:
> 1. Make sure the Authentication Mode of MSDE is mixed mode.
> The following article is for your reference:
> INFO: MSDE Security and Authentication
> http://support.microsoft.com/default...en-us;325022#3
>
> 2. Run the following command to test:
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
> 'SELECT * FROM databaseA.dbo.tableB ') AS a
> GO
>
> Or
>
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=databa
> seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]') as
> a
>
> Note:
> 1. You need to replace the <your MSDE instance name> with your MSDE
> instance name.
>
> For more detailed information about OPENROWSET, please refer to the
> OPENROWSET topic in SQL Books Online(BOL).
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> 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.
>
>

OLE/DB provider returned message: Invalid authorization specification

Hello,
I'm trying to import a table from a MSDE database (databaseB) into a SQL
server database (database A).
Using to following sql statement:
insert tableA
select a.*
from openrowset(sqloledb,'Provider=sqloledb;P
assword=pwd;User ID=usr;Initial
Catalog=databaseA;Data Source=server', select * from [dbo].[tableB]'
) as a
I'm getting the following error:
[OLE/DB provider returned message: Invalid authorization specification]
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
Everything runs fine if I use integrated security!? The used usr/pwd is a
MSDE login account.
A UDL file connection test directly to the MSDE database runs fine.
Thanx!
CHU! EricHello Eric,
I have reproduced the issue on my side. Based on my research, the following
command works well:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','sophietest\msdein
stance';'sa';'password',
'SELECT * FROM test.dbo.tableB ') AS a
GO
or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb
;UID=sa;PWD=password;Database=test;S
erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
Therefore, I recommend you perform the following commands:
1. Make sure the Authentication Mode of MSDE is mixed mode.
The following article is for your reference:
INFO: MSDE Security and Authentication
http://support.microsoft.com/defaul...;en-us;325022#3
2. Run the following command to test:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
'SELECT * FROM databaseA.dbo.tableB ') AS a
GO
Or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb
;UID=sa;PWD=password;Database=databa
seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]
') as a
Note:
1. You need to replace the <your MSDE instance name> with your MSDE
instance name.
For more detailed information about OPENROWSET, please refer to the
OPENROWSET topic in SQL Books Online(BOL).
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||Hello Sophie,
Thank you! It works fine now
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:n2c8bveKFHA.2876@.TK2MSFTNGXA02.phx.gbl...
> Hello Eric,
> I have reproduced the issue on my side. Based on my research, the
> following
> command works well:
>
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','sophietest\msdein
stance';'sa';'password',
> 'SELECT * FROM test.dbo.tableB ') AS a
> GO
> or
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb
;UID=sa;PWD=password;Database=test
;S
> erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as
a
>
> Therefore, I recommend you perform the following commands:
> 1. Make sure the Authentication Mode of MSDE is mixed mode.
> The following article is for your reference:
> INFO: MSDE Security and Authentication
> http://support.microsoft.com/defaul...;en-us;325022#3
>
> 2. Run the following command to test:
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
> 'SELECT * FROM databaseA.dbo.tableB ') AS a
> GO
>
> Or
>
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb
;UID=sa;PWD=password;Database=data
ba
> seA;Server=<your MSDE instance name>', 'select * from [dbo].[table
B]') as
> a
>
> Note:
> 1. You need to replace the <your MSDE instance name> with your MSDE
> instance name.
>
> For more detailed information about OPENROWSET, please refer to the
> OPENROWSET topic in SQL Books Online(BOL).
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> 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.
>
>

OLE/DB provider returned message: Deferred prepare could not be completed

I have 2 SQL servers. And in the first one I have added the second SQL as a Link Server. When I run an SQL statement on the linked server I get the following message.

Server: Msg 7202, Level 11, State 1, Line 1
Could not find server 'PROD' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

The SQL statement that I am runnins is

Select * from openquery(PROD,'Select * from PROD.GMS.dbo.qryDispCL')

But when I run only the SQL statement "Select * from PROD.GMS.dbo.qryDispCL" it works perfect. But I need to have the first statement running.

Please help. Your valuable feedback is greatly appriciated.

It's really strange... You can run this to check registered server:

EXEC sp_helpservers

If the server 'PROD' is not in the result, then run this to add it:

EXEC sp_addlinkedserver 'PROD'

Note: you may need to add login information for the 'PROD' server with 'sp_addlinkedsrvlogin'.

OLE error code:80040E14

Hi,

I am getting the following error:

OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Column 'tags.id' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

when trying to execute the following query:

select tags.id, name, count(*) as count from taggings, tags where
tags.id = tag_id group by tag_id

The above query works fine on MySQL, but chokes on SQL Server.

Could anyone please help?

Thanks!

NM(neutralm@.gmail.com) writes:

Quote:

Originally Posted by

I am getting the following error:
>
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Column 'tags.id' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
>
>
when trying to execute the following query:
>
select tags.id, name, count(*) as count from taggings, tags where
tags.id = tag_id group by tag_id
>
>
The above query works fine on MySQL, but chokes on SQL Server.

SQL Server, like most DB engines, as well as ANSI SQL, that if your
SELECT list includes an aggregate such as COUNT(*), and there is no
OVER clause for the aggregate, then all unaggregated columns in the
SELECT list must appear in the GROUP BY list.

Change tag_id in the GROUP BY clause to tags.id or vice versa.

Apparently MySQL is lax on this point. As a matter of fact SQL Server
4.x also permitted columns to appear in the SELECT list, if they did
not appear in GROUP BY. Sometimes the result made sense, as here
where tags.id is one-to-one with tags_id. Sometimes you got screenfulls
of garbage when you expected two lines, because you had left out a
column in the GROUP BY clause. The feature was removed in SQL Server
6.0 (and Sybase System 10), missed by few.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your prompt reply, Erland. Pardon my ignorance, but I'm
still not sure if I understood how to solve the problem (although I
think I understand what the problem is from your explanation).

I have two tables:

1. tags (with the primary key 'id' and an attribute 'name')
2. taggings (the primary key is 'id', the foreign key is 'tag_id')

The query string I'm using is:

select tags.id, taggings.tag_id, name, count(*) as count from taggings,
tags where tags.id = taggings.tag_id group by taggings.tag_id

How should the correct query look like?

Thanks so much in advance!

Erland Sommarskog wrote:

Quote:

Originally Posted by

(neutralm@.gmail.com) writes:

Quote:

Originally Posted by

I am getting the following error:

OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Column 'tags.id' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

when trying to execute the following query:

select tags.id, name, count(*) as count from taggings, tags where
tags.id = tag_id group by tag_id

The above query works fine on MySQL, but chokes on SQL Server.

>
SQL Server, like most DB engines, as well as ANSI SQL, that if your
SELECT list includes an aggregate such as COUNT(*), and there is no
OVER clause for the aggregate, then all unaggregated columns in the
SELECT list must appear in the GROUP BY list.
>
Change tag_id in the GROUP BY clause to tags.id or vice versa.
>
Apparently MySQL is lax on this point. As a matter of fact SQL Server
4.x also permitted columns to appear in the SELECT list, if they did
not appear in GROUP BY. Sometimes the result made sense, as here
where tags.id is one-to-one with tags_id. Sometimes you got screenfulls
of garbage when you expected two lines, because you had left out a
column in the GROUP BY clause. The feature was removed in SQL Server
6.0 (and Sybase System 10), missed by few.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||neutralm@.gmail.com wrote:

Quote:

Originally Posted by

The query string I'm using is:
>
select tags.id, taggings.tag_id, name, count(*) as count from taggings,
tags where tags.id = taggings.tag_id group by taggings.tag_id
>
How should the correct query look like?

select taggings.tag_id, name, count(*) as tag_id_count
from taggins join tags on taggings.tag_id = tags.id
group by taggings.tag_id, name

Explanations:

1) GROUP BY must include all unaggregated columns from the SELECT,
i.e. everything that is not a COUNT(), SUM(), etc. (Why doesn't
it implicitly assume this? Apparently, it used to let you leave
things out, but that caused more trouble than it was worth. The
short answer is "just give it what it wants".)

2) tags.id and taggings.tag_id are forced to be equal, so you only need
to include one of them. Optional but recommended, as it's simpler
and conserves bandwidth.

3) The join is changed from SELECT ... FROM A, B WHERE A.X = B.Y
to SELECT ... FROM A JOIN B ON A.X = B.Y
Optional but recommended, as it keeps join conditions separate from
each other, and from other restrictions (e.g. NAME LIKE '%ABC%'),
all of which makes the query easier to understand.|||Thank you very much, Ed. I really appreciate how quickly you've help me
fix this problem!

Ed Murphy wrote:

Quote:

Originally Posted by

neutralm@.gmail.com wrote:
>

Quote:

Originally Posted by

The query string I'm using is:

select tags.id, taggings.tag_id, name, count(*) as count from taggings,
tags where tags.id = taggings.tag_id group by taggings.tag_id

How should the correct query look like?

>
select taggings.tag_id, name, count(*) as tag_id_count
from taggins join tags on taggings.tag_id = tags.id
group by taggings.tag_id, name
>
Explanations:
>
1) GROUP BY must include all unaggregated columns from the SELECT,
i.e. everything that is not a COUNT(), SUM(), etc. (Why doesn't
it implicitly assume this? Apparently, it used to let you leave
things out, but that caused more trouble than it was worth. The
short answer is "just give it what it wants".)
>
2) tags.id and taggings.tag_id are forced to be equal, so you only need
to include one of them. Optional but recommended, as it's simpler
and conserves bandwidth.
>
3) The join is changed from SELECT ... FROM A, B WHERE A.X = B.Y
to SELECT ... FROM A JOIN B ON A.X = B.Y
Optional but recommended, as it keeps join conditions separate from
each other, and from other restrictions (e.g. NAME LIKE '%ABC%'),
all of which makes the query easier to understand.

OLE DB2 Provider

Hi

Running SQL 2005 standard edition, using OLE DB2 provider from Host integration server. When connected we can acces data with select statements, but cannot browse tables from import wizard in SSIS.

Is there a hint or solution?

rgds

It's probably the provider. The Enterprise Edition of Sql Server comes with a DB2 provider. You could also use an ODBC connection instead and see if that works. Finally, IBM might have a provider as well?|||Do you get an error message? I'm using the MS OLE DB2 provider quite regularly and have no issues... Setup is key, though, to getting it to work correctly.

OLE DB provider ran out of memory

I run 2 sql servers same nt4 sp5 and server 1 has 2G
memory, server 1 is linked to server 3 and vice versa.
server 1 has a limit of 1.5G max memory setting for sql
server and I recently ran into this error when initiating
job from server03
OLE DB provider 'SQLOLEDB' reported an error.
The provider ran out of memory. [SQLSTATE 42000] (Error
7399) Could not relay results of procedure 'xxx' from
remote server 'server01'.
[SQLSTATE 42000] (Error 7221) Associated statement is not
prepared
[SQLSTATE HY007] (Error 0) OLE DB provider 'SQLOLEDB'
reported an error.
The provider ran out of memory. [SQLSTATE 42000] (Error
7399)
Could not relay results of procedure 'xxx' from remote
server 'server01'.
[SQLSTATE 42000] (Error 7221). The step failed.
not sure if it's a memory cache problem or what. at my
wits endThe problem is more than likely on Server 3 not 1. You say Server 1 has 2GB
with a 1.5GB max setting, what about Server 3? Linked Servers and OLEDB
type activity is usually taken from the memory to leave portion and not the
memory pool. Memory to leave can be adjusted by using the -g statup
parameter for sql server. I also suggest you set a mx memory on Server 3 if
not already.
Andrew J. Kelly
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:c33101c3ee91$64e3dfb0$a101280a@.phx.gbl...
> I run 2 sql servers same nt4 sp5 and server 1 has 2G
> memory, server 1 is linked to server 3 and vice versa.
> server 1 has a limit of 1.5G max memory setting for sql
> server and I recently ran into this error when initiating
> job from server03
> OLE DB provider 'SQLOLEDB' reported an error.
> The provider ran out of memory. [SQLSTATE 42000] (Error
> 7399) Could not relay results of procedure 'xxx' from
> remote server 'server01'.
> [SQLSTATE 42000] (Error 7221) Associated statement is not
> prepared
> [SQLSTATE HY007] (Error 0) OLE DB provider 'SQLOLEDB'
> reported an error.
> The provider ran out of memory. [SQLSTATE 42000] (Error
> 7399)
> Could not relay results of procedure 'xxx' from remote
> server 'server01'.
> [SQLSTATE 42000] (Error 7221). The step failed.
> not sure if it's a memory cache problem or what. at my
> wits end
>

OLE DB provider ran out of memory

I run 2 sql servers same nt4 sp5 and server 1 has 2G
memory, server 1 is linked to server 3 and vice versa.
server 1 has a limit of 1.5G max memory setting for sql
server and I recently ran into this error when initiating
job from server03
OLE DB provider 'SQLOLEDB' reported an error.
The provider ran out of memory. [SQLSTATE 42000] (Error
7399) Could not relay results of procedure 'xxx' from
remote server 'server01'.
[SQLSTATE 42000] (Error 7221) Associated statement is not
prepared
[SQLSTATE HY007] (Error 0) OLE DB provider 'SQLOLEDB'
reported an error.
The provider ran out of memory. [SQLSTATE 42000] (Error
7399)
Could not relay results of procedure 'xxx' from remote
server 'server01'.
[SQLSTATE 42000] (Error 7221). The step failed.
not sure if it's a memory cache problem or what. at my
wits endThe problem is more than likely on Server 3 not 1. You say Server 1 has 2GB
with a 1.5GB max setting, what about Server 3? Linked Servers and OLEDB
type activity is usually taken from the memory to leave portion and not the
memory pool. Memory to leave can be adjusted by using the -g statup
parameter for sql server. I also suggest you set a mx memory on Server 3 if
not already.
--
Andrew J. Kelly
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:c33101c3ee91$64e3dfb0$a101280a@.phx.gbl...
> I run 2 sql servers same nt4 sp5 and server 1 has 2G
> memory, server 1 is linked to server 3 and vice versa.
> server 1 has a limit of 1.5G max memory setting for sql
> server and I recently ran into this error when initiating
> job from server03
> OLE DB provider 'SQLOLEDB' reported an error.
> The provider ran out of memory. [SQLSTATE 42000] (Error
> 7399) Could not relay results of procedure 'xxx' from
> remote server 'server01'.
> [SQLSTATE 42000] (Error 7221) Associated statement is not
> prepared
> [SQLSTATE HY007] (Error 0) OLE DB provider 'SQLOLEDB'
> reported an error.
> The provider ran out of memory. [SQLSTATE 42000] (Error
> 7399)
> Could not relay results of procedure 'xxx' from remote
> server 'server01'.
> [SQLSTATE 42000] (Error 7221). The step failed.
> not sure if it's a memory cache problem or what. at my
> wits end
>

Ole Db Provider Problems

I have been pulling data from an Oracle database into my Sql Server on an hourly basis for the last 12 months without any problems. I was then asked to pull data from another oracle server hourly as well.

Everything would work ok at first, for say a couple of hours, but then one of the jobs would start failing and the error message would be that the Ole db Provider had run out of memory. The only way I can get it working again is to start the SQL Server service and then restart it.

Each job takes about 10 minutes to run and they are offest by 30 minutes. When I was just running the one job at hourly intervals there has been no problem. I have tried using both the Microsoft and Oracle providers for Oracle but it makes no difference. Also, it isn't just the Oracle Provider that stops working its all of the ole db providers.

The problem seems to be when the SQL Server reaches is max memory on the server. I have the /3GB switch set on the server so the SQl Svr can use 3Gb of memory, but when it reaches the 3Gb thats when the ole db providers start having problems. The message I recieve is the the old db provider is 'out of memory'.

When I stop the service and restart it again, this obviously resets the memory usage and everything is fine again, at least for a few hours until it reaches 3gb of memory again.

I have tried clearing out the buffers after each job but this seems to make no difference.

Any help would be greatly appreciated.

Thanks,

AlphHave one or both of your jobs stop and restart SQL Server as its last step?

-PatP|||How much free space is there on your hard drive and how much memory do you physically have in the server?|||4Gb Physical memory - Windows 2000 Advanced Server
60Gb Drive of which 35Gb is free

The problem with having one of the jobs stop and restart the service is that this is my only server and it also holds the data for a reporting application. If I have to stop the service the reporting application is unavailable for the time it takes to restart it.

Its as if the ole db providers do not (immediately) release memory when the job has finished. Hence, when the next job starts it requests more memory etc..

OLE DB Provider Oracle

english:

Hello,

I have got a problem with the a linked server in SQL server 2005 (64 bit) to Oracle 8.0.5. (32 bit).

Further details:

The old existing SQL server 2000 (32 bit) has a linked server to Oracle 8.0.5. The works with the OLE DB provider from Microsoft. The new SQL server 2005 (64 bit) should also have a linked server to this Oralce database. I have installed the Oracle 8.0 clients on the new server. The connection to oracle works with the client tools from Oracle.

But the SQL server 2005 couldn't connect to the oracle database.

The error message from the SQL server sounds (translated form a german error message):

A instance of the OLE DB provider "OraOLEDB.Oracle" for the linked server "xxxxxx" can't create.

Is the Oracle client software / OLE DB provider the problem?

Exist an other OLE DB provider for Oracle, may be from Microsoft, which works?

Thanks for your help

german:

Hallo Zusammen,

ích habe ein Problem bei der Migration von SQL Server 2005 auf ein x64 Windows System.

Problem ist folgendes:
Auf dem alten Server Windows x32 lief SQL Server 2000 mit einem Verbindungsserver zum Oracle Server 8.05. Das funktionierte mit dem OLE DB treiber von Microsoft hervoragend. Nun haben wir aber eine neue Windows x64 Maschine und ich m?chte die Verbindung zum Oracle Server wieder nutzen. Hatte die Oracle Client Software installiert und bekomme eine Fehlermeldung:

Eine Instanz des OLE DB Anbieters "ORAOLEDB.ORACLE" für den Verbindungsserver "xxxxx" kann nicht erstellt werden.

Ist das ein Problem mit der Oracle Client Software bzw. dem zugrunde liegenden OLE DB Treiber?
Gibt es einen anderen OLE DB Treiber für Oracle, z.B. von Microsoft, der funktionieren k?nnte?

Bin schon l?nger damit besch?ftigt, das zum Laufen zu bekommen. Die Oracle Version ist nicht die neueste, ich wei?.
Was kann ich tun, damit das endlich l?uft.

Für Hilfen w?re ich sehr dankbar.

If you are running 64-bit SQL, you need to use a 64-bit OLEDB provider with linked servers.

So you need to get Oracle's 64-bit OLEDB Provider for Oracle, Microsoft does not provide a 64-bit OLEDB Provider for Oracle.

|||

I got it mostly working with the latest oracle client (10.2.0.3) which you can download from Oracle if you have support with them. If you don't have the .3 version, you'll encounter a bug with their client if you make connections in DTS / intergration services packages to the Oracle DB server. I'm still running into an issue with a remote link server (see my other post).

Here are the steps I did to make the client work. Hope this helps you.

1) To get it working you need to do a custom installation and select at minimum:

Oracle Windows Interfaces (all)

Oracle Database Utilities

SQL*Plus

Oracle Net

2) You then need to copy the files: tnsnames.ora AND sqlnet.ora from another DB server to where ever the Oracle client is installed. In my case it's located at: D:\oracle\product\10.2.0\client\NETWORK\ADMIN

3) You then need to modify the registry to change the dll's that MDAC uses to connect to Oracle:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

OracleXaLib=oraclient10.dll

OracleSqlLib=orasql10.dll

OracleOciLib=oci.dll

4) You then need to reboot to have the changes take effect.

5a) Browse to: /Server Objects/Linked Servers/Providers and right click on OraOLEDB.Oracle and select properties. Then check the following boxes: Dynamic parameter, Allow inprocess

5b) Then you need to create the link server in the Microsoft SQL Server Management Studio by doing the following:

Browse to: Server Objects/Linked Servers, right click and select "New Linked Server"

Enter the linked server name

Under provider select "Oracle Provider for OLE DB"

Enter a product name IE Oracle...

Enter the data source name (the same name in the tnsnames.ora file)

Select "Security" on the left side and select the last radio button "Be made using this security context" and enter the user name and password to connect to the remote db.

6) Then click OK :)

|||

Hello Arron,

thanks for the information.

I have installed the oracle version 10.2.0.1.0. That was the latest that I found on http://www.oracle.com/technology/software/products/database/oracle10g/index.html for Windows with 64-bit.

We haven't got a support contract with oracle, because it is an old version.

We wouldn't use DTS or integration services (would be nice if it's working also).
We need this only in stored procedures with SQL statements (SELECT, INSERT and UPDATE).

Stefan and MediaK

OLE DB Provider Oracle

english:

Hello,

I have got a problem with the a linked server in SQL server 2005 (64 bit) to Oracle 8.0.5. (32 bit).

Further details:

The old existing SQL server 2000 (32 bit) has a linked server to Oracle 8.0.5. The works with the OLE DB provider from Microsoft. The new SQL server 2005 (64 bit) should also have a linked server to this Oralce database. I have installed the Oracle 8.0 clients on the new server. The connection to oracle works with the client tools from Oracle.

But the SQL server 2005 couldn't connect to the oracle database.

The error message from the SQL server sounds (translated form a german error message):

A instance of the OLE DB provider "OraOLEDB.Oracle" for the linked server "xxxxxx" can't create.

Is the Oracle client software / OLE DB provider the problem?

Exist an other OLE DB provider for Oracle, may be from Microsoft, which works?

Thanks for your help

german:

Hallo Zusammen,

ích habe ein Problem bei der Migration von SQL Server 2005 auf ein x64 Windows System.

Problem ist folgendes:
Auf dem alten Server Windows x32 lief SQL Server 2000 mit einem Verbindungsserver zum Oracle Server 8.05. Das funktionierte mit dem OLE DB treiber von Microsoft hervoragend. Nun haben wir aber eine neue Windows x64 Maschine und ich m?chte die Verbindung zum Oracle Server wieder nutzen. Hatte die Oracle Client Software installiert und bekomme eine Fehlermeldung:

Eine Instanz des OLE DB Anbieters "ORAOLEDB.ORACLE" für den Verbindungsserver "xxxxx" kann nicht erstellt werden.

Ist das ein Problem mit der Oracle Client Software bzw. dem zugrunde liegenden OLE DB Treiber?
Gibt es einen anderen OLE DB Treiber für Oracle, z.B. von Microsoft, der funktionieren k?nnte?

Bin schon l?nger damit besch?ftigt, das zum Laufen zu bekommen. Die Oracle Version ist nicht die neueste, ich wei?.
Was kann ich tun, damit das endlich l?uft.

Für Hilfen w?re ich sehr dankbar.

If you are running 64-bit SQL, you need to use a 64-bit OLEDB provider with linked servers.

So you need to get Oracle's 64-bit OLEDB Provider for Oracle, Microsoft does not provide a 64-bit OLEDB Provider for Oracle.

|||

I got it mostly working with the latest oracle client (10.2.0.3) which you can download from Oracle if you have support with them. If you don't have the .3 version, you'll encounter a bug with their client if you make connections in DTS / intergration services packages to the Oracle DB server. I'm still running into an issue with a remote link server (see my other post).

Here are the steps I did to make the client work. Hope this helps you.

1) To get it working you need to do a custom installation and select at minimum:

Oracle Windows Interfaces (all)

Oracle Database Utilities

SQL*Plus

Oracle Net

2) You then need to copy the files: tnsnames.ora AND sqlnet.ora from another DB server to where ever the Oracle client is installed. In my case it's located at: D:\oracle\product\10.2.0\client\NETWORK\ADMIN

3) You then need to modify the registry to change the dll's that MDAC uses to connect to Oracle:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

OracleXaLib=oraclient10.dll

OracleSqlLib=orasql10.dll

OracleOciLib=oci.dll

4) You then need to reboot to have the changes take effect.

5a) Browse to: /Server Objects/Linked Servers/Providers and right click on OraOLEDB.Oracle and select properties. Then check the following boxes: Dynamic parameter, Allow inprocess

5b) Then you need to create the link server in the Microsoft SQL Server Management Studio by doing the following:

Browse to: Server Objects/Linked Servers, right click and select "New Linked Server"

Enter the linked server name

Under provider select "Oracle Provider for OLE DB"

Enter a product name IE Oracle...

Enter the data source name (the same name in the tnsnames.ora file)

Select "Security" on the left side and select the last radio button "Be made using this security context" and enter the user name and password to connect to the remote db.

6) Then click OK :)

|||

Hello Arron,

thanks for the information.

I have installed the oracle version 10.2.0.1.0. That was the latest that I found on http://www.oracle.com/technology/software/products/database/oracle10g/index.html for Windows with 64-bit.

We haven't got a support contract with oracle, because it is an old version.

We wouldn't use DTS or integration services (would be nice if it's working also).
We need this only in stored procedures with SQL statements (SELECT, INSERT and UPDATE).

Stefan and MediaK