i wish to work with linked servers in sql server. For this i have installed sql server 2008 enterprise version(with no error!) and oracle client successfully. Now when i open sql server management studio, and in the object explorer when i go to server objects -> linked servers -> providers, i can see the OraOLEDB.Oracle provider(i guess this means i have installed oracle clint without any error.). so far so good. the problem is when i try to create a new linked server by right clicking on the Linked servers link(server objects -> Linked servers). When i do the New Linked Server window opens up. I enter the Linked Server Name as : ORACLELINKEDSERVER Provider : Oracle Provider for OLE DB Product : Oracle DataSource : Oracle server Provider String : (i keep it blank)
click OK and the error dialog box appears saying :
The linked server has been created but failed to test the connection. Do you want to keep the linked server?
An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SQLServer.ConnectionInfo)
cant initialize the data source object of OLEDB provider "OraOLEDB.Oracle" for linked server ORACLELINKEDSERVER. ...
where am i getting wrong? what could be the reason for this error? how do i solve it?