Openrowset Issue - SQL Server Forums

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)

Openrowset Issue

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-07-08 : 20:24:19

Hi,

I am trying to access remote db server's stored procedure and am using openrowset for that. i am aware of that i can use linked server. but due to some reason my customer doesn't want to use that. Here is my sample code

server1
db: Test

declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
  EXEC('SELECT *
            FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
            ''
            SET NOCOUNT ON;
            set fmtonly off exec Employee.[dbo].TestInsert ' + @IdEmployee + ',' + @Name+ '
            ''
            ) AS a;')

server2 (170.30.149.34)
db: Employee

Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))

as 

BEGIN

 Insert into Emp(IdEmployee,Name) values(@IdEmployee,@Name)

END

When i execute the query it throws an exception

quote:
The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object

any suggestion or correction please

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources