Linking Tables From Different Databases Or Querying From Multiple Databases

Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.  

 

0
chrisjr2
12/10/2007 5:07:12 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
1030 Views

Similar Articles

[PageSpeed] 14
Get it on Google Play
Get it on Apple App Store

Yes, you just need to fully qualify the tables, using the database names:

select t1.field1, t2.field2 from Database1..table1 t1 inner join Database2..table2 t2 on t1.keyfield = t2.keyfield


Mark Doyle
Doyle ITS
www.doyleits.com
0
doyleits
12/10/2007 5:23:50 PM

SELECT t1.customer.* , t2.email.* 
FROM websiteDatabase.Customer t1 INNER JOIN emailDatabase.Email t2 on t1.email = t2.email

 I still I'm getting the error below. Would joining two databases change the connection string as well? Right now my connection string is

Data Source=XXX.XXX.XXX.XXX,1450\SQLEXPRESS;Initial Catalog=websiteDatabase;integrated Security=SSPI;

"

System.Data.SqlClient.SqlException: Invalid object name 'WebsiteDatabase.Customer'.

"

0
chrisjr2
12/10/2007 5:44:20 PM

The fully qualified format is DATABASE.OWNER.TABLE, so try WebsiteDatabase.dbo.Customer.

Your connection string shouldn't have to change.


Mark Doyle
Doyle ITS
www.doyleits.com
0
doyleits
12/10/2007 5:47:08 PM
Reply:

Similar Artilces:

Link FoxPro Database table to SQL Server Database table
I have LAN Server in witch Visual Foxpro Database reside.I have another remote server in witch SQL Server 2005 installed.I want to link Foxpro table to SQL Server Database Table.Is it possible? Can please someone help? Thanks MS...

LDAP queries linked to SQL database queries
Hi, I am wondering whether it is common practice to use the AD domain users information as a link table for user information in database queries. My first thought is that it would be too slow since it can potentially query the AD many times depending on the number of records returned. Any insight is appreciated, sivilianMy Weblogs...

How we run “SELECT” statement (in SQL SERVER query analyzer) for a table in different database.
Hi All, Please let me know how we can need to run “SELECT” statement (in SQL SERVER query analyzer) for a table in different database without going/loading DATABASE B (i.e. without running “USE B”) e.g. we are in a database “A” and we need to run “SELECT” command for a table in Database say “B” without going/loading (i.e. without running “USE B”) the Database “B” Thanks in Advance J----regards Bipul Kumar In the FROM clause, include the database name, something like this:SELECT whate...

How do I use a SQL query as my report datasource instead of just picking the tables in the database expert?
A colleague of mine has been creating our reports for our application and so far he's been just selecting the tables and setting up the relationships using the database expert (I think). We need to change this so that we can filter the data so we're only reporting on a single account instead of every record in the database. What do I need to change to accomplish this? Can it be done in the report or will I need to create a data source of some kind in the application and pass it into the report somehow? We're new to Crystal Reports so this may be a simple answer. Thanks! You can pa...

Move a Table from one SQL 2005 Database to Another SQL 2005 Database
I created a table in an SQL 2005 database, however, I want to move that table and the data to another SQL 2005 database (within the same server). I am new to SQL 2005 and detailed instructions would be greatly appreciated. Thanks Bill You have two options the first is clean but the second is easier.  Try the links below for details.  Hope this helps. http://msdn2.microsoft.com/en-us/library/ms174335.aspx http://msdn2.microsoft.com/en-us/library/ms188029.aspx  Kind regards,Gift Peddie Thanks, but the documentations is somewhat overwhelming and was hoping of a ba...

