Openrowset error with linked server

hi all,

i trying run stored procedure passing parameter using openrowset.
note: running below query datasrv2 . datasrv1 & datasrv2 configured linked server between eachother. also openrowset enabled at server level.

select  *

from    openrowset('sqlncli', 'server=datasrv1;uid=report;pwd=passoword',

'set nocount on;set fmtonly off;exec datasrv2.db_report.dbo.usp_search_sprocedures ''pefr''')

error:

but getting following error:

msg 7357, level 16, state 2, line 2<br>cannot process the object "set nocount

on;set fmtonly off;exec dataserv2.db_report.dbo.usp_search_sprocedures 'pefr'".

the ole db provider "sqlncli" for linked server "(null)" indicates that either

the object has no columns or the current user does not have permissions on that

object.

but when run above stored procdure through management studio using same credentials , runs fine. below code using run directly :

command:

exec datasrv2.db_report.dbo.usp_search_sprocedures 'pefr'

thanks help.

 

stored proc. dbo.usp_search_sprocedures deployed on dataserv1  in code, stroed proc has references  dataserv2 through linked server thru 4 part naming.

normal call stored proc using exec works fine if use openrowset, if fails.

thanks help.

SQL Server  >  SQL Server Data Access