>>>>>>I have been through the files here, on the microsoft site and even bought a SQL book to get me going, but no one seems to directly address my situation:
>>>>>>On this job I have multiple servers that contain SQL databases. One project I'm working on now requires me to get a join from 2 tables from 2 different databases. One database table contains the info I need and a SS#. The other database contains the name and address info to go with the SS#. It seems that the best idea would be to keep those tables together, but that is not an option for me.
>>>>>>When I send this command:
>>>>>>SQLPREPARE(myhandle,"SELECT * from courses","TEMPCOURSES")
>>>>>>the handle puts me directly into one database, so I'm not sure how I can address a table in another database for the join.
>>>>>>I tried using the query builder wizard, remote view wizard and MS Query to see how they would handle the situation and they don't seem to go far enough to do this kind of work. The help file for MS SQL Server 6.5 intimates that it is possible, but doesn't say how to do it. I'm running this from a web page, Foxweb, VFP 5.0 and MS SQL server.
>>>>>>TIA
>>>>>
>>>>>You should provide full reference to table which is not in current database:
>>>>>Select * From Databasename.dbo.Tablename
>>>>
>>>>Thanks for the quick help. I like the idea of only being away from functionality by a period or so, but I'm still not able to run this...here's how the code stands:
>>>>

>>>>STORE SQLCONNECT('EducationSQLData','OURID','OURPASSWORD') TO gnConnHandle
>>>>=SQLPREPARE(gnConnHandleA,"SELECT * FROM studcour,Dreams.dbo.students WHERE (Dreams.dbo.students.ssn=scssn)","TEMPCOUR")
>>>>=sqlexec(gnConnHandleA)
>>>>

>>>>
>>>>Dreams=the Database Name
>>>>Students= the table in Dreams
>>>>DreamsSQLData= the Database Name Source in the ODBC driver.
>>>>EducationSQLData= the Database Name Source that contains the Studcour table
>>>>Scssn=the social security number field in Studcour
>>>>ssn= the social security number field in Students
>>>>
>>>>I tried refering to the students table by Name Source and Database Name but the return answer is that my prepare statement didn't fly...I also don't get how it could open up the DreamsSQLData source without the ID and password. Many thanks for your help so far.
>>>
>>>I guess you better call stored procedure with this SQL-statement. At least, it will be easier for you to debug. Also, you will get ability:
>>>1. Include GRANT commands to get necessary permissions to another database.
>>>2. Run this SQL-statement faster.
>>
>>Thanks for all of the help. I'm working as you suggested from the Enterprise Manager of SQL Server and trying to put this all together as stored procedures. I see what you mean about the speed improvement. I'm still stuck on the original problem though. Now I see more clearly why. The two tables I want to join are actually on 2 separate servers, each running SQL Server. The manager sees them both and I ran all of the tests suggested in the online manual to verify that I had the names and references right. Where I am now is that I cannot 'see' the table unless I position the cursor on that server. When I run the stored procedure tool, I get 'invalid object' for whatever name I try, including dbo, the Database name, the table name, ! marks, etc. I know that this will be solved by banging on it eventually, but I hope that I am clear enough for you to recognize this problem. I've been looking at permissions, but I don't see anything wrong and dbo is the owner of both tables.
>>THanks.
>
>Probably, I misunderstand you. If it's databases from different servers, then I don't know whether it's possible at all. I would imagine that you could create the same database on both servers and make backup/replication link to provide that both databases will be identical. This is feasible, but I'm not SQL Administrator and cannot advise on this issue.

Thanks, I spoke with my boss and he is rethinking the plan for our servers...at least we found this out before we headed too far down the wrong path. A duplicate table may be the ticket...BTW, I am still surprised at the speed difference of the stored procedures vs. sending SQL statements from VFP.
I really appreciate this forum.