Copy link to clipboard
Copied
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.
How can I make a query using coldfusion to display images and their related texts !
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
thank you for your time and effort in providing helpful information, I will try your methodologies and let you know of the results.
Copy link to clipboard
Copied
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 ?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Do you realise that you can reuse the same or create new stored procedures?
Copy link to clipboard
Copied
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.