Solved

problem opening a view from linked server

Posted on 2006-06-21
10
223 Views
Last Modified: 2010-08-05
I added a linked server (astute01) to server webprd01 (I ran sp_addserver @server = 'astute01')

when I try to execute a view located on astute01 from webprd01


I am getting the following error message:

Server 'astute01' is not configured for data access
0
Comment
Question by:johnnyg123
  • 7
  • 3
10 Comments
 

Author Comment

by:johnnyg123
ID: 16955751
I should add all sql services are running on both servers.


0
 
LVL 29

Accepted Solution

by:
QPR earned 500 total points
ID: 16955762
sp_serveroption 'astute01','Data Access True'
0
 

Author Comment

by:johnnyg123
ID: 16955847
This may seem like a silly question but .....


is the data access not set on by default?

I'm trying to evaluate  what the pitfalls are by turning this on.

0
 
LVL 29

Expert Comment

by:QPR
ID: 16955856
Have a look in BOL for sp_serveroption
Data access true: Enables and disables a linked server for distributed query access.
0
 

Author Comment

by:johnnyg123
ID: 16955984
I ran sp_serveroption 'astute01','Data Access', 'True' in query analyzer ( using master db) and got a message indicating it was successful but I still get same error.


Is there a sp to run to check server settings?


Thanks!
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:johnnyg123
ID: 16959600
does the server need to be rebooted?
0
 

Author Comment

by:johnnyg123
ID: 16959998
well...tried rebooting astute01 and still get

Server 'astute01' is not configured for data access


please help!!!!!
0
 
LVL 29

Expert Comment

by:QPR
ID: 16963578
Strange.
Open Enterprise Manager and expand the server that webrpd01 lives on - under linked servers can you see astute01?
0
 

Author Comment

by:johnnyg123
ID: 16984813
Well...I think I solved one problem and have a different problem.

I was thinking maybe I needed to delete astute01 as a linked server (after running sp_serveroption 'astute01','Data Access', 'True') and re add it so I did.

This got rid of the Server 'astute01' is not configured for data access error message but now if I try to run the following query:

select * from astute01.epcprod.dbo.action

in query analyzer on webprd01 after about 45 seconds I get

Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.

if I run the same query in query analyzer on astute01...works fine

I saw some posts on the new error and one of the suggestions was to run telnet and see if I get blank screen to rule out firewall issue.  I did run telnet and I do get blank screen.  

Any ideas?
0
 

Author Comment

by:johnnyg123
ID: 16985045
Since this has turned in to a different problem I'm going to close this out and open a new one
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now