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
johnnyg123Asked:
Who is Participating?
 
QPRConnect With a Mentor Commented:
sp_serveroption 'astute01','Data Access True'
0
 
johnnyg123Author Commented:
I should add all sql services are running on both servers.


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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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
All Courses

From novice to tech pro — start learning today.