February 13, 2009 at 1:52 pm
Hi folks,
I have a query that works fine as a SQL query but I can't use it in the report builder provided by an application (Lansweeper32). Is it possible to recreate this without using temp tables? Seems like it should be but after a couple of hours, I'm not getting closer to an answer. Or if there is a better way, I'd appreciate hearing about this as well. In this case, there will only be a few 1000 rows and the query won't be run more than a couple of times a day, so performance isn't a serious issue.
/* Shows computers that have neither CP11 or CP14 installed and need installation
Norman Heyen (c)2009 - Feb 12, 2009
*/
USE lansweeper32
GO
/* First create a table of all the machines with App1 installed */
CREATE TABLE #Installed (Computername varchar (300))
INSERT INTO #Installed (Computername)
SELECT DISTINCT Computername
FROM dbo.tblSoftware
WHERE tblSoftware.softwareName = 'App1'
/* Next create a table of all machines with updates already installed */
CREATE TABLE #Updates (Computername varchar (300))
INSERT INTO #Updates (Computername)
SELECT DISTINCT Computername
FROM dbo.tblRegistry
WHERE Regkey = 'Regkey1
OR Regkey = 'Regkey2'
/* now remove the machines with the client pack installed to get the list yet to do and display */
SELECT DISTINCT Computername
FROM
WHERE NOT EXISTS
(
SELECT DISTINCT Computername
FROM #Updates
WHERE #Updates.Computername = #Installed.Computername
)
DROP TABLE #Installed
DROP TABLE #Updates
Many thanks in advance!
Norman
February 13, 2009 at 2:03 pm
Norman,
Try this:
SELECT DISTINCT S.Computername
FROM dbo.tblSoftware S
WHERE S.softwareName = 'App1' AND NOT EXISTS
(SELECT * FROM dbo.tblRegistry R
WHERE R.Regkey IN('RegKey1', 'Regkey2') AND R.Computername = S.Computername)
Greg
February 13, 2009 at 2:06 pm
You can user CTE(http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx) instead of temp tables.
with installed as(SELECT DISTINCT Computername
FROM dbo.tblSoftware
WHERE tblSoftware.softwareName = 'App1'),
updates as(SELECT DISTINCT Computername
FROM dbo.tblRegistry
WHERE Regkey = 'Regkey1'
OR Regkey = 'Regkey2')
SELECT DISTINCT Computername
FROM installed
WHERE NOT EXISTS
(
SELECT DISTINCT Updates.Computername
FROM Updates
where Updates.Computername = Installed.Computername
)
February 13, 2009 at 2:32 pm
Or this...
SELECT DISTINCT S.Computername
FROM dbo.tblSoftware S
LEFT JOIN dbo.tblRegistry R
ON (S.Computername = R.Computername AND R.RegKey IN ('Regkey1', 'Regkey2'))
WHERE (S.SoftwareName = 'App1')
AND (R.Computername IS NULL)
February 13, 2009 at 2:56 pm
Wow, that was fast! Thanks for the code, I think I can get one of these to work with the limited interface in the report builder.
And all three seem to give me the same results as my original.
Have a great weekend!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply