Q: view over databases

Hello,
How can I write a view that gets data from different database in the same
Database server?You can qualify object names with the database name:
CREATE VIEW dbo.MyView
AS
SELECT
t1.Col1,
t2.Col2
FROM Database1.dbo.Table1 t1
JOIN Database2.dbo.Table2 t2 ON
t1.Col3 = t2.Col4
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:92B7EB48-1A26-467F-97DB-8350669FDAF0@.microsoft.com...
> Hello,
> How can I write a view that gets data from different database in the same
> Database server?
>|||Qualify object names with the database name:
SELECT count(*)
FROM master.dbo.sysobjects as A
JOIN model.dbo.sysobjects as B
ON A.id = B.id
WHERE A.name <> B.name
Roy
On Wed, 22 Feb 2006 12:56:28 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Hello,
>How can I write a view that gets data from different database in the same
>Database server?
>|||Is this possible if the database on different server? I could not get it
working.
"Dan Guzman" wrote:
> You can qualify object names with the database name:
> CREATE VIEW dbo.MyView
> AS
> SELECT
> t1.Col1,
> t2.Col2
> FROM Database1.dbo.Table1 t1
> JOIN Database2.dbo.Table2 t2 ON
> t1.Col3 = t2.Col4
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:92B7EB48-1A26-467F-97DB-8350669FDAF0@.microsoft.com...
> > Hello,
> > How can I write a view that gets data from different database in the same
> > Database server?
> >
> >
>
>|||You can use linked serves.
Look in BOL for linked servers
Regards
Amish Shah|||As Amish mentioned, you can create linked servers and then use 4-part names
to qualify objects: on different servers:
CREATE VIEW dbo.MyView
AS
SELECT
t1.Col1,
t2.Col2
FROM Database1.dbo.Table1 t1
JOIN OtherServer.Database2.dbo.Table2 t2 ON
t1.Col3 = t2.Col4
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0D8CD62D-1258-49BE-B1C0-76F14235D931@.microsoft.com...
> Is this possible if the database on different server? I could not get it
> working.
> "Dan Guzman" wrote:
>> You can qualify object names with the database name:
>> CREATE VIEW dbo.MyView
>> AS
>> SELECT
>> t1.Col1,
>> t2.Col2
>> FROM Database1.dbo.Table1 t1
>> JOIN Database2.dbo.Table2 t2 ON
>> t1.Col3 = t2.Col4
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:92B7EB48-1A26-467F-97DB-8350669FDAF0@.microsoft.com...
>> > Hello,
>> > How can I write a view that gets data from different database in the
>> > same
>> > Database server?
>> >
>> >
>>