Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-20 : 09:40:19
|
| Paul Kettley writes "I am designing the site for multiple databases, so i can use different databases for different tasks. but I need to connect to the whole lot of them at one time. I am using a connection string at the moment, and executing it through a my_Conn.execute(adobe.connection)The problem is, i am using cookies to store certain information for the site.The site has got these databases at the moment:ACCOUNTSMAILBOXFORUMSITE_INFORMATIONLANGUAGESSTOREI have written the SQL statements to connection to tables, but I have already over 500 pages of ASP , so i dont want to alter them again to connection to different databases.I am only using a single connection string my_ConnUsed also in the Snitz Forum of SQLTeam.The site connects to login the user through ACCOUNTS, them allows the user to access the FORUM, SITE_INFORMATION, MAILBOX, STORE databases, the site it self connects to the databases to retrieve and display different information from different databases at one time.my sql statements look like thissql = "SELECT * from ACCOUNTS"SET rs = my_Conn.execute (sql)Anyone can help that would be great.Paul" |
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-03-20 : 09:54:47
|
| Err... wouldn't you be better off having multiple tables in a single database for all your requirements rather than multiple databases - sounds like a recipe for disaster.. Are these SQL or Access db's? If you want to connect to them all you'll have to have a different connection string for each database. Don't know of a way to do a single select statement over multiple databases - however, if it's possible someone here will know how! |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-20 : 10:19:42
|
| Generally speaking, SQL Server allows you to reference objects using a four-part naming convention:[server].[database].[owner].objecti've placed the first three names in brackets because they are optional. if you do not specify owner explicitly dbo is assumed.so, for example, if you had a connection to the accounts database and wanted to access, say, the "user" table in the mailbox database, you could use mailbox..user.given that you're using embedded sql it will be some effort to recode each and every select statement. you can do it, but you might consider consolidating the databases together as suggested by the first reply.setBasedIsTheTruepath<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-20 : 11:57:29
|
quote:
I am only using a single connection string my_ConnUsed also in the Snitz Forum of SQLTeam.
The Snitz forum code only uses one database. It has multiple tables, but all are kept in the same database. When you write "SELECT * FROM Accounts", you are NOT selecting from an Accounts database, but an Accounts table.If everything is in the same database on the same server, then you can definitely use a single connection to access as many tables as you need. |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-03-20 : 15:36:00
|
quote:
Generally speaking, SQL Server allows you to reference objects using a four-part naming convention:[server].[database].[owner].object
Does that mean you could, in theory, run a select on two different servers at the same time?i.e.SELECT MyTable.* FROMMyServer.MyDb.dbo.MyTableJOINYourServer.YourDb.dbo.YourTableONMyServer.MyDb.dbo.MyTable.ID = YourServer.YourDb.dbo.YourTable.ID |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-20 : 15:44:51
|
| Yes, as long as you have the remote server (YourSever) set up as a linked server on the local server (MyServer). |
|
|
|
|
|
|
|
|
Connecting to Multiple Databases