SQL join Query Between Two Different Database File

Question

I am creating a Windows application which have two SQL Server databases. One is in application/startup path and other is in different drive.

I have two SQL Server database files with different names. Both are in different location. There are same tables in both databases. I want toe create a join query between tables for different database.

So it is possible or not? If yes then how? This is my first question in stack over flow so please help me.

Answer 1

If your databases are on same sql server instances there is no need to create linked servers(because it will hurt performance),you can simply reference table with [DBName].[Schema].[TableName]. If you have same database with 2 files sql will handle that for you If you have 2 instances than you could create linked servers or handle that in applicaiont(join 2 result sets)

Answer 2

As far as I am aware you cannot directly access an MDF file using VB.NET. It needs to be a SQL Server Setup importing that MDF File first. THat is also going to be a challenging taak since you really cant just point SQL to an MDF file.

http://www.daniweb.com/software-development/vbnet/threads/115645/connecting-to-an-.mdf-database

Other people have said you can do it. I recommend getting SQL Server 2008 Express which is free. http://www.microsoft.com/en-us/download/details.aspx?id=23650

If you setup 2 servers with a linked server all you will need to do is

SELECT * FROM TableName t JOIN LinkedServerName.DatabaseName.dbo.TableName on ...