May 9, 2008 at 7:11 pm
Please I need help in rewriting this query to be a single unit of sql.
SELECT sqlserver.Name0, sqlserver.FileVersion AS SQLSERVR, sqlagent90.FileVersion AS SQLAGENT90, msftesql.FileVersion AS MSFTESQL,
msdtssrvr.FileVersion AS MSDTSSRVR, sqlwriter.FileVersion AS SQLWRITER, sqllhelper.FileVersion AS SQLHELPER,
sqlbrowser.FileVersion AS SQLBROWSER, sqlreporting.FileVersion AS SQLREPORTING
FROM (SELECT v_R_System_7.Name0, v_GS_SoftwareFile_7.FileVersion
FROM v_R_System AS v_R_System_7 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_7 ON v_R_System_7.ResourceID = v_GS_SoftwareFile_7.ResourceID
WHERE (v_GS_SoftwareFile_7.FileName = 'ReportingServicesService.exe') AND
(v_GS_SoftwareFile_7.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin\'))
AS sqlreporting FULL OUTER JOIN
(SELECT v_R_System_6.Name0, v_GS_SoftwareFile_6.FileVersion
FROM v_R_System AS v_R_System_6 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_6 ON v_R_System_6.ResourceID = v_GS_SoftwareFile_6.ResourceID
WHERE (v_GS_SoftwareFile_6.FileName = 'sqlbrowser.exe') AND
(v_GS_SoftwareFile_6.FilePath = 'C:\Program Files\Microsoft SQL Server\90\Shared\')) AS sqlbrowser ON
sqlreporting.Name0 = sqlbrowser.Name0 FULL OUTER JOIN
(SELECT v_R_System_5.Name0, v_GS_SoftwareFile_5.FileVersion
FROM v_R_System AS v_R_System_5 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_5 ON v_R_System_5.ResourceID = v_GS_SoftwareFile_5.ResourceID
WHERE (v_GS_SoftwareFile_5.FileName = 'sqladhlp90.exe') AND
(v_GS_SoftwareFile_5.FilePath = 'C:\Program Files\Microsoft SQL Server\90\Shared\')) AS sqllhelper ON
sqlbrowser.Name0 = sqllhelper.Name0 FULL OUTER JOIN
(SELECT v_R_System_4.Name0, v_GS_SoftwareFile_4.FileVersion
FROM v_R_System AS v_R_System_4 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_4 ON v_R_System_4.ResourceID = v_GS_SoftwareFile_4.ResourceID
WHERE (v_GS_SoftwareFile_4.FileName = 'sqlwriter.exe') AND
(v_GS_SoftwareFile_4.FilePath = 'C:\Program Files\Microsoft SQL Server\90\Shared\')) AS sqlwriter ON
sqllhelper.Name0 = sqlwriter.Name0 FULL OUTER JOIN
(SELECT v_R_System_2.Name0, v_GS_SoftwareFile_2.FileVersion
FROM v_R_System AS v_R_System_2 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_2 ON v_R_System_2.ResourceID = v_GS_SoftwareFile_2.ResourceID
WHERE (v_GS_SoftwareFile_2.FileName = 'msftesql.exe') AND
(v_GS_SoftwareFile_2.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\')) AS msftesql FULL OUTER JOIN
(SELECT v_R_System_3.Name0, v_GS_SoftwareFile_3.FileVersion
FROM v_R_System AS v_R_System_3 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_3 ON v_R_System_3.ResourceID = v_GS_SoftwareFile_3.ResourceID
WHERE (v_GS_SoftwareFile_3.FileName = 'MsDtsSrvr.exe') AND
(v_GS_SoftwareFile_3.FilePath = 'C:\Program Files\Microsoft SQL Server\90\DTS\Binn\')) AS msdtssrvr ON
msftesql.Name0 = msdtssrvr.Name0 ON sqlwriter.Name0 = msdtssrvr.Name0 FULL OUTER JOIN
(SELECT v_R_System_1.Name0, v_GS_SoftwareFile_1.FileVersion
FROM v_R_System AS v_R_System_1 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_1 ON v_R_System_1.ResourceID = v_GS_SoftwareFile_1.ResourceID
WHERE (v_GS_SoftwareFile_1.FileName = 'sqlagent90.exe') AND
(v_GS_SoftwareFile_1.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\')) AS sqlagent90 ON
msftesql.Name0 = sqlagent90.Name0 FULL OUTER JOIN
(SELECT v_R_System.Name0, v_GS_SoftwareFile.FileVersion
FROM v_R_System LEFT OUTER JOIN
v_GS_SoftwareFile ON v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID
WHERE (v_GS_SoftwareFile.FileName = 'sqlservr.exe') AND
(v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\')) AS sqlserver ON
sqlagent90.Name0 = sqlserver.Name0
WHERE (sqlserver.Name0 IS NOT NULL)
ORDER BY sqlserver.Name0
May 9, 2008 at 7:44 pm
Don't cross post.
Answered here: http://www.sqlservercentral.com/Forums/Topic498266-338-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply