Execute SP on different server

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Execute SP on different server

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-06-13 : 08:07:26

I have two SQL Servers
Server 1 SQL2008 R2
Server 2 SQL2008 R2

Is it possible for SERVER 1 to execute a stored Procedure on Server 2 and if so how?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 08:21:22

If you had another server as linked server, then you can execute..

1) SERVER1 should have linked server connection to SERVER2...
2) from server1 you can execute procedure which is in SERVER2 by using fully qualified name (SERVER1.DBName.SchemaName.ProcedureName )

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-06-13 : 08:28:37

quote:
Originally posted by bandi

If you had another server as linked server, then you can execute..

1) SERVER1 should have linked server connection to SERVER2...
2) from server1 you can execute procedure which is in SERVER2 by using fully qualified name (SERVER2.DBName.SchemaName.ProcedureName )

--
Chandu

Too old to Rock'n'Roll too young to die.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 08:29:44

It was a typo.........

quote:
Originally posted by webfred

quote:
Originally posted by bandi

If you had another server as linked server, then you can execute..

1) SERVER1 should have linked server connection to SERVER2...
2) from server1 you can execute procedure which is in SERVER2 by using fully qualified name (SERVER2.DBName.SchemaName.ProcedureName )

--
Chandu

Too old to Rock'n'Roll too young to die.

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-06-13 : 08:32:16

I know

quote:
Originally posted by bandi

It was a typo.........
quote:
Originally posted by webfred

quote:
Originally posted by bandi

If you had another server as linked server, then you can execute..

1) SERVER1 should have linked server connection to SERVER2...
2) from server1 you can execute procedure which is in SERVER2 by using fully qualified name (SERVER2.DBName.SchemaName.ProcedureName )

--
Chandu

Too old to Rock'n'Roll too young to die.

--
Chandu

Too old to Rock'n'Roll too young to die.

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-06-13 : 08:39:32

cheers guys, will try that now

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 08:46:54

Another method is by using Distributed query
http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/

--
Chandu

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-06-13 : 09:05:20

Hi guys,

i have tried that and come up with this error

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVER 2" was unable to begin a distributed transaction

On server 1 I have a trigger

ALTER TRIGGER [dbo].[CRM_Customer_Add]
ON [dbo].[tbCBI_client]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @nLicence varchar(6)
Select @nLicence = Licence from Inserted
EXEC [SERVER 2].[MYDATABASE].[dbo].[Exec_DTSXRunner] @licence = @nLicence

END

Exec_DTSXRunner fires a dtsx. If i execute Exec_DTSXRunner direct from Server 2 it works fine.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 09:09:51

Refer
http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/523116d4-4c8e-43a8-abf4-e705ffaa68c8
--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources