none
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')

     

     

     

    Tuesday, July 31, 2007 1:40 PM

Answers

  • 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

    • Marked as answer by Kalman TothModerator Wednesday, February 01, 2012 9:15 PM
    • Edited by Ivanzinho Monday, February 13, 2012 7:39 PM I don't understand why this OPENROWSET works so poorly
    Wednesday, February 01, 2012 2:24 PM

All replies

  • 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.

    Thursday, August 30, 2007 2:48 PM
  • 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;

     

     

     

     

     

     

     

     

     

    Thursday, August 30, 2007 4:22 PM
  • Can you share what the syntax would be for the formatting and no count settings for SQLOLEDB?

    Thursday, August 30, 2007 4:44 PM
  •  

    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;

     

     

    Thursday, August 30, 2007 4:50 PM
  • 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

    Thursday, August 30, 2007 5:02 PM
  • Like to see the source code of the development..SSAS_ProductionCube;
    Thursday, August 30, 2007 5:31 PM
  • 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

    Thursday, August 30, 2007 5:55 PM
  • There is no select statement or resultset at all in the sp. Is SSAS_Core_ProductionReportWSort has any select statement or return any resultset.

     

    Thursday, August 30, 2007 6:04 PM
  • Yes, it is a very complex procedure but does return a result set.

    Thursday, August 30, 2007 6:13 PM
  • Is it any chance the query wont return any result at ant point of time.

     

    Thursday, August 30, 2007 6:25 PM
  • No.  It works fine when run outside of the openrowset.

    Thursday, August 30, 2007 7:27 PM
  • 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

    • Marked as answer by Kalman TothModerator Wednesday, February 01, 2012 9:15 PM
    • Edited by Ivanzinho Monday, February 13, 2012 7:39 PM I don't understand why this OPENROWSET works so poorly
    Wednesday, February 01, 2012 2:24 PM