Solved

ms sql

Posted on 2001-06-20
8
186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

710 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