Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

problem opening a view from linked server

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
johnnyg123
Asked:
johnnyg123
  • 7
  • 3
1 Solution
 
johnnyg123Author Commented:
I should add all sql services are running on both servers.


0
 
QPRCommented:
sp_serveroption 'astute01','Data Access True'
0
 
johnnyg123Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
QPRCommented:
Have a look in BOL for sp_serveroption
Data access true: Enables and disables a linked server for distributed query access.
0
 
johnnyg123Author Commented:
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
 
johnnyg123Author Commented:
does the server need to be rebooted?
0
 
johnnyg123Author Commented:
well...tried rebooting astute01 and still get

Server 'astute01' is not configured for data access


please help!!!!!
0
 
QPRCommented:
Strange.
Open Enterprise Manager and expand the server that webrpd01 lives on - under linked servers can you see astute01?
0
 
johnnyg123Author Commented:
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
 
johnnyg123Author Commented:
Since this has turned in to a different problem I'm going to close this out and open a new one
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now