June 18, 2009 at 7:34 am
hi experts
i have two servers of which i need to create union query for my report when i run the query in one server with different databases it's work fine but when i add another union query which will query data from my second server i'm getting an error 'Can't find server asp2.pragmaproducts.com'... here's my code, the first two union all works fine but the last is giving me an error... please help
UNION ALL
SELECT distinct 'RFF' AS CompanyName,
(SELECT COUNT(*) FROM [OK42_PA_RFF].dbo.vw_astAssets astA WHERE astA.IsMSI = 1) as MSI,
(SELECT COUNT(DISTINCT(wrkL.StaffID)) from [OK42_PA_RFF].dbo.vw_wrkLabourUsed wrkL) AS 'Number Of Staff',
(SELECT COUNT(*) FROM [OK42_PA_RFF].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'S' and wrkW.BaseStateDescription = 'Approved') AS 'SLAOutstanding',
(SELECT COUNT(*) FROM [OK42_PA_RFF].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'W' and wrkW.BaseStateDescription = 'Approved') AS 'Ad-hocOutstanding'
from [OK42_PA_RFF].dbo.vw_astAssets
UNION ALL
SELECT distinct 'Technical Systems' AS CompanyName,
(SELECT COUNT(*) FROM [OK42_PA_TECHNICALSYSTEMS].dbo.vw_astAssets astA WHERE astA.IsMSI = 1) as MSI,
(SELECT COUNT(DISTINCT(wrkL.StaffID)) from [OK42_PA_TECHNICALSYSTEMS].dbo.vw_wrkLabourUsed wrkL) AS 'Number Of Staff',
(SELECT COUNT(*) FROM [OK42_PA_TECHNICALSYSTEMS].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'S' and wrkW.BaseStateDescription = 'Approved') AS 'SLAOutstanding',
(SELECT COUNT(*) FROM [OK42_PA_TECHNICALSYSTEMS].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'W' and wrkW.BaseStateDescription = 'Approved') AS 'Ad-hocOutstanding'
from [OK42_PA_TECHNICALSYSTEMS].dbo.vw_astAssets
UNION ALL
SELECT distinct 'Ceres Fruit Juices ' AS CompanyName,
(SELECT COUNT(*) FROM [asp2.pragmaproducts.com].[OK42_PA_CFJ].dbo.vw_astAssets astA WHERE astA.IsMSI = 1) as MSI,
(SELECT COUNT(DISTINCT(wrkL.StaffID)) from [asp2.pragmaproducts.com].[OK42_PA_CFJ].dbo.vw_wrkLabourUsed wrkL) AS 'Number Of Staff',
(SELECT COUNT(*) FROM [asp2.pragmaproducts.com].[OK42_PA_CFJ].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'S' and wrkW.BaseStateDescription = 'Approved') AS 'SLAOutstanding',
(SELECT COUNT(*) FROM [asp2.pragmaproducts.com].[OK42_PA_CFJ].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'W' and wrkW.BaseStateDescription = 'Approved') AS 'Ad-hocOutstanding'
from [asp2.pragmaproducts.com].[OK42_PA_CFJ].dbo.vw_astAssets
Servername.DatabaseName.Owner.TableName
June 18, 2009 at 7:40 am
have u created linked server for asp2.pragmaproducts.com?
June 18, 2009 at 8:10 am
hi pradeep
thanks for the quick response, no i haven't, the servers reside in our main office how do i go about creating linkservers
June 18, 2009 at 8:19 am
check out for sp_addlinkedserver in BOL
June 18, 2009 at 9:51 am
thanks again
i've decided to create two reports for each server... i've got one other problem though if you could assist,
can you look at the code below
what i'm trying to achieve here is the union queries should select records based on the first select query i.e. the companyName from last two queries should equal vw_astAssets.Description.
the trick is all three queries run from separate databases.
select distinct vw_astAssets.ID, vw_astAssets.Description, 'MSI'
(SELECT COUNT(*)
FROM vw_wrkWorkOrders A
where a.AssetID = vw_astAssets.ID
and LEFT(a.Code, 1) = 'S' AND (a.BaseStateDescription = 'Approved')) AS 'Scheduled',
(SELECT COUNT(*)
FROM vw_wrkWorkOrders A
where a.AssetID = vw_astAssets.ID
and LEFT(a.Code, 1) = 'R' AND (a.BaseStateDescription = 'Approved')) AS 'Ad-Hoc'
from vw_astAssets INNER JOIN vw_wrkWorkOrders ON
vw_astAssets.ID = vw_wrkWorkOrders.AssetID
where vw_astAssets.isActive = 1
UNION ALL
(SELECT distinct 'OK42_PA_KROMCO' AS CompanyName,
(SELECT COUNT(*) FROM [OK42_PA_KROMCO].dbo.vw_astAssets astA WHERE astA.IsMSI = 1) as MSI,
(SELECT COUNT(DISTINCT(wrkL.StaffID)) from [OK42_PA_KROMCO].dbo.vw_wrkLabourUsed wrkL) AS 'Number Of Staff',
(SELECT COUNT(*) FROM [OK42_PA_KROMCO].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'S' and wrkW.BaseStateDescription = 'Approved') AS 'SLAOutstanding',
(SELECT COUNT(*) FROM [OK42_PA_KROMCO].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'W' and wrkW.BaseStateDescription = 'Approved') AS 'Ad-hocOutstanding'
from [OK42_PA_KROMCO].dbo.vw_astAssets
UNION
SELECT distinct 'Kimberly Clark' AS CompanyName,
(SELECT COUNT(*) FROM [OK42_PA_KIMBERLYCLARK].dbo.vw_astAssets astA WHERE astA.IsMSI = 1) as MSI,
(SELECT COUNT(DISTINCT(wrkL.StaffID)) from [OK42_PA_KIMBERLYCLARK].dbo.vw_wrkLabourUsed wrkL) AS 'Number Of Staff',
(SELECT COUNT(*) FROM [OK42_PA_KIMBERLYCLARK].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'S' and wrkW.BaseStateDescription = 'Approved') AS 'SLAOutstanding',
(SELECT COUNT(*) FROM [OK42_PA_KIMBERLYCLARK].dbo.vw_wrkWorkOrders wrkW
WHERE LEFT(wrkW.Code,1) = 'W' and wrkW.BaseStateDescription = 'Approved') AS 'Ad-hocOutstanding'
from [OK42_PA_KIMBERLYCLARK].dbo.vw_astAssets)
June 18, 2009 at 12:19 pm
I've written the 3rd column as 0 (no of employees in other queries). i'm assuming that assets description is unique.
select distinct vw_astAssets.Description, 'MSI',0,
(SELECT COUNT(*)
FROM vw_wrkWorkOrders A where a.AssetID = vw_astAssets.ID and LEFT(a.Code, 1) = 'S' AND (a.BaseStateDescription = 'Approved')) AS 'Scheduled',
(SELECT COUNT(*) FROM vw_wrkWorkOrders A where a.AssetID = vw_astAssets.ID and LEFT(a.Code, 1) = 'R' AND (a.BaseStateDescription = 'Approved')) AS 'Ad-Hoc'
from vw_astAssets INNER JOIN vw_wrkWorkOrders ON
vw_astAssets.ID = vw_wrkWorkOrders.AssetID
where vw_astAssets.isActive = 1
🙁 i dont hv sql server on the laptop i'm using currently so cant test out things.. just check it out...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply