July 19, 2007 at 10:40 am
I have just installed my first sqlserver 2005 instance. I have had linked servers to oracle db set up and working well in sqlserver 2000. I have installed MDAC 2.8, oracle 9.x client tools, verified connection to oracle via sql*plus successfully, set up the connection with the below information.
Provider: Microsoft OLE DB Provider for Oracle
Product Name: oracle
Data source: PROD
Provider string: MSDAORA
"security"
* be made using this security context
username/pwd
When trying to test the connection, I receive the below error: Any ideas?
"The test connection to the linked server failed."
Additional Information
An exception occured while executing a transact-sql statement or batch (microsoft.sqlserver.connectioninfo)
Cannot initialize the data source object of oledb provider "msdaora" for linked server ACU_PROD",
OLEDB provider "MSDAORA" for linked server "ACU_PROD" returned message "oracle error occurred, but error message could not be retrieved from oracle (microsoft sqlserver error: 7303.
July 19, 2007 at 11:40 am
I have the same setup, but in Security I did specified a local login, remote login and remote password. When using this linked server I do have to connect Management Studio to the local server as this local login.
I if I try to login as another login, the error is:
Msg 7416, Level 16, State 2, Line 1 Access to the remote server is denied because no login-mapping exists.
which is not your case, so it does not even try to get to the authentication.
I can guess that maybe you have to go to Surface Area Configuration tool and enable the first option Ad Hoc Remote Queries.
Let me know if it helped.
Regards,Yelena Varsha
July 19, 2007 at 2:10 pm
I enabled the ad hoc remote queries, restarted the server, tried to connect to Oracle tables and received the below error
Cannot initialize the data source object of ole db provider "msdaora" for linked server MS error 7303.
Shelley
July 20, 2007 at 9:18 am
To get the linked server to work correctly, I made the following changes.
Under Server Objects, Linked Servers, Providers, MSDAORA
Right click MSDAORA
Properties
"uncheck" "allow inprocess
Restart Server
July 20, 2007 at 10:51 pm
This is what I use. In Query Analyzer
sp_addlinkedserver ORALINK,Oracle,MSDAORA,ORCL
go
sp_addlinkedsrvlogin ORALINK,false,null,USER,PASSWORD
go
ORALINK is a name of your choice and will be used by a SQL Server query
Oracle is a keyword and should not be changed
MSDAORA is a keyword and should not be changed
ORCL refers to the ...\network\admin\tnsnames.ora entry that points to an Oracle instance
USER,PASSWORD refer to the Oracle table owner and that user's password
Once you ran the above you can do
select * from ORALINK..USER.sometable
Is this any different from what you are doing?
July 24, 2007 at 10:39 am
I do have 'Allow Inprocess' checked and it works.
What version of Oracle you are connecting? We are connecting to 10G
Regards,Yelena Varsha
July 24, 2007 at 10:44 am
I am connecting to 10G also.
July 24, 2007 at 1:18 pm
I asked our Oracle admin and he explained that the Oracle server may be set up to allow all users connect to one process or to have each user have his / her own process. He told me that my Oracle server is set up so each user has his own connection. In this case I think the provider settings can be overruled by the server settings. If your Oracle server is set up differently, then checking 'Allow Inprocess' may direct your connection to the common process and it does not exactly work. What do you think?
Regards,Yelena Varsha
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply