Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ms sql

Posted on 2001-06-20
8
Medium Priority
?
191 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 400 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

661 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