none
How to access two different databases in one dataset ?

Answers

  • Hi,

    You can access more than one database in a single database by using a 3 part name(as long as the databases reside on a single instance of SQL Server). 

    Something like:

    SELECT * FROM

    Database1.dbo.Table1 a INNER JOIN 

    Database2.dbo.Table2 b ON a.Id = b.Id

    Hope this helps.

    • Marked as answer by guest369 Thursday, July 7, 2011 1:35 PM
    Tuesday, July 5, 2011 9:17 PM
    Moderator

All replies

  • you will need to create a database link for those two at the database level.  If this is not an option you can use subreports as a workaround (feed one dataset into a subreport that uses another dataset). 

    If you provide more info of what you are trying to do I can provide a more detailed response.

    Tuesday, July 5, 2011 9:17 PM
  • Hi,

    You can access more than one database in a single database by using a 3 part name(as long as the databases reside on a single instance of SQL Server). 

    Something like:

    SELECT * FROM

    Database1.dbo.Table1 a INNER JOIN 

    Database2.dbo.Table2 b ON a.Id = b.Id

    Hope this helps.

    • Marked as answer by guest369 Thursday, July 7, 2011 1:35 PM
    Tuesday, July 5, 2011 9:17 PM
    Moderator
  • Hi Guest39,

    Thanks for your question and other partners’ replies.

    In order to access two different databases in one dataset, you need to pay attention to below three options:

    1. If the two databases stay in the same instance, and your login id has the permission to access the two databases, please reference Shahfaisal’s statement directly in your dataset.

    2. If the two databases don’t stay in the same instance, and your login id has the permission to access the two databases, you should follow by Nehemiah’s suggestion to create a link server, then referencing Shahfaisal’s statement in your dataset.

    3. If the two databases don’t stay in the same instance, and your login id hasn’t the permission to access the two databases, thus you couldn’t access two different databases in one dataset.

    Hope it helps you, if your issue still exists, please feel free to let me know.

    Thanks,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Thursday, July 7, 2011 1:30 AM
    Moderator
  • Thank you all for your help.
    Thursday, July 7, 2011 1:35 PM
  • I posted another question in the following thread. Can you guys please help me with this as well?

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/acd20a6e-057d-4a6e-8e3b-22b65f060f60


    Thursday, July 7, 2011 1:39 PM
  • Hi Guest389,

    1,if u hv databases at different servers, first create linked server n then use 4 name part.

     

    Amit

    Thursday, July 7, 2011 8:28 PM