Solved

problem opening a view from linked server

Posted on 2006-06-21
10
214 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

10 Experts available now in Live!

Get 1:1 Help Now