By Fabian R Silva, - - 9/14/2007
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
|
By StrataFrame Team - 9/18/2007
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.
|
By Fabian R Silva, - - 9/18/2007
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 
|
By StrataFrame Team - 9/18/2007
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... 
|
By Fabian R Silva, - - 10/4/2007
Ben Chase (09/18/2007)
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...
sorry I lost this post 
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!
|
|