OpenRowSet and "No Columns" error

Question

Anyone notice an issue in SQL 2005 with openrowset?  I get the following error:

 

Cannot process the object "exec testProc". 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.

 

I have tried to use the set FMTONLY ON option but I get the same error.  I understand the openrowset is trying to get meta data, but this seems extremely limiting if you cannot use temp tables.  Any insight would be appreciated.  Thanks.

 

example code to produce the error.

 

create procedure testProc

as

select * into #tmp

from master..sp_who

go

 

select *

from OPENROWSET('SQLNCLI','Server=sql2005;Trusted_Connection=Yes;Integrated

Security=SSPI;','exec testProc')

 

 

 

Answer 1

I have exactly the same problem.  It works great with a stored proc with a simple select but a more complex one that uses temporary tables as above produces the error.

Answer 2

Here the Solution,

1.     The connection String should be any one of these(trusted or sql authentication),

a.     “Server=<server>;Trusted_Connection=YES”

b.     “Server=<server>;UID=<user>;PWD=<password>”

2.     You need to use any one of the following provider

a.     SQLNCLI

b.     SQLOLEDB

3.     The query string should have the following Environment Settings

a.     SET FMTONLY OFF;

b.     SET NOCOUNT ON;

Sample,

 

Code Snippet

Use Tempdb

Go

 

Create procedure testProc

as

            Create table  #tmp (

                        [spid] int ,

                        [ecid] int ,

                        [status] Varchar(100) ,

                        [loginame] Varchar(100) ,

                        [hostname] Varchar(100) ,

                        [blk] bit ,

                        [dbname] Varchar(100) ,

                        [cmd] Varchar(100) ,

                        [request_id] int

            );

 

            Insert Into #tmp exec master..sp_who;

 

            Select * from #tmp

 

go

 

select

            a.*

from    

            openrowset      

            (

                        'SQLNCLI',

                        'SERVER=.\mssql2005;UID=sa;PWD=sqladmin', 

                        'SET NOCOUNT ON;SET FMTONLY OFF;EXEC tempdb..testproc'

            )           as a;

 

 

 

 

 

 

 

 

 

Answer 3

Can you share what the syntax would be for the formatting and no count settings for SQLOLEDB?

Answer 4

 

The query will work for both SQLOLEDB & SQLNCLI.

 

Code Snippet

select

a.*

from

openrowset

(

'SQLOLEDB',

'SERVER=.\mssql2005;UID=sa;PWD=sqladmin',

'SET NOCOUNT ON;SET FMTONLY OFF;EXEC tempdb..testproc'

) as a;

 

 

Answer 5

Using this syntax:

select

a.*

from

openrowset('SQLOLEDB','SERVER=TERROSSQL;UID=sa;PWD=XXXX','SET NOCOUNT ON;SET FMTONLY OFF;Exec development..SSAS_ProductionCube'

) as a;

 

 

I get this error:

Error:

Cannot process the object "SET NOCOUNT ON;SET FMTONLY OFF;Exec development..SSAS_ProductionCube". 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.

Occurred on Line : 1

Answer 6

Like to see the source code of the development..SSAS_ProductionCube;

Answer 7

Here is the proc

create procedure SSAS_ProductionCube

AS

create table #iFromDate (iFromDate DateTime)

insert into #iFromDate values ('AUG 1 2007')

create table #iThruDate (iThruDate DateTime)

insert into #iThruDate values ('AUG 28 2007')

create table #iReportLayout (ID int)

insert into #iReportLayout values (3356)

create table #iProdSort1 (ID int)

insert into #iProdSort1 values (3259)

create table #iProdSort2 (ID int)

create table #iProdSort3 (ID int)

create table #iProdSort4 (ID int)

create table #iProdSort5 (ID int)

create table #Provider (ID int)

create table #Site (ID int)

create table #StaffMaster (ID int)

create table #StaffGroup (ID int)

create table #RBHA (ID int)

create table #RBHAGroup (ID int)

create table #Funds (ID int)

create table #FundGroup (ID int)

create table #Clients (ID int)

create table #Guarantor (ID int)

create table #ProcedureCode (ID int)

create table #iPrintToFile (ID int)

create table #iGraph (ID int)

Create Table #ReportCode (ReportCode varchar(20))

insert into #ReportCode values ('bee0020')

exec SSAS_Core_ProductionReportWSort

Answer 8

There is no select statement or resultset at all in the sp. Is SSAS_Core_ProductionReportWSort has any select statement or return any resultset.

 

Answer 9

Yes, it is a very complex procedure but does return a result set.

Answer 10

Is it any chance the query wont return any result at ant point of time.

 

Answer 11

No.  It works fine when run outside of the openrowset.

Answer 12

It runs for me like this:

SELECT * INTO #Temp FROM 
OPENROWSET(
'SQLNCLI', 
'Server=localhost;Trusted_Connection=yes',
'EXEC msdb.dbo.sp_help_job')

And like this:

SELECT * INTO #Temp FROM 
OPENROWSET(
'SQLNCLI', 
'Server=.;Database=OperadorMovilidad;Uid=om;PWD=om',
'EXEC msdb.dbo.sp_help_job')

And like this:

SELECT * INTO #Temp FROM 
OPENROWSET(
'SQLNCLI', 
'Server=localhost;Trusted_Connection=yes',
'SET NOCOUNT ON;SET FMTONLY OFF;EXEC MOVILIDAD_DATOS.dbo.sp_MyStoredProcedure')

I had the last query running in SQL Server 2005 SP4, but, for some reasons, I had to change the server (I mean, change the hardware and OS to Windows 2008 Server) and it works only with the first query, why this happen? It's a little annoying to change the query each time I have to change infrastructure, I only changed the OS from Windows 2003 server to 2008 Server.... SQL Engine it's exactly the same :S

---

Ivan Dario Ospina C#.NET, T-SQL Developer