MSSQL - Can't create view with data from two databases in one server

[A819A1L]

Hello 

I'm trying to create a view using data from two databases and 3 tables in total but it isn't working.  

The two databases are thardata and Gardners

The three tables are thardata.dbo.WorkingOperations, thardata.dbo.MachinesAndLabour and Gardners.dbo.CurrentIDCActivities$

The first link is WorkingOperations.OperationCode = MachineAndLabour.Code.

The second link is MachinesAndLabour.Code = CurrentIDCActivities$.Code.

First I right clicked views in the main db, selected new view and entered the following..

SELECT thardata.dbo.WorkingOperations.OperatorName
,thardata.dbo.WorkingOperations.OperationCode
,thardata.dbo.WorkingOperations.OperationName
,thardata.dbo.WorkingOperations.DateTimeStarted
,thardata.dbo.WorkingOperations.JobNo
,thardata.dbo.WorkingOperations.TerminalID
,thardata.dbo.WorkingOperations.InterruptedDateTime
,thardata.dbo.MachinesAndLabour.Resource
,Gardners.dbo.CurrentIDCActivities$.StatusType

FROM thardata.dbo.WorkingOperations
INNER JOIN thardata.dbo.MachineAndLabour ON thardata.dbo.WorkingOperations.OperationCode = thardata.dbo.MachineAndLabour.Code
JOIN Gardners.dbo.CurrentIDCActivities$ ON thardata.dbo.WorkingOperations = Gardners.dbo.CurrentIDCActivities$.Code
WHERE (thardata.dbo.WorkingOperations.Interrupted = '0')

This resulted in an error: Could not find server 'Gardners' in sys.servers.

Both databases are in this one server and I have permissions to create views and select data in both.

After doing some research here I created a view in a query...

CREATE VIEW dbo.AD_LiveData
  AS

SELECT WorkingOperations.OperatorName
,WorkingOperations.OperationCode
,WorkingOperations.OperationName
,WorkingOperations.DateTimeStarted
,WorkingOperations.JobNo
,WorkingOperations.TerminalID
,WorkingOperations.InterruptedDateTime
,MachinesAndLabour.Resource
,NULL AS StatusType
,NULL AS Code
,'thardata' AS thardata

FROM thardata.dbo.WorkingOperations
INNER JOIN thardata.MachinesAndLabour ON thardata.dbo.WorkingOperations.OperationCode
= thardata.MachinesAndLabour.Code
WHERE (thardata.dbo.WorkingOperations.Interrupted = '0')

UNION ALL

SELECT CurrentIDCActivities$.StatusType
,CurrentIDCActivities$.Code
,NULL AS OperatorName
,NULL AS OperationCode
,NULL AS OperationName
,NULL AS DateTimeStarted
,NULL AS JobNo
,NULL AS TerminalID
,NULL AS InterruptedDateTime
,NULL AS Resource
,'Gardners' AS Gardners

FROM Gardners.dbo.CurrentIDCActivities$
JOIN thardata.MachinesAndLabour ON Gardners.dbo.CurrentIDCActivities$.Code
= thardata.MachinesAndLabour.Code
WHERE Gardners.dbo.CurrentIDCActivities$.Code = thardata.MachinesAndLabour.Code

I understand that using UNION ALL requires an equal amount of objects on either side which is why I used NULL for the associated columns.

The query did work in creating the view however, the table result didn't link the data it just sandwiched two views on top of each other (see attached screenshot).

Can someone help me figure this out? My knowledge of creating views is basic.

Many thanks,

[#2 · Accepted solution]

Hello @A819A1L ,

1. Use alias for objects for better readability, that reduce also failures

2. Don't use GUI/Wizards, they are not very reliable. Always use plain T-SQL

3. Set object name with reserved words and special characters in bracket

4. In the first JOIN => ON clause is the column name "OperationCode" missing (see 1.)

Please try if this works for you:

CREATE VIEW dbo.AD_LiveData
AS
    SELECT WO.OperatorName
          ,WO.OperationCode
          ,WO.OperationName
          ,WO.DateTimeStarted
          ,WO.JobNo
          ,WO.TerminalID
          ,WO.InterruptedDateTime
          ,MAL.[Resource]
          ,CI.StatusType
    FROM thardata.dbo.WorkingOperations AS WO
         INNER JOIN 
         thardata.dbo.MachineAndLabour AS MAL
             ON WO.OperationCode = MAL.Code
         INNER JOIN 
         Gardners.dbo.[CurrentIDCActivities$] AS CI 
             ON WO.OperationCode = CI.Code
    WHERE WO.Interrupted = '0';

Olaf

[#3]

Olaf thank you! This worked perfectly.

Do you know of any really good online learning material that would help me get my head around this sort of thing?

[#4]

This looks well and it's free

https://www.w3schools.com/sql/