Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PeterG
Posting Yak Master
156 Posts |
 Posted - 2004-02-10 : 12:39:15
|
| I want to create a stored proc that would select from serverA.db.table and if record exists, insert a record in serverB.db.table. Both servers are on the network.Create Stored procedure ...IF EXISTS (Select 1 From serverA.db.table where field1 = '12345') |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-02-10 : 12:44:37
|
| Ok, there is a stored procedure in server B that I can call. My stored proc would now be:IF EXISTS (Select 1 From serverA.db.table where field1 = '12345')EXEC serverB.db.dbo.StoredProc param1, param2, param3 ...Is syntax correct? |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-10 : 12:46:05
|
| Did you set up a linked server?Brett8-) |
|
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-02-10 : 13:05:33
|
| Thanks Brett,Do I exec sp_addlinkedserver in my stored proc, before I exec the stored proc? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-10 : 13:33:01
|
| Its a 1 time setup thing...don't forget to use sp_addlinkedsrvloginThe reference the table using the 4 part naming convention...look in BOLBrett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-10 : 13:45:41
|
Actually...You need to do a call...and use ODBC...Look up Remote stored procedure call in BOLPart of BOLquote:
Calling a Stored ProcedureThe Microsoft® SQL Server™ ODBC driver supports both the ODBC CALL escape sequence and the Transact-SQL EXECUTE statement for executing stored procedures; the ODBC CALL escape sequence is the preferred method. Using ODBC syntax enables an application to retrieve the return codes of stored procedures and the SQL Server ODBC driver is also optimized to use a protocol originally developed for sending remote procedure (RPC) calls between SQL Servers. This RPC protocol increases performance by eliminating much of the parameter processing and statement parsing done on the server.The ODBC CALL escape sequence for calling a procedure is:{[?=]call procedure_name[([parameter][,[parameter]]...)]}
Brett8-)EDIT Also check out BEGIN DISTRIBUTED TRANSACTIONIn BOL...there's an example there... |
|
|
|
|
|
|
Can I do this?