Executing Stored Proc over linked 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

SQL Server Development (2000)

Executing Stored Proc over linked Server

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2005-03-23 : 02:22:25

There is a stored procedure that I need to execute over a linked server and I am struggling to find the best way of doing this.

The stored procedure is on a server with a link name of 'MainServer' I know I can execute it using OPENQUERY but that would require me to create a stored procedure on my side which I don't want to do.

Is there anyway to execute this stored procedure like I would over different databases on the same server ie. MainServer.dbo.StoredProc @var = 'wewe'?

I tried this but it doesn't work, how else could I do it?

Thanks,
Gavin

Kristen
Test

22859 Posts

Posted - 2005-03-23 : 03:45:20

I have a feeling there is a problem trying to do this, but I can't rememer exactly what. But you would certainly need this syntax change, if you didn't already try it:

EXEC MainServer.MyDatabaseName.dbo.StoredProc @var = 'wewe'?

Kristen

Mannga
Yak Posting Veteran

70 Posts

Posted - 2005-03-23 : 05:50:20

Thanks Kriten but now I am being told that my SQL is nto setup for RPC and the DBA's say we can't for security reasons, I guess I will have to create a new stored proc...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-23 : 06:02:05

USE pubs

GO

SELECT a.*

FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',

'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a

GO

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-23 : 06:44:03

select * FROM OPENROWSET(
'SQLOLEDB','ServerName';'sa';'pwd',
'exec master..xp_cmdshell ''dir D:\'' select convert(nvarchar(255), null) AS [output]')

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-23 : 08:17:38

select * into #t
FROM OPENROWSET('SQLOLEDB', 'anyServer'; 'sa'; 'pwd',
'
exec sp_helprole
select
cast(null as sysname) RoleName,
cast(null as smallint) RoleId,
cast(null as int) IsAppRole
')

select * from #t
drop table #t

OR SIMPLY:

select * into #t
FROM OPENROWSET('SQLOLEDB', 'anyServer'; 'sa'; 'pwd',
'
exec sp_helprole
select null, null, null
')

select * from #t
drop table #t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources