View Created in db1.table? Select out of db2.leases?

[craigfw]

Greetings,

I'm trying to write a view that resides in one db1."table" (that doesn't contain the same table as db2) and selects from db2.Lease table. Both db's are in the same SQL Server. I thought I read somewhere that a view you are executing (selecting) from an application had to have reside in the database you are selecting the data from?

Thanks, Craig

 

[SQLDenis]

not true

use pubs 
go

create view SomeView
as
select * from northwind..customers
Go

select * from pubs..SomeView

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

You can create a view that selects data from another database.

use db1
go
create view dbo.lease as
select *
from db2.dbo.lease
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[craigfw]

Thanks, Appreciate the information guys.

 

[craigfw]

Back again,

I'm trying to write this View and it continues to fail. I honestly don't know what is going wrong. As I mentioned earlier. I'm having to create the View in one db and then query the data to another db. The tables in both db's are different. I'm just trying to get to the second db to return a result.

CREATE VIEW dbo.vInstrumentBalanceDue
AS
SELECT *
FROM LRM_Instrument_Tracking.'any_table_name'
go

SELECT Balance, No.
FROM dbo.LARS-Prod$.lease
WHERE Balance <> 0

 

[mrdenny]

You need to put the owner from the remote database in the FROM command.

CREATE VIEW dbo.vInstrumentBalanceDue 
AS
    SELECT *
      FROM LRM_Instrument_Tracking.[red]dbo[/red].any_table_name
go

SELECT Balance, No.
FROM dbo.LARS-Prod$.lease
WHERE Balance <> 0

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com