Answered by:

OpenRowSet Issue

Question

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

[quote]Msg 7357, Level 16, State 2, Line 1
Cannot process the object "
SET NOCOUNT ON;
set fmtonly off exec Employee.[dbo].TestInsert 11,'Peter'
". 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.
[/quote]

but insert is working fine. why it throws this error. how to avoid this error .

If i don't add set fmtonly off then insert is not working. I tried new line, and a semi-colon
and still it throws an error.

The stored procedure which am calling is not returning any rows as it has only insert statement. also it is on another server. thats why am using this mechanism to
do. any suggestion please

[quote]

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "
set fmtonly off;
exec Employee.[dbo].TestInsert 11,'Peter'
". 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.

check by running directly the above and see, if you are getting error. and also check executing the SP "TestInsert" in 170.30.149.34 with same parameter and see it is running without any error, there could be changes in SP to produce Incorrect syntax near
the keyword 'convert'.

If i don't add set fmtonly off then insert is not working. I tried new line, and a semi-colon and
still it throws an error.

The stored procedure which am calling is not returning any rows as it has only insert statement.
also it is on another server. thats why am using this mechanism to do. any suggestion please

[quote]

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "
set fmtonly off;
exec Employee.[dbo].TestInsert 11,'Peter'
". 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.

[\quote]

loving dotnet

Then whats the point in doing a select from it if doesnt have a resultset?

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

The operation what am performing is i need to do some operations on remote servers(db) from my production db. The operation will have select, insert,update and delete.

for example,i will be writing a stored procedures in my production db to call the stored procedure in the remote database. my remote database procedures will have select,insert,update, delete statements.

To select the data i am already done. but calling the procedure which has insert statement fails as i posted in my previous posts. i should not be using linked server for this as my client requirement.

so i chosen openrowset concept. The reason i have select statement is i need to pass the no of rows affected to the calling procedure because based on that i will be showing success/failure message on the front end(c# .net).

but by seeing these many constraints, i will propose my client to change this methodology to lined server. is it possible to block other developers to use this linked server? because not of the developers should have execute access to the linked server. is
it possbile to do? any samples please how to achieve this impersonation.