Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

problem opening a view from linked server

Posted on 2006-06-21
10
Medium Priority
?
261 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 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