Forum Discussion
Solved
MSSQL - Can't create view with data from two databases in one server
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,
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 Replies
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
- 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?
