?
Solved

problem opening a view from linked server

Posted on 2006-06-21
10
Medium Priority
?
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

762 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