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

Today I stumbled upon this little annoying issue.
If you are trying to run a stored procedure using OPENROWSET, for example:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','EXEC [DB_Name].dbo.SP_Name')

and you’ve received the following error:

Msg 50000, Level 16, State 1, Line 231
Cannot process the object [T-SQL statement]. The OLE DB provider "SQLNCLI10" for linked server [LinkedServerName] indicates that either the object has no columns or the current user does not have permissions on that object.

The thing is that I wasn’t able to solve it properly, just found a work-around thanks to Google:

By adding ‘SET FMTONLY OFF; SET NOCOUNT ON;‘ it will ignore the metadata as it seems, it will simply return the resultset without validating:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','SET FMTONLY OFF; SET NOCOUNT ON; EXEC [DB_Name].dbo.SP_Name')



More info about it:

The OLE DB provider “SQLNCLI10” for linked server indicates that either the object has no columns or the current user does not have permissions on that object.
When one of the columns has an invallid name this can occurs. This can be fixed by altering the sys.spt_columns_view by replacing the c.name column in quotename (c.name).

To avoid this error we need to check if our SP returns a result set that has two or more columns with the same name or does not have any column name. Next we need to set NOCOUNT ON at the beginning of the procedure (just after the begin statement). If these two things are correct then the statement will execute successfully.








Advertisements

2 thoughts on “OPENROWSET – The object has no columns or the current user does not have permissions on that object

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s