Linked Server is not configured for data access Error: 7411 in SQL Server 2005

I wrote one article to set Linked server at:

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

Sometime you properly set the link server and while trying to execute SELECT statement over it, it may show you following error.

Msg 7411, Level 16, State 1, Line 2

Server ‘LinkToAceess’ is not configured for DATA ACCESS.

Solution of this error is really pretty much simple. Before we move to solution of error I would like to raise this error. Run the linked server example given in above article and see whether it is working properly. One you done with that let us change one property of Linked Server which will raise above said error.

–set data access property to ‘false’ which will generate error

–when you try to execute any SELECT statement against your linked server

exec sp_serveroption [LinkToAceess],‘Data Access’,‘false’

Now try to execute SELECT statement and greet the error:

Msg 7411, Level 16, State 1, Line 2

Server ‘LinkToAceess’ is not configured for DATA ACCESS.

Solution is so simple; just enable your data access to linked server with following command.

exec sp_serveroption [LinkToAceess],‘Data Access’,‘true’

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Advertisements