Solved

ms sql

Posted on 2001-06-20
8
185 Views
Last Modified: 2013-12-24
I have one MS SQL 7.0 server with two databases I have tables that need to used several in each database. Both db use the same user username and password. How can this be done in cf code and sql?
0
Comment
Question by:asipma97
8 Comments
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6210965
What do you mean when you say "I have tables that need to be used several in each database"?

Do you mean that there is data that you want to have in both databases, or that there is data in both databases that you want to use in ColdFusion?

Or do you mean something else?

Spike
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 6212286
If you mean running queries across 2 databases, SQL will not allow it.  You would have to run 2 queries.  Run the first one and then loop through that query running the 2nd query once each time through each loop.
0
 
LVL 2

Expert Comment

by:sshhz
ID: 6212922
asipma97,

You will have to create two datasource name (ODBC or OLEDB) since you have two database. Maintaining same users for both database is possible, but why you would want to separate to two database while not putting into one ? This can save up one datasource and perhaps to simplify your flow of code as well. But again, it's not wiseable to have same user for both database, it's a bit dangerous in terms of security hole.

About how to implement in your cf codes, all you have to do is to define your username, password, datasource in your Application.cfm

Tells us more on your objective of this two database with same user, it might light up someone's idea too. :)

sshhz

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 2

Expert Comment

by:hammond_david
ID: 6221744
There are certainly good reasons to have your data in more than one database - if you want different back-up schedules for different types of data, for instance.

SQL Server will allow you to reference tables not in the default database for your ODBC connection by using the following notation:

[db name]..[table name]

i.e. w/ two dots between the db name and the table name.  

I can't find documentation for this (a co-worker introduced me to this notation), but have used it many times.  I believe you can use this notation to run joins between tables in different databases, but don't think I've ever tried this and don't have access to an SQL Server at the moment to try it out.

Hope that helps!
- David
0
 
LVL 6

Accepted Solution

by:
reitzen earned 100 total points
ID: 6222331
SQL Server allows scripts to cross servers, databases, and tables.<br><br>To have one query get data from two tables in two different databases, use the following syntax:<br><br>
SELECT E.FirstName, E.LastName, J.JobTitle, J.JobDescription
FROM EmployeeTable E inner join Database2..JobDataTable J on E.EmployeeID=J.EmployeeID
<br><br>
Create a DSN for Database1 and you can change your &lt;cfquery&gt; datasource to any database on the fly.  Works in Stored Procedures too.
0
 
LVL 1

Expert Comment

by:MediaStorm
ID: 6538843
Reitzen's answer is correct.
0
 

Expert Comment

by:SpideyMod
ID: 8300655
Force Accepted

SpideyMod
Community Support Moderator @Experts Exchange
0
 

Expert Comment

by:rduval
ID: 13849861
OK but if you actually have 2 separate DATABASES, not just tables, how do you pass along the login data for the 2nd table?

Somehow Cold Fusion needs the  equivalent of the <cfquery name="common_users" datasource="2nd_datasource" username="root" password="123456"> so it can open the 2nd datasource doesn't it?

<cfquery name="common_users" datasource="1st_datasource" username="root" password="123456">
SELECT F.user_id F.user_email
FROM users F inner join 2nd_datasource U.email=F.user_email
</cfquery>

I thought that maybe you can query the first table in the first database (lets call the result "firstquery") and then use it as the second datasource (DBtype-"query" if you will) in a new query where you can specify the login info. Problem is how do you tell CF (and ultimately MSSQL) that the 2nd datasource is a query? I tried specifying "DBquery.firstquery" but MSSQL puked on that too (see code below).

<cfquery name="common_users" datasource="2nd_datasource" username="root" password="123456">
SELECT F.user_id F.user_email
FROM users F inner join dbquery.firstquery U U.email=F.user_email
</cfquery>

Rick
0

Featured Post

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question