How to call SP of one SQL server from other SQL server

Hi,

In my application environment, we have data will be in available in different SQL servers.

I hava requirement to call the SP of on SQL server from the SP in the other SQL server.

Can any one help me out for a solution which will be faster?

Please note that I cant go for the linked servers as the other SQL severs credentials will be stored in a Database table and it has to be used in run time of the SPs.

Awaiting quick response from you all..

Thanks in advance.

-Thams

YOu can use the openrowset for that:

DECLARE @.USERNAME VARCHAR(50)

DECLARE @.Password VARCHAR(50)

DECLARE @.SQLString VARCHAR(500)

SET @.USERNAME = 'SomeUser'

SET @.Password = 'SomePassword'

SET @.SQLString = '

SELECT a.*

FROM OPENROWSET(''SQLNCLI'', ''Data Source=.;UserName=' + @.USERNAME + ';Pwd=' + @.Password + ';'',

''EXEC Northwind.dbo.[Ten Most Expensive Products]'') AS a;

'

SELECT @.SQLString

EXEC(@.SQLString)

Jens K. Suessmeyer

http://www.sqlserver2005.de