Executing SP on a remote server

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 2000 Forums

Transact-SQL (2000)

Executing SP on a remote server

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2011-03-28 : 13:04:23

I'm trying to figure out how to call SP on server A from within a SP on server B.

So far I have this:

CREATE PROCEDURE [dbo].[p_upd_lim_tr](
  @lim_id varchar(30),
  @lim_low_num int,
  @lim_hgh_num int
) AS

SET NOCOUNT ON

declare @sql_qry varchar(1000)

set @sql_qry = '''[someDB].[dbo].p_upd_lim '''
    + cast(@lim_low_num as varchar) + ', '
    + cast(@lim_hgh_num as varchar) + ''''''

print @sql_qry

EXEC ('SELECT * FROM OPENQUERY([LNKDSRVR],' + @sql_qry + ')')
-- rest omitted

For sample parameters I'm using, @sql_qry is printed as:

'[someDB].[dbo].p_upd_lim '120, 140''

So perhaps it's not really surprising that it doesn't work - but I tried various combinations of quotes, with no success. How should this string be formatted for it to work? Or is there some other way of running an SP from remote (linked) server and passing parameters to it?

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-28 : 13:11:14

Try EXEC [LNKDSRVR].[someDB].[dbo].p_upd_lim '120, 140'

ch9862
Yak Posting Veteran

76 Posts

Posted - 2011-03-28 : 19:17:28

quote:
Originally posted by robvolk

Try EXEC [LNKDSRVR].[someDB].[dbo].p_upd_lim '120, 140'

Thanks. Actually, I had too many quotes around the parameters.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources