2 Replies Latest reply on Oct 10, 2016 12:02 PM by GreggSmith

    SQL server databases

    Mohammad Haddad Apprentice

      How many database i can include in one single data source for the sql ??

       

       

       

      Q : for each data source (SQL) i can connect it with one database or more than that ?

        • 1. Re: SQL server databases
          cjensen SupportEmployee

          Hi Mohammed,

           

          You can only add one database connection per data source. If you wish to report on multiple databases, you would need to add an additional data source and input the appropriate database connection details.

           

          Please let me know if you have any questions.

           

          Thanks,
          Channing

          • 2. Re: SQL server databases
            GreggSmith SupportEmployee

            While each data source is limited to connecting to one database, you CAN effectively report on data in other databases through that one database connection.

             

            This works when you have multiple SQL servers, or multiple databases on the same server.  What would happen is Xtraction would generate a query similar to:

             

            SELECT <field list>

            FROM dbo.incident

             

            to get data from the current database, or

             

            SELECT <field list>

            FROM OtherDB.dbo.incident

             

            to get data from a different database on the same server, or

             

            SELECT <field list>

            FROM OtherServer.OtherDB.dbo.incident

             

            to get data from a different database on a different server.

             

            You would accomplish this by putting "OtherDB.dbo" or "OtherServer.OtherDB.dbo" (instead of just "dbo") in the schema/owner field of the table definitions in the data model editor.

             

            In these examples, Xtraction is still only connecting directly to the main database.  It is the SQL server that is then connecting to the different database and/or different sql server to retrieve the information (it acts as a middle man).