I execute a simple query on the linked server that uses a User that is mapped across the two servers this errors. I tried Test Link Server connection which works ok.
What is confusing is that something I stumbled across on the WEB was to try using OPENQUERY which did work.
the following errors returned when I executed my query not using the OPENQUERY method.
OLE DB provider "SQLNCLI10" for linked server "xxx.xxx.x.x" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "xxx.xxx.x.x" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 2 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "192.168.9.5". The provider supports the interface, but returns a failure code when it is used.