CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Nanda
Nanda

Reputation: 241

Insert value to another sqlserver db

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?

Upvotes: 0

Views: 117

Answers (3)

Nathan
Nathan

Reputation: 160

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.

Upvotes: 0

anon
anon

Reputation:

Yes, please look into linked servers:

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

Upvotes: 3

Joel
Joel

Reputation: 438

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.

Upvotes: 0

Related Questions