Lookup table in separate database

  • Thread starter Thread starter Rob Richardson
  • Start date Start date
R

Rob Richardson

Greetings!

My application uses a simple source control system implemented in SQL Server
2000. The file is contained in an image field in a table. When a user
wants to edit the file, the a new file is created on the user's hard drive
using data from the image, and a foreign key into the users table is stored
in the file's record. I am trying to build a DataGrid that will show the
name of the file and who has it checked out, if anybody. The problem is
that the file table and the user table are in different databases.

If I only show data from the file table, I would show the file name and the
user ID. I want to replace the user ID with the user's name, extracted from
the second table. How can I do that?

Thanks very much!

Rob
 
Lets say you have two databases:
FilesDb (stores files)
UsersDb (stores user information)

In FilesDb you have a table called Files with columns
FileId int,
FileName varchar,
IsCheckedOut bit
CheckOutUser int,

In UsersDb you have a tabe called Users with columns
UserId in,
UserName varchar

To get the file names and the user names who have checked out the files
this is the query you will execute (can be on FilesDb or UsersDb)

SELECT F.FileName, U.UserName FROM FilesDb.dbo.Files F JOIN
UsersDb.dbo.Users U ON F.CheckOutUserId = U.UserId
WHERE F.IsFileCheckedOut = 0

Basically your using the full [database].[owner].[object] qualifier to
tell sql server from which database in the server to get the data from.
One restriction is both database must be on the same server. If they
are on different servers you can use Linked Servers to execute
distributed queries. Do google on Linked Servers and you'll get a lot
of info on it.
 
NuTcAsE,

Thank you for your reply.

If I could assume that the databases are on the same server, then your
solution would work. Unfortunately, I can't. The database expert working
with me on this project mentioned the possibility of linked servers, but we
don't want the administrative overhead that would come along with them. I
was hoping there was a clean solution in the ADO.Net world. For now, I'm
creating a DataTable object populated with the file table, adding a column
to it for the user name, and then walking through the table filling in the
user name from the user table if the key stored in the file table is not
null. It works, but it's about as ugly as its description.

Rob
 
Rob Richardson said:
If I could assume that the databases are on the same server, then your
solution would work. Unfortunately, I can't. The database expert working
with me on this project mentioned the possibility of linked servers, but
we
don't want the administrative overhead that would come along with them.
Click to expand...

Then have him create a stored procedure on his box and grant you execute
privilege, you contruct a separate connection to his box, pass the UserID
and get the name back from his DB.

John
 
Back
Top