.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...
View
Options
Author
Message
Fabian R Silva, -
Fabian R Silva, -
Fabian R Silva, -
posted 17 Years Ago
StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)
Group: StrataFrame Users
Posts: 153, Visits: 1.2K
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
Reply Like 0
StrataFrame Team
S
StrataFrame Team
posted 17 Years Ago
StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)
Group: StrataFrame Developers
Posts: 3K, Visits: 2.5K
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.

Reply Like 0
Fabian R Silva, -
Fabian R Silva, -
Fabian R Silva, -
posted 17 Years Ago
StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)
Group: StrataFrame Users
Posts: 153, Visits: 1.2K
thanks for the reply Smile

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
Reply Like 0
StrataFrame Team
S
StrataFrame Team
posted 17 Years Ago
StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)StrataFrame Developer (4.2K reputation)
Group: StrataFrame Developers
Posts: 3K, Visits: 2.5K
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

Reply Like 0
Fabian R Silva, -
Fabian R Silva, -
Fabian R Silva, -
posted 17 Years Ago
StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)StrataFrame User (269 reputation)
Group: StrataFrame Users
Posts: 153, Visits: 1.2K
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... Ermm




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 Smile thanks!
Reply Like 0
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...



Submit

Similar Topics

Post Quoted Reply
Reading This Topic

Powered by InstantForum 2017-1 Final © 2024
Execution: 0.000. 3 queries. Compression Enabled.

Login

Explore
Messages
Mentions
Search