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-01-24 : 09:26:05
|
| Gary writes "I am trying to create a stored procedure in one SQL database that pulls information from that database and a different SQL database. For example here is my sproc:CREATE PROCEDURE sp_CRMTasksViewASSELECT*FROM CRMTasksCRMTasks is in a database called Tasks. What I want to do is pull in information from a table called TMNEvents in SQL Database Events. I tried using the sproc below, but it gave me an error that I cannot use the "USE" statement in a sproc.CREATE PROCEDURE sp_CRMTasksViewAS/*select stuff from first db*/ use Tasks; select * from CRMTasks; /*select stuff from second db*/ use Events; select * from TMNEvents;ANY IDEAS???? Thanks." |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-24 : 09:35:43
|
| if you follow this naming convention it should solve your problemselect * from servername.databasename.username.object if you are in the same server and with the same user but different databaseselect * from databasename..tablename should do it.--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-24 : 13:02:07
|
The 'user' that Nazim is actually referring to is the object owner, not the user who is logged in currently. So you ought to be able to do this:CREATE PROCEDURE sp_CRMTasksViewAS/*select stuff from first db*/ select * from tasks.dbo.CRMTasks /*select stuff from second db*/ select * from Events.dbo.TMNEventsI used dbo as the table owner, because it usually is, but it doesn't have to be. Check to make sure DBO owns those tables. Also, a ; is not required, this is SQL not C++  HTH-Chad |
|
|
|
|
|
|
Querying two databases in one stored procedure