SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to access database in another machine?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jeff06
Posting Yak Master

166 Posts

Posted - 05/17/2007 :  13:36:42  Show Profile  Reply with Quote
machine A and B in the same network.
A has dbs A1, A2,A3...
B has dbs B1 B2 B3....

I conect to B first using sql query analyzer. how I can access A1 in A?
Thanks.

Jeff

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/17/2007 :  13:38:41  Show Profile  Reply with Quote
On ServerB make a Linked Server for ServerA

Then you can access TableA in DatabaseA as:

SELECT * FROM ServerA.DatabaseA.dbo.TableA

Kristen
Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 05/17/2007 :  13:46:34  Show Profile  Reply with Quote
AFTER RUN SELECT * FROM ServerA.DatabaseA.dbo.TableA
I got following msg:

Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'serverA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

How can i fix this problem?
Thanks
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/17/2007 :  13:48:05  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Doesn't the error message tell you exactly what to do? As did Kristen.

Kristen:

"On ServerB make a Linked Server for ServerA"

Error Message:

"Could not find server 'serverA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/17/2007 :  13:55:43  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Of course, if you are not familiar with sp_addlinkedserver, just google it:

http://www.google.com/search?q=sp_addlinkedserver



- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 05/17/2007 :  14:01:34  Show Profile  Reply with Quote
Thank you for your response:
When I do:
EXEC sp_addlinkedserver @server='serverA'

i got:
User does not have permission to perform this action.

I have read right to both database ( A and B). Do this mean I have to ask the DBA to give me some rights to run sp_addlinkedserver ?
Thanks.
JEff
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 05/17/2007 :  14:07:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
The DBA will need to setup the linked server for you.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 05/17/2007 :  14:08:27  Show Profile  Reply with Quote
Thank you for your response:
When I do:
EXEC sp_addlinkedserver @server='serverA'

i got:
User does not have permission to perform this action.

I have read right to both database ( A and B). Do this mean I have to ask the DBA to give me some rights to run sp_addlinkedserver ?
Thanks.
JEff
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/17/2007 :  14:08:38  Show Profile  Reply with Quote
"Do this mean I have to ask the DBA to give me some rights to run sp_addlinkedserver"

Yup, I expect you don't have permissions to create a linked server.

Personally I don't think that:

EXEC sp_addlinkedserver @server='serverA'

is "rich" enough - in terms of what I consider to be the minimum requirements of a linked server.

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000