accessing two databases from a single Stored Procedure

Is it possible to access another database from a single stored procedure of another database. If it is possible, please show how.

If both the DBs are on single Server and the current user have access permission on both database then you can use the following query..

Select SomeColumns From CurrentDBName..TableName
Select SomeColumns From OtherDBName..TableName

If the other database on different database Server then you have to use the Linked Server..

EXEC sp_addlinkedserver @.server = 'SERVER', @.provider = 'SQLOLEDB.1', @.srvproduct = '', @.provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

go
Exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.useself = true, @.locallogin = null, @.rmtuser = 'Userid', @.rmtpassword = 'Password'

On your SP you can use..

Select * From OpenQuery(MyRemoteServer, 'Select * From Sysobjects')

--OR

Select * From MyRemoteServer.DatabaseName.dbo.Sysobjects

|||

If the databases are on the same server just qualify the tables (in the database) with the database name

i.e.

Assume your stored procedure is in a database called MyDB. In your stored procedure, you can use the follwoing to access tables on from two different databases.

Select col1 from pubs.dbo.authors t1
Inner join adventurewords.dbo.sales t2 on t1.id = t2.id

If the database is on another server, you can create a linked server (and assuming security is OK).

Assume stored procedure is on a database called MyDB on server named Server1 and table you are trying to access is on Server2

Select * from [Server2].pubs.dbo.authors

|||

You can validate the Linked Server already present or not using..

select srvname From Master..sysservers

|||create procedure Proc_name as
begin
select * from database1..Tablename left join
select * from database2..Tablename
end