I am trying to create a link server from SQL Server 2005-A to another SQL Server 2005-B which is on named instance.e.g HPSR080001\Artenaprod. I am able to create a link from SQL Server A but I am unable to get the option 'test connection'. It does not come up with any error and I am not sure if the connection is been made. I think NOT.
I have tried using both options which are SQL Server and Other Data Source but no joy. The settings which I have put in for the Data Source are:
Provider: OLE DB Provider
As for the security, I am using the option 'be made using this security context' whereby I have put in the SQL Server B 'sa' username and 'password'.
I have tried on my own and I am able to make a test connection if both of my SQL server is on default instance.
Long story short, how do I make a connection for a named instance?