Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2001
    Posts
    36

    Unanswered: Servername as Variable ???

    I have a query that should run against various linked server on my machine.
    If I use it in this way it does not work. Nor does it work with OpenRowSet.

    SELECT a.* FROM OPENquery(@servername ,'SELECT name FROM sysobjects ')

    Please suggest any other way to execute a query against multiple servers in T-SQL.

    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Not sure if this will work for you but here are two examples...

    Code:
    ---------------------------------------------------------------------------------------------------------------------------------------------------
    declare @servername varchar(25)
    set @servername = 'XXXXXXXX'
    exec('select * from openquery(' + @servername + ',''select * From master.dbo.sysobjects'')')
    exec('select * from ' + @servername + '.master.dbo.sysobjects')

    ---------------------------------------------------------------------------------------------------------------------------------------------------
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Nov 2001
    Posts
    36
    Thanks for your reply Paul.

    But I have to declare a cursor based on that SQL statement.
    Just executing the SQL stmt works fr me, but how to create after that based on the queries that you have given me ?

    Thanks

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Could you insert the results of the query into a temp table and cursor over the temp table? I have also seen a cursor declaired buy issuing an EXECUTE() statment but to be honest I haven't done that before. I try to avoid cursors if possable.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I just found the code I was looking for...


    Code:
    ------------------------------------------------------------------------------------------------------------------------------------------
    declare @servername varchar(25), @TSQL varchar(2000)
    set @servername = 'HGW2DB18'
    set @TSQL = 'declare tmp_cursor cursor for select * from openquery(' + @servername + ',''select * From master.dbo.sysobjects'')'
    exec(@TSQL)
    open tmp_cursor
    fetch next from tmp_cursor
    while (@@FETCH_STATUS = 0) begin
    fetch next from tmp_cursor
    end
    deallocate tmp_cursor

    ------------------------------------------------------------------------------------------------------------------------------------------
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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