Connection between 2 databases in local sql server

Hi,

I have two different applications based on SQL server database.

In one of the applications I need to use the information stored in the second database, only for read purpose.

As I understand I need how some to make a link between the databases in order to share tables.

Can anybody advise me how to do that, if it is possible at all?

Thanks

Just use a fully qualified name i.e. Server.Database.Owner.Table

for example: Select * from MyServer.Northwind.dbo.Customers

Regards

Paul

|||

Hi Paul,

Thanks for your replay, but maybe I'm not explained myself as well.

Because I'm accessing the database through ODBC, and when I'm using the specific DSN only the tables which are belongs to the specific database are available to me.

That’s the reason I though to make a link between the databases.

There is an option to do that?

|||

The easy way would be to create a stored procedure and have it do the work for you.

The hard way would be to establish 2 connections and query the tables seperately. If you are using .NET you could them combine them in a DataSet as two DataTables and establish a DataRelation.

Regards

Paul

|||

Hi Paul

Unfortunately I'm not using .NET, I'm working with VB6.

I'll appreciate it if you can be more specific about the stored procedure, how can I access the tables in the other database.

Thanks again

|||

Something like:

Create Proc MyProcedure
AS
select * from Db1.dbo.Table1 as tb1

inner join Db2.dbo.OtherTable as tb2
on tb1.id = tb2.id

Set the command type in an ADO Command object to a Stored Procedure and the CommnandText to MyProcedure (the stored procedure name) and call the execute method.