Solved

ms sql

Posted on 2001-06-20
8
187 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
Supports up to 4K resolution!

The VS192 2-Port 4K DisplayPort Splitter is perfect for anyone who needs to send one source of DisplayPort high definition video to two or four DisplayPort displays. The VS192 can split and also expand DisplayPort audio/video signal on two or four DisplayPort monitors.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

636 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