Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

'DECLARE' Error when creating collection

Created: 10 Apr 2012 | 5 comments

Good afternoon,

We are seeing an error when testing a query in the 6.x console UI when creating a collection. The query itself runs fine inside SQL Management Studio however when we try to plug it into a collection we get an Error saying -

  • Test fails with error: Incorrect syntax near the keyword 'DECLARE'.

Does anyone know why this might be the case? The query in question actually pulls execution data for packages from the Evt_AeX_SWD_Execution table. It is meant to pull the last event for each machine that matches a specific return code. As I mentioned, it seems to return data fine in the SQL Management Studio but not in Altiris. We are running 6.x SP3 R13. Any thoughts would be much appreciated!

 

Below is the query:

-----------------------------------------------------------------------

DECLARE @PackageGuid uniqueidentifier

SET @PackageGuid = '01B54EB5-3679-4C73-9E10-E169D5A5EC59'

SELECT

swdexec.[_ResourceGuid]

/* id.[name]

,swdexec.[_eventTime] [Time of Event]

,swdexec.[ReturnCode]

,swdexec.[Status]

,swdexec.[AdvertisementName]

,swdexec.[PackageName]

,swdexec.[ExecutionNumber]

,swdexec.[Start]

,swdexec.[End]

,swdexec.[NextRun]

*/

FROM Evt_AeX_SWD_Execution AS swdexec

JOIN (

SELECT max(_id) [maxid] FROM Evt_AeX_SWD_Execution

WHERE PackageID = @PackageGuid

GROUP BY [_ResourceGuid]

) recentevents ON recentevents.[maxid] = swdexec.[_id]

AND swdexec.[PackageId] = @PackageGuid

AND swdexec.[ReturnCode] = '0'

JOIN Inv_AeX_AC_Identification AS id

ON id.[_ResourceGuid] = swdexec.[_ResourceGuid]

ORDER BY _eventTime ASC

-------------------------------------------------

Thank you!

Comments 5 CommentsJump to latest comment

KSchroeder's picture

1) Either make the @PackageGuid into a regular report parameter and reference it as '%PackageGuid%' in the query, or just hard-code it into the SQL query itself.  If you think you might eventually use something other than the Inventory Agent package (is it wrong that I recognize the package guid for the Inventory package? blush), or might want to have a drop-down with multiple packages listed, I would go with the first method.

2) You don't need the ORDER BY part; it is meaningless in a collection definition, and once you sort out the DECLARE problem, it will probably complain about the ORDER BY.

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

D-Chad's picture

K,

So below is the Query I am using for the collection. I removed the ORDERBY clause as well. I have created a Parameter for @PackageGuid but am now getting the following error.

 

Test fails with error: Conversion failed when converting from a character string to uniqueidentifier.

 
     

      The parameter is currently set as BASIC/STRING and then I've pasted in the GUID tot he value section. I am guessing that i am way off here? I don't see any paramter/value type that allows me to use a GUID. Any thoughts on what I might be missing?

      Here is the modified Collection Query:

      SELECT  
      swdexec.[_ResourceGuid] 
      /* id.[name] 
      ,swdexec.[_eventTime] [Time of Event] 
      ,swdexec.[ReturnCode] 
      ,swdexec.[Status] 
      ,swdexec.[AdvertisementName] 
      ,swdexec.[PackageName] 
      ,swdexec.[ExecutionNumber] 
      ,swdexec.[Start] 
      ,swdexec.[End] 
      ,swdexec.[NextRun] 
      */ 
      FROM Evt_AeX_SWD_Execution AS swdexec 
       JOIN ( 
        SELECT max(_id) [maxid] FROM Evt_AeX_SWD_Execution 
         WHERE PackageID =  '%PackageGuid%'
         GROUP BY [_ResourceGuid] 
       ) recentevents ON recentevents.[maxid] = swdexec.[_id] 
       AND swdexec.[PackageId] =  '%PackageGuid%'
       AND swdexec.[ReturnCode] = '0' 
       JOIN Inv_AeX_AC_Identification AS id 
        ON id.[_ResourceGuid] = swdexec.[_ResourceGuid] 
       

      D-Chad's picture

      Thanks K,

       

      Yeah I was working inside an empty Test DB and didn;t have much to work with. We will be changing this up with standard Package Guids quite often.

      I've give it a try and let you know how it goes.

      Thank you

      -D

      KSchroeder's picture

      The parameter name is just PackageGuid right, not @packageguid or % packageguid%, correct?

      Thanks,
      Kyle
      Symantec Trusted Advisor

      For Forum threads, please click "Mark as Solution" if answered.
      For all content, please give a thumbs up if you agree with or support the post.

      KSchroeder's picture

      Also...NS6 forever! :)

      Thanks,
      Kyle
      Symantec Trusted Advisor

      For Forum threads, please click "Mark as Solution" if answered.
      For all content, please give a thumbs up if you agree with or support the post.