How to execute one Stored Procedure inside other Stored Procedure?
NOTE: Both Stored Procedure's reside in different servers
views:
59answers:
2
+1
A:
The easiest, if you can on your system is (they must be linked - click the link for information on linking Servers):
EXEC [RemoteServer].DatabaseName.ObjectOwnerName.StoredProcedureName
Have a look here for more information .
Also, for getting away from hard-coding server names, look at Paul's answer here involving Synonyms.
Kyle Rozendo
2010-09-08 11:55:34
The brackets around the remote server name are only necessary if the name contains invalid identifier characters, just like with any other name in SQL. I see this a lot where I work, because we have a lot of servers with hyphens in the name, which require the brackets, so people end up using them for all server names, which is unnecessary ugliness, IMO.
P Daddy
2010-09-08 12:02:52
@Kyle: See my answer for a way to abstract the server name out of the stored procedure. That way you can move the code between environments and not have to modify the reference itself.
Paul Hadfield
2010-09-08 12:03:54
Also, the second to last part is only the database owner (dbo) if the stored procedure in question belongs to the database owner. In most designs, it does, but a more correct specification would be ObjectOwnerName.
P Daddy
2010-09-08 12:07:00
@Paul - Quite right, I should have mentioned Synonyms as we've been bitten there before. @P Daddy - Altered in accordance of your second comment. This is just a simple named structure to follow.
Kyle Rozendo
2010-09-08 12:18:15
+1
A:
Further to @Kyles answer, you can add a synonym to remove the need for the longer / linked structure. This abstracts the server being referenced. So you could move the SP from UAT to LIVE environment and the code wouldn't have to change. The synonym with in the two environments would be set up on each server to correctly reference the other server in that environment.
Paul Hadfield
2010-09-08 12:02:46
Synonyms should be a best practice, hard coding a 4 part name is just asking for trouble. +1
2010-09-08 12:06:00
@Daytona250: True, but it's knowing that functionality is available that's always the problem. It was only a new DBA coming into my previous role that highlighted it to the team. Prior to that we thought hard coded linking was the only option.
Paul Hadfield
2010-09-08 12:23:55