Insert value to another sqlserver db

Question

I have 2 SQL Servers:

1. temp1 XX.13.23.2
2. temp2 XX.23.45.6

The temp1 server has a database called db1 and contains a procedure called p1. I want that procedure to insert the value on Temp2 server Database name db2 on table T1.

Is it possible to use procedure to insert value on another server's database?

If this is this possible then can someone provide me with an idea or some examples on how to achieve this?

Answer 1

You can call a remote stored procedure from the instance you want to insert to:

exec [RemoteServer].DatabaseName.DatabaseOwner.StoredProcedureName

You need to have the RemoteServer set up as a linked server.

Answer 2

Yes, please look into linked servers:

http://msdn.microsoft.com/en-us/library/ms188279%28SQL.90%29.aspx

Answer 3

Another option, especially if you're going to have a development version of the procedure where you're going to want to do tests and you don't want touching a production environment, would be to use SQL Server synonyms: http://technet.microsoft.com/en-us/library/ms177544.aspx.

I personally like using them because once the proc is initially setup to use them, you won't have to change the SQL in the procedure.