Getting Return value from a dynamic sql stored procedure SQL Server 2008

[stainless]

As a result of a migration process from SQL Server 2000 to 2008. we have stored procedures that are going to be held on a linked server and thus are building dynamic sql combining the server name in the environment we are in into the call.

I know there are many other soplutions but the big picture is we have to have a variable linked server name in each environment.

e.g. set @SQLcommand = @linkedservername + '.dbo.MyStoredProcedure ' + @someparametervalue

The issue we habe is we want to know what the Return value integer is from the called stored procedure to trap any errors.

If it was local, it would be as simple as "select @RC = dbo.MystoredProcedure @someparametervalue"

However, I am unsure how we can code this to get the @RC value from the @SQLcommand stored procedure call.

Is there a way of putting @RC into the dynamic SQL and referencing this in the calling SQL?

Have tried various versions of "Select @RC = Exec(@SQLcommand)" statements but none work.

I know this must be simple but have struggled to find examples in Google. Any ideas?

Cheers

Mark

[stainless]

Just need to clarify, the call using dynamic sql is on a SQL Server 2000 server and the stored procedure being called is on a SQL Server 2008 server, so we cannot use sp_executesql which is not avaiable in SQL Server 2000

[Erland Sommarskog]

Post by stainless
Just need to clarify, the call using dynamic sql is on a SQL Server 2000
server and the stored procedure being called is on a SQL Server 2008
server, so we cannot use sp_executesql which is not avaiable in SQL
Server 2000

sp_executesql was added in SQL 7.

Then again, you don't need dynamic SQL at all.

Just do:

SELECT @spname = @linkedserver + '.dbo.some_sp'
EXEC @ret = @spname @param1, ....


I predict that you will have a lot of pain with your linked servers. Linked
servers often incurs pain. Good luck!