updating Sql database from linked Access database
I got thrown into a new project that is going to require me to update an SQL server database tables from an Access table on the backend of an Oracle database on another server. At the end of each day the Access dabase will be updated from the Oracle database. What I need to do, is when the Access database is updated I need to have the table in the SQL database automaticaly updated. When a new record is added to the Access table I need the new record added to the SQL table. When a record is deleted in the Access table I need to keep that record in the SQL table and set a field to a value (...

How to copy a database table and copy it into a different database?
Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated? start sql enterprise manager.select the research databaseright clickselect all tasks | importfollow...

Transfer tables and other contents from access database to sql database
Hi All, can anyone please help me to transfer tables and other contents from access database to sql database? I have access database file, can I also directly convert it into a sql database file? You can upsize your Access database to an SQL Server database (In Access, Tools>>Database Utilities>>Upsize Wizard.... follow the screen. Or you can use SQL Server's Import and Export wizard to import your access database to SQL Server.   You cannot convert your Access database file to SQL Server data file directly.Limno  Read this http://support.microsoft.com/kb/23...

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

Linking Multiple Tables in an SQL Query
Hello, I have a table called "ShoppingCart" which has fields for CartID, UniqueID and Quantity. At the moment I haven't specifed a primary key for this table becuase none of the fields are unique. I need to be able to link this ShoppingCart table to a table called "Size" using the UniqueID (both tables contain a field for UniqueID) in order to obtain the ProductID and CategoryID of the data item. I then need to link the ProductID and CategoryID I obtained from the first part of the SQL statement to a table called "plants" in order to get information from the following fields in the pla...

HOw to store link in a SQL database table?
hi,i want to store links to different pages in a database table(.mdf). and i want to show the data in Gridview. And when users click the links in the GridView, they should be redirected to the desired pages. how to do so??  plz help. i am using VWD 2008 , c#, .NET 3.5thanks in advance amritbera:i want to store links to different pages in a database table(.mdf). Did you try anything for this? amritbera:and i want to show the data in Gridview. You can use HyperLinkField column to show links in columns.  Thanks,MaxLet Me Google That For You!  how r u Max?? thanks for ...

Error 7405 accessing SQL Server database by database link
I'm persistently getting the above error when executing a stored procedure that uses a dabase link to another SQL Server database. I can run the procedure in MS SQL Server management studio fine. I'm setting ANSI_NULLS and ANSI_WARNINGS ON before creating the procedure. Procedure code: SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SSP_WAR_RPT_MANUAL_RECLAIM] @adt_sbi_from datetime, @adt_sbi_to datetime, @as_vendor varchar(15) output, @as_campaigns Char(1) AS BEGIN SET ANSI_NULLS O...

Query between 2 tables in different databases
Hi all.  I am an Access user (pretty good) and am trying to learn Visual Studios since we went to SQL Server.  I know how to connect Visual Studios to several databases but how do I write a query to join tables from different databases?  Also, is there a way to import tables?  Finally, any suggestions on good references for me to read? Thanks!  Use this  USE Northwind GOSELECT c.*, o.*FROM Northwind.dbo.Customers AS c INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb&#...

Multiple databases or multiple tables?
I'm working on a website that allows users to create their own storefront, and i'm wondering how I should be storing data in the database (MySql) Should I use multiple databases? (A new MySql database for each storefront) or.. should I use multiple tables? (A new table for each storefront, ex: store_StoreName_Products) it's a relatively small project, which I don't anticipate more than 10,000 storefronts... It is a bit difficult to answer without knowing the details in your case. I would tend to think that you should avoid both the ideas. If you are anticipating 100...

Web resources about - Linking Tables From Different Databases Or Querying From Multiple Databases - asp.net.sql-datasource

Uh oh. Bernie Sanders thinks cops should handle campus rape claims
Oh you got trouble. Right here in Bernie City. With a capital ‘B’ and that rhymes with ‘C’ and that stands for ‘Cops’ Just when Bernie Sanders ...

Amazon Prime Now Includes 20% Discount On Video Game Pre-Orders & New Releases
For years, Amazon was able to win over some video game fans by guaranteeing release-day delivery of new titles. But now that gamers can pre-order ...

Skype bringing free group video calling to iPhone and iPad
Skype today announced that it’s bringing free group video calling— a feature it’s long had available on the desktop—to mobile devices including ...

Explosion in Istanbul: What You Need to Know About the ISIS Attack in Turkey
An explosion in Istanbul on Tuesday which killed at least 10 people and injured at least 15 was the result of a s uicide bombing orchestrated ...

Jeff Long Stepping Down as Chairman of College Football Playoff Selection Committee
Arkansas athletic director Jeff Long is stepping down from his role as chairman of the College Football Playoff selection committee next year, ...

Driving Around in a Star Wars Spoiler
No that kind of spoiler, yet the image is strangely appropriate for this post. We mentioned earlier that there should be some sort of statute ...

Lady Gaga Leaves WME for CAA
As her acting career takes off following her Golden Globes win on Sunday, Lady Gaga is parting ways with talent agency WME. The “American Horror ...

Bandidos Biker Gang President Freed in Texas on $250K Bond
Bandidos biker gang president charged with drug trafficking, racketeering, freed on $250K bond

Murdoch, Hall engagement sparks mockery on social media
The news of Rupert Murdoch's engagement to Jerry Hall sent social media into a sometimes snarky whirl on Tuesday, as Twitter users mocked the ...

Twitter adds live Periscope broadcasts to timelines
Twitter on Tuesday began weaving live Periscope video broadcasts into timelines streamed to its application tailored for Apple mobile devices. ...

Resources last updated: 1/12/2016 8:47:43 PM