Results 1 to 4 of 4

Thread: Openrowset

  1. #1
    Join Date
    May 2009
    Location
    Bangalore, India
    Posts
    7

    Unanswered: Openrowset

    Hello SQL masters,

    I have a situation which i know you guys can easily resolve.

    Please check this query which is working fine. But the requirement is to make it short.
    Can anyone help me out:

    -- Server name(EG-EPLSQL-A08)

    select 'il_oistd' as Dbase_name,
    job.jobtypeid,
    job.description,
    job.jobstateid,
    job.totalitemcount,
    job.succeededitemcount,
    job.faileditemcount,
    job.startdt,
    job.lastcompletiondt,
    job.failurenote
    from OPENROWSET('SQLOLEDB','eg-eplsql-a08';'square';'square',
    [il_oistd].[dbo].[job]) as job

    UNION all

    select 'nal_cistd' as Dbase_name,
    job.jobtypeid,
    job.description,
    job.jobstateid,
    job.totalitemcount,
    job.succeededitemcount,
    job.faileditemcount,
    job.startdt,
    job.lastcompletiondt,
    job.failurenote
    from OPENROWSET('SQLOLEDB','eg-eplsql-a08';'square';'square',
    [nal_cistd].[dbo].[job]) as job

    UNION all


    --Server name(EG-EPLSQL-A40)

    select 'forecastflash' as Dbase_name,
    job.jobtypeid,
    job.description,
    job.jobstateid,
    job.totalitemcount,
    job.succeededitemcount,
    job.faileditemcount,
    job.startdt,
    job.lastcompletiondt,
    job.failurenote
    from OPENROWSET('SQLOLEDB','eg-eplsql-a40';'adread';'adread',
    [forecastflash].[dbo].[job]) as job

    UNION all

    select 'smg_wfstd' as Dbase_name,
    job.jobtypeid,
    job.description,
    job.jobstateid,
    job.totalitemcount,
    job.succeededitemcount,
    job.faileditemcount,
    job.startdt,
    job.lastcompletiondt,
    job.failurenote
    from OPENROWSET('SQLOLEDB','eg-eplsql-a40';'adread';'adread',
    [smg_wfstd].[dbo].[job]) as job

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Looks to me like that is as short as it is going to get
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmm. I'm sure I responded to this.

    The gist of my question was why do you care how many characters it uses up if it works fine?
    I would be inclined to link these servers rather than use openrowset I suppose.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2009
    Location
    Bangalore, India
    Posts
    7
    actually the servers mentioned was a sample.....there are 4 servers and almost 57 databases with the table column.

    only one of the server is linked and hence i can only do this in that server (DBA didnot give permission).

    As we use this statements in Cognos Framework Manager, isn't it a load on the server each time an Openrowset is executed?

    But thanks for you views....

Posting Permissions

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