OpenRowset Error

Question

OpenRowset Error

boumerlin

SSCrazy

Points: 2023
More actions
July 7, 2009 at 10:18 am

#134044

I'm stumped using OpenRowset. My code looks likt this (though I have tried various iterations of it):
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server= (local);Trusted_Connection=yes; Initial Catalog=MyDB',
MyDB.dbo.MyProc 124')
I keep getting the error:
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.
Here is what is odd. I have an existing stored procedure I can call and return data from using openrowset in the same management studio window, with the same credentials. It's any new procedure I create that I get the error message. I can even re-create a functioning procedure with a new name and replicate the error.
This has led me to carefully review the permissions at the object level, but there are no differences I can find. I don't know what else to consider. Can somebody assist?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

boumerlin SSCrazy Points: 2023 More actions · Answer 1

bump.....I could really use some insight on this. I just don't have a clue why new sprocs won't run and existing sprocs will. Existing sprocs were created in SQL 2000 before a 2005 migration, but I can't believe that is tied into this.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Heh... do you expect it to guess what to do with your stored proc? You need it to be a valid query.

Also, (local) doesn't seem to work as a valid server name in most instances...

[font="Courier New"] SELECT *

FROM OPENROWSET('SQLNCLI','Server=SeverInstanceNameHere;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.sp_Who')[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

boumerlin SSCrazy Points: 2023 More actions · Answer 3

'Set FmtOnly OFF; EXEC dbo.sp_Who'

I have used various iterations of OpenRowSet, including SET FMTONLY OFF and SET NOCOUNT ON. I have used an OLE DB connection rather than the SQL Native Client, all connecting to local. I'm *fairly* certain I have the syntax under control, as I can run some sprocs with this code and not others.

What seems to be occuring is that new stored procedures are failing and existing ones are not. Thinking that perhaps it is something in my new sproc or some syntax issue with OpenRowset, I have even taken an existing "working" sproc and created it under a new name, only to have it fail when I change the sproc name in my previously working code.

This line of thinking has led me to review the object level permissions, and I could not find any difference there either.

I feel like I'm going mad with this!:hehe:

boumerlin SSCrazy Points: 2023 More actions · Answer 4

Also, (local) doesn't seem to work as a valid server name in most instances...

Bingo! You are right. I have three instances of SQL running on this server, with Dev being where I'm working and the default instance. Local is apparently going somewhere else because I haven't promoted these new sprocs yet and once I put the actual server name in, the the query ran as intended.

This is very nasty, as I have 4 server environments to contend with and do not want to have code that needs to be server specific, spread across multiple client DB's. Do you have any idea why local is not working? Does it have anything to do with multiple instances?