Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Posts
    229

    Unanswered: 'Subquery returned more than 1 value' in OpenQuery

    I'm getting the message

    "Subquery returned more than 1 value." etc when running a query on a
    linked server through OpenQuery. This returns around 6 values
    (some of them are fairly long varchar's).
    When running another query the same way which returns 2 values,
    it works fine.

    The problem query also works fine when run locally. It's calling a User-Defined Functions which returns a table, but the second query which works fine also does this.

    Where can this subquery be ? Inside the UDF (shouldn't matter, should it?) ?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    We would need to see the code of the query and the function really
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2002
    Posts
    229
    Here's the UDF, it concatenates all info for a certain job and returns
    it as a table record.
    As I wrote, the UDF works great when run locally.
    Note: StripJobName is a UDF that jsut removes a certain string in the beginning of job names + removes
    any leading and trailing blanks.

    The intention is to make a compare-job routine to aid in comparing the jobs of two servers.

    So, where does it make a subquery that can't be handled??


    create function OneLinePerJob (@jobname sysname)
    returns @jobinfo table(jobname sysname, jobdescr nvarchar(1024) ,jobstartstep int, stepdata varchar(4000), scheddata varchar(300), debug varchar(16) ) as
    begin

    -- Returns all job data concatenated into one table record.
    -- note: full length of Command not supported because of limitation on
    --length of returned table variable record

    declare @retstr varchar(8000)

    -- SYSJOBSTEPS data
    declare @step_name sysname
    declare @cmd1 nvarchar(1000)
    declare @cmd2 nvarchar(1000)
    declare @cmd3 nvarchar(1000)
    declare @cmd4 nvarchar(1000)
    declare @cmd5 nvarchar(1000)
    declare @cmd6 nvarchar(1400)
    declare @osa tinyint
    declare @oss int
    declare @ofa tinyint
    declare @ofs int
    declare @retryatt int
    declare @retryint int

    -- SYSJOBSCHEDULES data
    declare @schedname sysname
    declare @freqtype int
    declare @freq_interval int
    declare @freq_subday_type int
    declare @freq_subday_interval int
    declare @freq_relative_interval int
    declare @freq_recurrence_factor int
    declare @asd int
    declare @aed int
    declare @ast int
    declare @aet int

    set @retstr = ''

    -- add job info

    insert into @jobinfo (jobname,jobdescr,jobstartstep)
    select dbo.StripJobName(name), description, start_step_id
    from sysjobs
    where dbo.StripJobName(name) = @jobname

    -- add job steps

    DECLARE stepcur CURSOR FOR
    SELECT step_name, left(command,999), substring(command,1000,1000), substring(command,2000,1000),substring(command,300 0,1000),
    substring(command,4000,1000), substring(command,5000,1400),
    on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, retry_attempts, retry_interval
    FROM sysjobsteps
    WHERE sysjobsteps.job_id = (select job_id from sysjobs where dbo.StripJobName(name) = @jobname)

    OPEN stepcur

    FETCH NEXT FROM stepcur
    INTO @step_name, @cmd1, @cmd2, @cmd3, @cmd4, @cmd5, @cmd6, @osa, @oss, @ofa, @ofs, @retryatt, @retryint

    WHILE @@FETCH_STATUS = 0
    BEGIN

    set @retstr = @retstr + @step_name + @cmd1 + @cmd2 + @cmd3 + @cmd4 + @cmd5 + @cmd6 + cast(@osa as varchar) +
    cast(@oss as varchar) + cast(@ofa as varchar) + cast(@ofs as varchar) + cast(@retryatt as varchar) + cast(@retryint as varchar)

    FETCH NEXT FROM stepcur
    INTO @step_name, @cmd1, @cmd2, @cmd3, @cmd4, @cmd5, @cmd6, @osa, @oss, @ofa, @ofs, @retryatt, @retryint

    END

    CLOSE stepcur
    DEALLOCATE stepcur

    update @jobinfo set stepdata = @retstr

    -- add job schedules

    set @retstr = ''

    DECLARE schedcur CURSOR FOR
    SELECT name, freq_type, freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval,
    freq_recurrence_factor, active_start_date, active_end_date, active_start_time, active_end_time
    FROM sysjobschedules
    WHERE sysjobschedules.job_id = (select job_id from sysjobs where dbo.StripJobName(name) = @jobname)

    OPEN schedcur

    FETCH NEXT FROM schedcur
    INTO @schedname, @freqtype, @freq_interval, @freq_subday_type, @freq_subday_interval, @freq_relative_interval,
    @freq_recurrence_factor, @asd, @aed, @ast, @aet

    WHILE @@FETCH_STATUS = 0
    BEGIN

    set @retstr = @retstr + @schedname + cast(@freqtype as varchar) + cast(@freq_interval as varchar) + cast(@freq_subday_type as varchar) +
    cast(@freq_subday_interval as varchar) + cast(@freq_relative_interval as varchar) + cast(@freq_recurrence_factor as varchar) +
    cast(@asd as varchar) + cast(@aed as varchar) + cast(@ast as varchar) + cast(@aet as varchar)

    FETCH NEXT FROM schedcur
    INTO @schedname, @freqtype, @freq_interval, @freq_subday_type, @freq_subday_interval, @freq_relative_interval,
    @freq_recurrence_factor, @asd, @aed, @ast, @aet


    END

    CLOSE schedcur
    DEALLOCATE schedcur

    update @jobinfo set scheddata = @retstr

    -- data is being returned in table @jobinfo

    RETURN

    end /* function */
    Last edited by Coolberg; 11-15-06 at 04:22.

  4. #4
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    i would suggest using 'IN' instead of '=' while using a sub-query.

    you might be getting error where your sub-query returns multiple results and you are directly comparing this result using '='.

    Hope this helps.
    In GOD we believe. Everything else we Test!

  5. #5
    Join Date
    Jul 2002
    Posts
    229
    It solved the first problem. The reason why I didn't react upon the = rather than IN was that it worked locally.

    Now I'm getting:
    "String or binary data would be truncated."

    Is there a limitation on the length of the data returned through OpenQuery?

  6. #6
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    i think some varchar field has a shorther length than the length of result value in the select query.

    Just make sure your varchar columns have enough length to accomodate the largest string that might be returned by your query.

    I would suggest if you check your string variables too .

    Hope this helps.
    In GOD we believe. Everything else we Test!

  7. #7
    Join Date
    Jul 2002
    Posts
    229
    True. One or two variables were too short. However, the
    data SchedData column is still being truncated (though no error
    message this time). Anyway ,I realized I need to re-write the code for
    SQL Server 2005 since we're leaving 2000 soon.

Posting Permissions

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