Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Making a stored proc reads from two databases?

Posted on 1998-09-09
3
Medium Priority
?
271 Views
Last Modified: 2010-03-19
I have a one big database on the SQL Server, and a new small database that I've created recently. I am creating some stored procedures in the new database and in those stored proc I need to join a table from the new db with a table in the old db. I tried to include "USE OLD" with the stored proc but it didn't accept it. IS there a way to do this in SQL? or is there a way around that? any help is appreciated.
0
Comment
Question by:khal
  • 2
3 Comments
 
LVL 1

Accepted Solution

by:
bharris1 earned 120 total points
ID: 1089981
You simply need to qualify the tables with their databases in your query.

SELECT * FROM OLD..Table1 OLD, NEW..Table2 NEW WHERE OLD.Field1 = NEW.Field1


0
 

Author Comment

by:khal
ID: 1089982
It still doesn't accept that, it is saying "invalid object name OLD..Table1". I tried to use FROM OLD..table1
also I tried  FROM OLD.table1 (one dot) but both are not working. Am I doing something wrong?
0
 
LVL 1

Expert Comment

by:bharris1
ID: 1089983
Try this:

SELECT * FROM OLD.dbo.Table1 OLD, NEW.dbo.Table2 NEW WHERE OLD.Field1 = NEW.Field1

I forgot to add the owner field  [database].[owner].[table]

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question