[RESOLVED] Cross Database Queries

[FishGuy]

Hi,

SELECT Orders.OrderID, Orders.DealerID, Dealers.Dealer, Orders.OrderDate, Orders.ShipDate, Orders.Cancelled, Dealers.DealerType
FROM Dealers INNER JOIN Orders ON Dealers.ID = companysql.AcornInternational.Orders.NewDealerID
WHERE (((Orders.ShipDate)>GetDate()) AND ((Orders.Cancelled)=0) AND ((Dealers.DealerType)="Export"));

I have the following sp Select Im trying to set up. The problem is the sp will Reside on the same server (company sql) but in a different database from one of the tables required (Orders).

How do I cross Query the table Orders from AcornInternational database?

[szlamany]

Simply use fully qualified names...

Select * From [Database1].[dbo].[TableNameA] TA
   Left Join [Database2].[dbo].[TableNameX] TX on TX.KeyCol=TA.KeyVal

If you are in query analyzer you open the object browser - open the branches till you see your table names listed...

Then right-click a table and drag it into the query window - popup menu - take the SELECT option...

You will see what the fully-qualified names are for you table with that...

[FishGuy]

Hmm I don't have query analyser available as im using SQL 2005 Business Intelligence Delelopment Studio and it doesnt seem to just let me drag a table from Server Explorer into the Designer window? Can this be done?

This worked by the way thanks.

FROM Dealers INNER JOIN AcornInternational.dbo.Orders ON Dealers.ID = Orders.NewDealerID

[szlamany]

I have SQL Server Management Studio that I use with MS SQL 2005 db's...

It works differently then the old QA I'm used to.

If I right-click a table in the Object Explorer I get a menu with "Script Table As". Select that option...

Give me Create to... Drop to... Select to... Insert to... Update to... Delete to...

I can mouse-over any of those and choose:

New Query Editor Window

That shows a fully qualifed name for tables and fields...