March 23, 2011 at 7:17 am
I have a MySQL database that I run queries against from MSSQL 2008. There is a canned report in our MySQL application that I can't seem to convert to MSSQL to run correctly. Here is the MySQL report:
SELECT CONCAT(S.DISPLAY_NAME, ' ', S.DISPLAY_VERSION) AS 'Software',
M.NAME as 'Computer', AH.TIME as 'Installed'
FROM ASSET_HISTORY AH, ASSET A, ASSET_TYPE AST, SOFTWARE S, MACHINE M
WHERE AST.ASSET_CATEGORY = "Computer"
and A.ID = AH.ASSET_ID
and AH.DESCRIPTION LIKE CONCAT('%%Found software item ', S.DISPLAY_NAME,' ', S.DISPLAY_VERSION, '%%')
and DATE(AH.TIME) > DATE(DATE_SUB(NOW(), INTERVAL 2 DAY))
AND M.ID = A.MAPPED_ID
AND M.OS_NAME LIKE '%SERVER%'
ORDER BY Software, Computer
Here is the MSSQL script that I have so far. I'm especially having problems with the "and AH.DESCRIPTION LIKE ('%%Found software item '+S.DISPLAY_NAME+' '+S.DISPLAY_VERSION+'%%')".
It seems as though I might not have the correct syntax. I'm really questioning the %% syntax.
SELECT (S.DISPLAY_NAME+' '+ S.DISPLAY_VERSION) AS 'Software',
M.NAME as 'Computer',
AH.TIME as 'Installed'
FROM KBOX...ASSET_HISTORY AH, KBOX...ASSET A, KBOX...ASSET_TYPE AST, KBOX...SOFTWARE S, KBOX...MACHINE M
WHERE AST.ASSET_CATEGORY = 'Computer'
and A.ID = AH.ASSET_ID
and AH.DESCRIPTION LIKE ('%%Found software item '+S.DISPLAY_NAME+' '+S.DISPLAY_VERSION+'%%')
and DATEDIFF(DD,AH.TIME,GETDATE())>2
AND M.ID = A.MAPPED_ID
AND M.OS_NAME LIKE '%SERVER%'
ORDER BY Software, Computer
Anyone have any thoughts as what I might be doing wrong or how I can improve on this?
Thanks,
Dave
March 23, 2011 at 7:29 am
you should only need One (%) in the Like not Two next to each other (%%)
March 23, 2011 at 7:36 am
Thanks for the suggestion, but I've already tried that as well. One of the problems is that the description column does not only contain 'Found Software Item', but the same field is full of other changes to the PC like 'named changed to', or 'Item removed'. Below is the information found in one field of the description column:
'Found software item Java(TM) 6 Update 24 for SATACOM 6.0.240 in inventory.
Found software item Adobe Flash Player 10.2.152.26 ActiveX for SATCOM 10.2.152.26 in inventory.
Found software item Adobe Flash Player 10.2.152.26 Plugin for SATCOM 10.2.152.26 in inventory.
Software item Adobe Flash Player 10.1.85.3 Plugin for SATCOM 10.1.85.3 removed from inventory.
Software item Adobe Flash Player 10.1.85.3 ActiveX for SATCOM 10.1.85.3 removed from inventory.
Software item Java(TM) 6 Update 22 32bit for SATCOM 6.0.220 removed from inventory.'
So I need to the report to show a row for every 'Found software item' that is listed in the description column even thought there are mulitple entries in that field.
Thanks again,
Dave
March 23, 2011 at 7:43 am
What errors are you getting? Also try moving the join criteria out of the where clause, it will make easier to work out whats going on.
March 23, 2011 at 7:50 am
If you re-write the query and move the WHERE clauses up to the JOIN statement (like they should be in SQL :-)) it might make it a lot easier to read and isolate the problem.
What exactly is the problem that you are having? I have just converted an ASA Sybase DB to SQL server and found a fair few differences on how NULLs, BINARYs, etc were handled.
March 23, 2011 at 7:54 am
and try this instead:
and AH.DESCRIPTION LIKE '%' + 'Found software item ' + S.DISPLAY_NAME + ' ' + S.DISPLAY_VERSION + '%'
March 23, 2011 at 7:57 am
SSC-Addicted - thanks for the info
Trying your suggestions now...
Dave
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply