How to get the return value from Exec Executed as command at the Linked Server

Question

Post reply

How to get the return value from Exec Executed as command at the Linked Server

subodhk

SSC-Addicted

Points: 401
More actions
October 29, 2007 at 9:47 am

#62625

Hi,
I am being barged by this problem since 2 days. Any help will be greatly appreciable.
Problem is
There is a server A and a linked Server B.
Now, I have an StoredProcedure sp_CreateObjects in A.
I want to execute it in B.
The method I figured out is sp_executesql at the linked server.
for example.
set @sql = 'EXEC A.master.dbo.sp_executesql N'EXEC sp_CreateObjects'
now i have to ececute this sql like
EXEC(@sql)
Now how can I get a return value from the storedProcedure at server A.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

try this

declare @returnValue varchar(50)--make the datatype match your output

EXEC @returnValue = A.master.dbo.sp_CreateObjects--assuming your crate objects is in master database on serverA

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Adam Angelini (10/29/2007)
try this
declare @returnValue varchar(50)--make the datatype match your output

ReturnValue from ANY stored procedure ALWAYS is int.

_____________
Code for TallyGenerator

subodhk SSC-Addicted Points: 401 More actions · Answer 3

Thnks for the reply, but the problem is little different.

Actually I have to create objects at the linked server that is B.

I have a script that takes 4 inputs.

SourceServer = A

DestinationServer = B

SourceDatabase = C

DestinationDatabase = D

Now I have a Stored Procedure in SourceServer A which I need to run at B.

But I dont want to hard code the values.

For example I shall declare an

Declare @sql varchar(100)

and then

set @sql = 'EXEC ' + @DestinationServer +'.master.dbo.sp_executesql N'' EXEC sp_CreateDataObjects 'ArchiveServerDatabaseName''

now as i have set the sql i will execute it by

EXEC (@sql)

The first Exec will execute the storedProcedure and the stamenet passed as the parameter to Execute the stored procedure will be executed and it will return some value. Now i have to trap that value and check whether there is any fault while creation or something else.

After this