Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Doing a JOIN across two different data sources? 1

Status
Not open for further replies.
gbaughma

gbaughma

IS-IT--Management
Staff member
Nov 21, 2003
4,772
US
I have an ASP page that I'm writing, which I need to join two databases on.

In other words, Database 1 has a table called "UserToProjects", while Database 2 (on a different server) has the actual projects.

I need to do something like "SELECT projects.projectID, projects.UserID WHERE UserToProjects.UserID=7 INNER JOIN UserToProjects ON projects.UserID = UserToProjects.UserID"

The Join part is simple... but how do I open up two databases on a single recordset?

For example, I can do a:
SET myConnection = Server.CreateObject ("ADODB.Connection")
SET myConnection2 = Server.CreateObject("ADODB.Connection")

myConnection.Open = "DSN=UsertoProjects;"
myConnection2.Open = "DSN=projects;"

The problem comes in, of course, here....

rcdSet.Open strSQL, myConnection <---- only one connection...

Any thoughts?

Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Sort by date Sort by votes
Is either one of them a Microsoft SQL Server database?

If so, you can create a linked server in one of them and then write a query to do what you want.

If this is the case, let me know and I will gladly help you through the process.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #3
One of them is a SQL server. The other is an access database on another machine. Whatcha thinking? ;)



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
On the SQL server, set up a linked server to the Access database. It does not matter that it's on another machine, as long as SQL has permissions to the folder where the database is.

On the SQL Server, open SQL Server Management Studio. Expand "Server Objects" in the Object Explorer window. Right click "Linked Servers", click Add. Fill in the blanks, and you should be ready to go.

Then, in ASP, connect to the SQL Server and use something like this....

Code: 
SELECT projects.projectID, 
       projects.UserID 
FROM   LinkedServerName.DatabaseName.dbo.Projects As Projects
       INNER JOIN UserToProjects 
         ON projects.UserID = UserToProjects.UserID
WHERE  UserToProjects.UserID=7



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #5
Hmmm... OK, I've managed to link the MDB backend into my SQL server... and I can do a query in SQL such as "SELECT * FROM MyDatabase...Customers"

That seems to be a good start anyway... thanks for the help!

Just for anyone who's reading this thread, here's how I linked them:

SQL Server Management Studio - Expand "Server Objects"

Right-Click on Linked Server, and do "Add Linked Server"
For Access, the Provider is "Microsoft Jet ODEDB 4.0"
Product Name is "OLE DB Provider for Jet"
DataSource is the full path (Mapped or UNC) to the MDB file

Under the General tab, set the security however it needs to be to access the MDB file.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #6
Urk. Something must be wrong.

I have the Access link to SQL working fine.

If I do a query right in the SQL Server Management Studio, it works fine... for example "SELECT * FROM AccessDB...Project" works great.

HOWEVER....

If I fire that same query from my ASP Page, I get an error that the MDB is already opened exclusively by another user....

What am I doing wrong?

Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
There must be a setting somewhere. Unfortunately, I don't know where it would be, or what it would be called.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #8
It's got to be a permissions issue. Obviously, it's not locked (even though there *ARE* multiple users at any given time in the Access backend database)....

It's an issue with scripting permissions bouncing SQL queries off of SQL, and that linking to Access. It must be a permission issue... I'm googling my little heart out.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
You may be right about the permissions issue thing. From asp, what do you get when you run this query:

Code: 
Select suser_sname()

Whatever user name you get, must have appropriate priveleges to the linked server.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #10
George:

Well, I make a DSN connection to the server... it's only when I try and access the linked server that I get the error.

I added the DSN username with the "Impersonate" checkbox on the properties for the linked server, but still no joy...

Sorry to be dense, but where would I run that select query? I tried doing it against my linked server table, but I got the same error.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
Don't run it against any table.

In ASP....

Code: 
SET myConnection = Server.CreateObject ("ADODB.Connection")    
myConnection.Open = "DSN=UsertoProjects;"

rcdSet.Open "Select suser_sname() As UserName", myConnection

Response.Write rcdset("UserName")

Of course, this assumes that USerToProjects is the DSN pointing to SQL Server (and not the Access database). You may need to change this.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #12
Comes up as DOMAIN\Administrator (which is what I set it to impersonate.....)

Interesting thing:

If I log onto the SQL Server Management Studio as the SQL Username and password, I can't browse the linked server then either. Only if I'm logged on as the administrator using Windows Authentication does it work.....



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #13
ARRRRRRRRRGH! Found it.

How stupid.


If it's an UNSECURED MDB File, you have to pass a login of "Admin" with no password for it to open.

I set that in the properties for the linked server, and I was able to authenticate and expand the views. Now on to my ASP page, and see if those will query now.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #14
ARRRGH! (Part 2)

I thought that was it... but evidently ASP pages still don't have permissions to the dataset.

<Sigh> Another day wasted for me.... :(


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
try this:

In SSMS, open "Server Objects", open "Linked Servers", Right click the linked server you added,click Properties.

On the Select a page window, click "Security"

Bottom right....

"For a login not defined in the list above, connections will:"
Choose, "Be made using this security context:", and the for remote login, put in Admin.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
178
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
194
leifoet
leifoet
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
183
leifoet
leifoet
Swi
  • Locked
  • Question
JQUERY AJAX POST and how to receive data via Classic ASP
Replies
0
Views
288
Swi
Swi
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
154
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top