StrataFrame Forum » .NET Forums » General .NET Discussion » .Net / SQL 2005 file/mdf join between tables on 2 databases question [offtopic?]

.Net / SQL 2005 file/mdf join between tables on 2 databases question [offtopic?]

Fabian R Silva, - — Thu, 04 Oct 2007 13:13:17 GMT

I'm using sql 2000 and I not known some stuff about sql 2005, one of that are if it´s possible using mdf/file connection strings on sql 2005 if I can open two files and make only 1 t-sql statment with a inner join.

something like
DB1.MDF (Customers)
DB2.MDF (products)

and make something like:
select * from [db1.dbo.customers] C inner join [db2.dbo.products] on...... where...

It´s a crazy question but only I want to known if it´s posible :/

thanks

RE: .Net / SQL 2005 file/mdf join between tables on 2 databases question [offtopic?]

Fabian R Silva, - — Thu, 04 Oct 2007 13:13:17 GMT

[quote][b]Ben Chase (09/18/2007)[/b][hr]Aha, are you using the SQL Anywhere (Everywhere) version? The version that uses an .mdf file directly and does not have a service that you connect to? In that case, I would think that, no you would not be able to join across multiple databases. With a full blown version of SQL Server 2005 or with SQL Server 2005 Express, you can join across multiple databases, because the service that processes the query has access to both databases, but, I don't think the versions of SQL Server that access the .mdf directly will be able to access more than one at a time.

I'm not positive on that, but I wouldn't think so... :ermm:[/quote]

sorry I lost this post :w00t:

my question is about the connection string to a mdf file on ms sql 2005 express, and as you said it appears that be possible, if I not missunderstood, when I used the connection string like "Source=.\SQLEXPRESS;AttachDbFilename=somepath\somedb.mdf;Integrated Security=True;", the sql server attach the database and can be used from another source, I will test it and post here the result :) thanks!

RE: .Net / SQL 2005 file/mdf join between tables on 2 databases question [offtopic?]

StrataFrame Team — Tue, 18 Sep 2007 15:03:53 GMT

Aha, are you using the SQL Anywhere (Everywhere) version? The version that uses an .mdf file directly and does not have a service that you connect to? In that case, I would think that, no you would not be able to join across multiple databases. With a full blown version of SQL Server 2005 or with SQL Server 2005 Express, you can join across multiple databases, because the service that processes the query has access to both databases, but, I don't think the versions of SQL Server that access the .mdf directly will be able to access more than one at a time.

I'm not positive on that, but I wouldn't think so... :ermm:

RE: .Net / SQL 2005 file/mdf join between tables on 2 databases question [offtopic?]

Fabian R Silva, - — Tue, 18 Sep 2007 11:47:07 GMT

thanks for the reply :)
and how about the mdf files? if I have 2 databases, each of those on a mdf, it´s not posible to join both of them (the connection string only can reference to 1 mdf at time?)

sorry, It´s a SQL question but I not sure where to question it :doze:

RE: .Net / SQL 2005 file/mdf join between tables on 2 databases question [offtopic?]

StrataFrame Team — Tue, 18 Sep 2007 09:09:33 GMT

Yes, you can do exactly that. Just remember to include the schema name when referencing it with full db.schema.table syntax, even when the schema is "dbo."

So, this works:

Database.dbo.TableName

but this doesn't work:

Database.TableName

As long as the currently logged in user has access to both databases, you can access both. And as far as my tests have shown, you don't have to worry about what database the connection string is using; when you use the full database scope syntax, SQL Server will find the correct table, no matter what database it's in.