November 16, 2005 at 9:48 am
Hi
The environment has two servers server1 and server2 and both servers have completely identical databases 'database1'. Server2 is registered on server1 using windows login authentication. Now, I am retrieving the values from table1 on database1 on both servers using below query.
SERVER1:
SELECT * FROM SERVER1.DATABASE1.DBO.TABLE1
SERVER2:
SELECT * FROM SERVER2.DATABASE1.DBO.TABLE1
but if I run the below query then I am getting an error message
SELECT * FROM SERVER1.DATABASE1.DBO.TABLE1 WHERE COL1 NOT IN
(SELECT COL1 FROM SERVER2.DATABASE1.DBO.TABLE1 )
ERROR MESSAGE
Server: Msg 7411, Level 16, State 1, Line 1
Server 'SERVER2' is not configured for DATA ACCESS.
I would really appreciate if you can suggest me how to configure the server security / data access to execute the above given query.
Thanks,
November 17, 2005 at 6:12 am
We have a similiar setup so I ran your query [SELECT * FROM SERVER1.DATABASE1.DBO.TABLE1 WHERE COL1 NOT IN
(SELECT COL1 FROM SERVER2.DATABASE1.DBO.TABLE1 )] on our 2 db servers. The query returned the results fine (the results came back fine w/o any errors].
Could the problem be with the user account that the linked server is running as? Check the properties to see what account the "linked server" is running as. In SQL EM, under the server name (for which you are running the query), double-click on "linked servers", right-mouse click on the linked server for which you are querying against and choose "Properties.
Hope this helps.
Norene Malaney
November 17, 2005 at 6:58 am
Check SERVER1 has Data Access checked in Server Options for the linked server SERVER2
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply