Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Execute query for report on multiple servers

Answered Execute query for report on multiple servers

  • Wednesday, April 11, 2012 3:44 PM
     
     
    Sign In to Vote
    0

    Hi!

    Is it possible to execute query on multiple servers in SSRS?

    We can do it simply in SSMS using Central Management Servers (CMS) feature, but is it possible to do the same in SSRS?

    Thanks!



    • Edited by Skazimax Wednesday, April 11, 2012 3:45 PM
    •  
     

All Replies

  • Wednesday, April 11, 2012 3:54 PM
     
     Answered
    Sign In to Vote
    0

    Yes, it is possible. You need have the SQL Servers are linked servers.

    More detail on linking servers.

    http://msdn.microsoft.com/en-us/library/aa213778(v=sql.80).aspx

    Once you have the linked servers, you could access the database on other server by using fully qualifying name (example: SQLServerName.DatabaseName.dbo.TableName)

    Hope this helps

    Regards,
    Adhi
    My TFS Blog
    Please remember to mark as answered, if this reply helps

     
  • Thursday, April 12, 2012 8:45 AM
     
     
    Sign In to Vote
    0

    Thanks!

    One more question, i created linked server, i see it in a linked server as server\instance, but if i try to run query like this:

    select * from server\instance.master.sys.databases it didn't recognize server name, seems that '\' is a problem, how to properly point to server instance in query:

     
  • Thursday, April 12, 2012 8:59 AM
     
     Answered
    Sign In to Vote
    1

    Use [] to enclose server name and the instance

    like

    [ServerName\InstanceName].DatabaseName.dbo.TableName.

    Hope this helps..

    Vijey

    • Marked As Answer by Skazimax Thursday, April 12, 2012 9:05 AM
    •