Solved

ms sql

Posted on 2001-06-20
8
184 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
URL redirect 4 56
exchange 2010 turning off 3des ciphers 2 191
web & database SERVERS -- PHYSICAL & VM ? 5 55
Adding multiple JVM environments to RedHat 6 7 18
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 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