Menu
I want to call a sproc on server B from server A in TSQL without linking the servers. Is it possible to use something like a connection string to execute this sproc? The return will be a single nvarchar value.
Regards.
961
In large database applications, it is common to call one stored procedure from another stored procedure.
Here is an example of how to call a stored procedure inside another stored procedure. This is also known as nested stored procedures in SQL Server. Step 1: Create two simple stored procedure to insert some data into two different tables. both accept four parameters to insert the data.
The alternative to using Linked Servers is to use the OPENQUERY statement, also known as a pass through query. When using an OPENQUERY statement, the WHERE clause gets executed at the remote server and the resultant (mapped) records traverse over the wire instead of an entire sourced data set.
It is quite possible that a MySQL stored procedure can call another MySQL stored procedure inside it. To demonstrate it, we are taking an example in which a stored procedure will call another stored procedure to find out the last_insert_id.
To avoid "linked servers", you'd normally use OPENDATASOURCE
After comment:
EXEC OPENDATASOURCE('SQLNCLI', 'Data Source=London\Payroll;Integrated Security=SSPI').remoteDB.remoteSchema.remoteProc @param1, @param2,...
Simple 4 part naming convention. The whole OPENDATASOURCE simply replaces the linked server name...
Note: you may have issues with "adhoc access"
141
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
