Task 8: Linked Servers

• Trying to do some changes on the production server I got the following error that should occur when you try to access the linked server.

TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "MSDASQL" for linked server "Name" reported an error. The provider did not give
any information about the error. Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "Name". (Microsoft SQL Server, Error: 7399)

• What I found is: 

1. Restart the database
2. Toggle the value of link server time-out 

         sp_configure 'remote query timeout', 1 
         go 
         reconfigure with override 
         go
         sp_configure 'remote query timeout', 0
         go 
         reconfigure with override 
         go
3. Ask your client to provide you the linked server in order to see the expanded table list 
4. Check if the user is not blocked on that server