Stored Procedure to Connect to another server

[DileepKumar]

Can anybody help me in writing a stored procedure which will update the data in another server

My problem is like this
I want to write a stored procedure which will update the data in another server.

Please help
Thanks in advance

 

[volodja]

In Enterprise Manager go to Security - Linked Srever right click and choose New Linked Server
Give the name of linked server
Microsoft OLE DB Provider for SQL SERVER
Product name and Data source: name of SQQL Server
Server option check RPC i RPC OUT

On your server create procedure for ex:
update LinkedServer.dbo.TableName
set FieldName1 = 123
where FieldName2 = 'please change me'

For table specification on other server always use
LinkedServer.dbo.TableName

 

[DileepKumar]

Tested it and it is working
Thank you very much for your tip

 

[vness]

One of my programmers is trying to extract data from tables in the Server1.DBX database into a temp table and use it to update the "tblname" table in the Server2\DB1 database.

I have tried changing the linked server as you suggested

update LinkedServer.dbo.TableName
set FieldName1 = 123
where FieldName2 = 'please change me'

and I get invalid object name errors.

If I use Server2.db1.dbo.tablename.columnname I get:

--The number name 'Server2.db1.dbo.tblname' contains more
--than the maximum number of prefixes. The maximum is 3.

But I can do select * from Server2.db1.dbo.tblname

UGH! What can I do?

 

[tlbroadbent]

1) Are both servers running SQL Server?
2) What version of SQL server are you running?
3) Is the Same version installed on both servers?
4) On which server are you trying to run the update?
5) Have you created the linked server on that server to the other server?
5) Can you post the actual query you are trying to use to update the table? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[vness]

1) Are both servers running SQL Server? - YES
2) What version of SQL server are you running? SQL 2K SP2
3) Is the Same version installed on both servers? YES
4) On which server are you trying to run the update? on the linked server - Server 2
5) Have you created the linked server on that server to the other server? Yes I have a link from on Server 1 to Server2
5) Can you post the actual query you are trying to use to update the table? Yes a modified version follows:

--Run on Server1/database1

update tblname1
set tblname1.volume = #temptable.volume, tblname1.updated_by = tablename1.SalesRepKey,
tblname1.updated_on = #temptable.SalesRepAve
from #temptable
where tblname1.shiptocode = #temptable._accountnum

--the select works
select * from Server2.database2.dbo.tblname2

 

[RiverGuy]

Try leaving out 'dbo'. Or possibly an OPENQUERY (SERVER2, blah.blah.blah.blah).

 

[tlbroadbent]

The Update query doesn't reference the remote server. I'm confused by your answer. The query updates a table on server1. You orignal post showed the table on Server2. You answered my 4th, "On which server are you trying to run the update?" with "on the linked server - Server 2."

If you run the update query on server1 and want to update the table on server2, try the following.

Update t2 set
volume = #temptable.volume,
updated_by = t2.SalesRepKey,
tblname1.updated_on = #temptable.SalesRepAve
From server2.db2.dbo.tablename2 t2
Join #temptable t1
Where t2.shiptocode = t1._accountnum Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.