I want to access data in one server from another. Both these servers are coming in the same server group. Is there any way to do this?
What do you mean with "the same server group?"
If there's a network connection between the two servers, it's possible to define Server1 as a Linked Server in Server2. You can also access data on a remote server using OPENROWSET.
Read more about both Linked Servers and OPENROWSET at Tech Republic: http://blogs.techrepublic.com.com/datacenter/?p=133
they are Linked Servers.
Actually I tried out the option OPENDATASOURCE('SQLOLEDb','Data Source=Server_name;Integrated Security=SSPI').DATABASE_name..table_name
I am actually trying to access data from Production server and the above command is giving me error Server: Msg 18452, Level 14, State 1, Line 1 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The same command went fine in Staging Server. Is there any ID and Password which I need to supply? I am not able to open the link supplied from you from office. Will read it from home
You'll need to supply a userid and password unless you're logged in with a Windows account to the SQL Server you're on, and that WIndows account has access also to the remote server you're connecting to. If you use linked server, you have the possibility to define a login which will always be used in connections to the linked server.
No one has followed this question yet.