EXECUTE MySproc AT @MyLinkedServer

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 MySproc AT @MyLinkedServer

Author

Topic

Kristen
Test

22859 Posts

Posted - 2011-11-18 : 11:10:15

What's the best way to execute an Sproc on a @MyLinkedServer (i.e. Linked Server name only known in a @Variable) ?

I rashly thought that this would work:


EXEC @intErrNo = dbo.MySproc
	@Param1 = @Param1,
	@Param2 = @Param2,
	...
AT @MyLinkedServer

but I've read BoL more carefully now

Many thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 11:13:16

Define "best".
Put it all in dynamic sql?
Probably come up with other errors though.

Could also create a linked server with a specific name - might be an issue if needed at the same time on diffenrent connections).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2011-11-18 : 12:25:42

I think dynamic SQL is going to be a problem with all the parameters and escaping them etc.

By "Best" I was after whatever you guys have used and found to work best

What I had tried (which seems to work OK) is:


DECLARE	@strSQL Nvarchar(4000)

SELECT	@strSQL = N'EXEC [' + @MyLinkedServer
	+ N'].MyRemoteDatabase.dbo.MySproc
	@Param1 = @Param1,
	@Param2 = @Param2 OUTPUT,
	@strErrMsg	= @strErrMsg OUTPUT,
	@intRetVal	= @intRetVal OUTPUT'

EXEC @intErrNo = sp_ExecuteSQL
	@strSQL,
N'@Param1 	int,
@Param2		int OUTPUT,
@strErrMsg	varchar(8000) OUTPUT,
@intRetVal	int OUTPUT',
	@Param1 = @Param1,
	@Param2 = @Param2 OUTPUT,
	@strErrMsg	= @strTempErrMsg OUTPUT,
	@intRetVal	= @intTempRetVal OUTPUT

my only (so far ...) gripe with this is the need to maintain the definition list if the Sproc's parameter's definitions change

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources