Results 1 to 3 of 3

Thread: OPENROWSET issue

  1. #1
    Join Date
    Mar 2007
    Posts
    27

    Arrow OPENROWSET issue

    Hi,
    I’ve got a proc on a server which I am trying to run from the other server.
    There are no linked servers and I want to avoid linked servers.
    I have tried to use openrowset which I have used in many other occasions.
    select * from OPENROWSET('SQLOLEDB','ServerA';'user';'password', 'exec testdb..testproc ''Summary'',''longdesc''')
    and I will get The OLE DB provider 'SQLOLEDB' indicates that the object has no columns. Error message. This is down to the openrowset checking the meta of the proc prior to running it
    so I have tried to use
    set FMTonly on
    to disable it but still it doesn’t work.
    Any idea how I can get around this problem?

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  3. #3
    Join Date
    Mar 2007
    Posts
    27
    create proc gemini_createissue_by_db
    @summary varchar(256),@longdesc varchar(7000)
    as
    declare @issueid int,@start_date varchar(25)
    select @start_date = convert(char(4),datepart(yyyy,getdate()))+'-'+convert(char(4),datepart(mm,getdate()))+'-'+convert(char(4),datepart(dd,getdate()))
    exec gemini_createissue 19,233,161,@summary,@longdesc,3,3,@start_date,null ,0,0,0,@issueid out
    select @issueid issueid
    return(0)
    'Summary','longdesc'
    go





    and sub proc


    create procedure gemini_createissue
    @projid numeric(10,0),
    @compid numeric(10,0),
    @userid numeric(10,0),
    @summary nvarchar(255),
    @longdesc ntext,
    @isstype numeric(10,0),
    @isspriority numeric(10,0),
    @startdate datetime,
    @duedate datetime,
    @assignedto numeric(10,0),
    @fixedinverid numeric(10,0),
    @isprivate bit,
    @issueid numeric(10,0) out
    as
    begin
    declare @issue_status numeric(10,0)
    declare @issue_resolution numeric(10,0)

    select @issue_status=statusid from issuestatuslut
    where seq=(select min(seq) from issuestatuslut)
    select @issue_resolution=resid from issueresolutionlut
    where seq=(select min(seq) from issueresolutionlut)

    -- Detect if issue has been assigned to and set status accordingly
    if (@assignedto > 0)
    begin
    select @issue_status=statusid from issuestatuslut where statustype=1
    end

    insert into issues (projid,compid,reportedby,summary,
    longdesc,isstype,isspriority,issstatus,issresoluti on,risklevel,startdate,duedate,assignedto,fixedinv erid,
    isprivate)
    values (@projid,@compid,@userid,@summary,
    @longdesc,@isstype,@isspriority,@issue_status,@iss ue_resolution,1,@startdate,@duedate,@assignedto,
    @fixedinverid,@isprivate)

    set @issueid = @@identity

    exec gemini_createissuehistory @issueid,@projid,N'Created',@userid

    end


    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •