How to create a remote server link to update data?

[fredong]

I am trying to create a link from my local SQL 2000 server to a SQL 2000 remote server. I need help how to create the remote connection so I can schedule a to run a script to update the tables in my remote server database. What is the best way of do this? I appreciate if someone can help me.Thanks.

 

[jimmYLepp]

the procedure you need is sp_addlinkedserver take a look for it in BOL to see how it works, also, you would will refer to the linked tables as server_name.database_name.dbo.table_name. One piece of advice, have all your remote servers statrup under the same account on the same domain.



jimmY

 

[ClaireHCM]

If you are not familier with T-SQL setting for linked server/remote server,use Enterprise manager -- Security --Add linked server/remote server might be easier for you.For sql server no need of chossing ole db provider,directly choose SQL Server and type the real server name.

 

[fredong]

I went to Enterprise Mananger and right click on the My Servername(Server A) and choose Properties and click on the security tab. I do not see any Add linked server/remote server as you mentioned above. Did I go to the wrong place? Please advise. Thanks.

 

[ClaireHCM]

You should expand the server you are going to add a linked serevr on it.
Then you will see a folder called "Security" -- Expand it
Then you will see a folder called "Linked server" -- Right click on it and choose "New linked server",
Then a dialog will prompt up.check the SQL Server Cylindrical,in Linked Server column type the remote server name(need the real name of it,cant be named by you)
Then go to Security tab.check the last cylindical,give the accessiable login to that remote server and pwd of it.Then go to Server option tab,check all the rows.This should work!(I believe)