4

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.

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Narendra
  • 43
  • 1
  • 4
  • How are they SQL Server if you are saying the files reside in two seperate paths. – logixologist Jun 29 '13 at 06:12
  • Is it possible that these are Microsoft Access MDB – logixologist Jun 29 '13 at 06:15
  • 1
    If they are truly SQL Servers all you need to do is create a link between them called a linked server. This will allow you access between the two servers. – logixologist Jun 29 '13 at 06:19
  • if sql server fail with this problem then Access is my alternate solution. but i want go with sql if it will possible with lots of efforts. – Narendra Jun 29 '13 at 06:20
  • linked server is possible when second database file is in usb drive. – Narendra Jun 29 '13 at 06:22
  • @Narendra, what is the actual format of the data file. Is it a .mdb, .csv, or what is on your USB drive and what is on the other drive? – logixologist Jun 29 '13 at 06:27
  • Database file in .mdf format. And One database file in my Local Drive and second database file will be usb drive. then is it possible to use linked server. and whats about linked server if i reconnect usb drive after linked connection. if not possible then what is alternate ways. – Narendra Jun 29 '13 at 06:32
  • the .mdf file is where SQL stores the data but you can't call it directly. Do you have a running SQL server with data from these files or do you just have the files. – logixologist Jun 29 '13 at 06:42
  • I have not running sql server. its just file. file is not attached with sql server. there are only .mdf file and i give this file path in connection string. – Narendra Jun 29 '13 at 06:50

2 Answers2

7

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)

dixpac
  • 533
  • 2
  • 10
  • It turns out he only has an mdf and no SQL server. – logixologist Jun 29 '13 at 15:37
  • Oh,you need sql server to acces that mdf,or you could try to open sql mdf(with some tool) and convert hexadecimal representation to some format.But that would be hard and silly to do :) ! – dixpac Jun 30 '13 at 09:10
  • yeah from what I read, its hard to even get SQL working from just an .MDF you need a .BAK file as well. – logixologist Jun 30 '13 at 20:02
  • 1
    If you want sql files to work on sql server you need mdf file and ldf(log) file,at least.This is not only sql serever problem you can't do this on any mainstream databases ! – dixpac Jul 01 '13 at 07:23
1

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 ...
logixologist
  • 3,694
  • 4
  • 28
  • 46
  • ok i will see it. but one thing you tell me, In single sql query can we use tow different database connection ? – Narendra Jun 29 '13 at 07:09
  • To do a true JOIN on the data you can do it with one statement provided the servers were joined together. If you cant do it, one other option is 2 recordsets in VB.NET... call one from one server and call one from another. Then do the work in VB.NET. – logixologist Jun 29 '13 at 07:12
  • Hey,logixologist i referred you link. there is one connection string with overland.mdf. if there is two database just like this then can we use linked server? – Narendra Jun 29 '13 at 07:13
  • Nope! You need SQL Server to have a linked server as far as I know. – logixologist Jun 29 '13 at 07:15
  • OK logixologist. this discussion will be very useful for me. nice to talk(chat) with you. Thanks a lot. – Narendra Jun 29 '13 at 07:19
  • Thanks.. make sure you UpVote any answers you felt were useful. – logixologist Jun 29 '13 at 07:22