6 Replies Latest reply on Oct 13, 2009 7:55 AM by Dan Bracuk

    Relational MS SQL Databases

    TALAL MANAA Level 1

      I have multilple relational databases, our aspx developer was able to relate them using stored procedure , but since I'd like to convert the job into coldfusion I found difficulties in using the stored procedures to query all the relational databases , since coldfusion connection should be made manually to every database. Here is the scenario:

      I have 5 MS SQL databases: [img1, img2, img3, img4, and descriptionDB]

      the "descriptionDB" holds text and id information the id will be related to a hexadecimal image inside one of images databases img[i].

      How can I make a query using coldfusion to display images and their related texts !

        • 1. Re: Relational MS SQL Databases
          ilssac Level 5

          Do you want to do it at the database level or the applicaiton server level?

           

          At the application server level you could query each table seperatly and then use a query-of-query syntax to combine the different recordsets together, but I would not consider this the best choice.

           

          At the database level, if one of these databases can see the other databases you can do it in a singe query using fully qualified table names.  In MS SQL a fully qualified table name is [server].[databasename].[owner].[tablename].

           

          You can leave of the higher levels of that if they are the same for all the objects you want to join.  I.E.  If all these databases are on the same server you can leave the server name out of the name.

          • 2. Re: Relational MS SQL Databases
            GArlington

            Do you realise that you can reuse the same or create new stored procedures?

            • 3. Re: Relational MS SQL Databases
              TALAL MANAA Level 1

              As I use Dreamweaver for coldfusion development, I can see the whole stored procedures written by the previous aspx developer;  however, the stored procedure was so complex and coldfusion couldn't understand it as it has first to be connected properly to databases.

              • 4. Re: Relational MS SQL Databases
                TALAL MANAA Level 1

                thank you for your time and effort in providing helpful information, I will try your methodologies and let you know of the results.

                • 5. Re: Relational MS SQL Databases
                  TALAL MANAA Level 1

                  I would prefer to do it in the application level as it is more convenient to me; but I will take your advice and move to the database level; however, the problem is that coldfusion accept a connection only to one ms sql database, so do I have to set a DSN for each Database before I query them ?

                  • 6. Re: Relational MS SQL Databases
                    Dan Bracuk Level 5

                    You might be looking to fix something that isn't broken.  If the stored procedures worked in asp, there is no reason for them to not work in cf.  The only connection you should need is to the db with the stored procedure.