Mark Iwaszko's Technical Blog - Monday, December 05, 2005

When you create dynamic Collections in SMS using query based rules, you have to define the query using WQL. This is fine in most circumstances, but as you have probably already found, WQL is nowhere near as rich in functions as SQL and whereas a query could be created using SQL to get the result set you want, you just can’t get the same from WQL.

Consider the scenario where you want to create a Collection that contains all workstations that have not reported Hardware Inventory for more than 14 days. To perform the query in SQL, you could use the GetDate() function to return the current date and use the DateDiff() function to compare the current date with the LastHWScan date and return all workstations where this value was greater than 14, as demonstrated below.

select sys.ItemKey, sys.DiscArchKey, sys.Name0, sys.SMS_Unique_Identifier0, sys.Resource_Domain_OR_Workgr0, sys.Client0 from System_DISC AS sys JOIN WorkstationStatus_DATA AS wks on sys.ItemKey = wks.MachineID where DATEDIFF(dd,wks.LastHWScan,GetDate()) > 30

When you create a Query Based Rule in the SMS Administrator console, SMS stores the WQL and also translates the WQL into a SQL query. The WQL and SQL are stored in the Collection_Rules_SQL table, which uses the CollectionID and query index as key. When SMS performs Collection Evaluation, it uses the SQL query in the SQL column of this table rather than the WQL. Therefore, if you create a Query Based Rule in the console, but don’t set any criteria, you can then modify the translated SQL to whatever you like, and the Collection membership will then be evaluated based on that query. As long as you don’t change the WQL by editing the Collection in the SMS Admin Console, this provides a useful workaround.

Here’s how you do it:
1. Create the Collection in the SMS Administrator Console. Create a single Query based rule, but don’t define any criteria.
2. Determine the Collection ID (you could run the WQL query ‘select CollectionID from SMS_Collection where name = ‘<collection name>’’)
3. Using SQL Query Analyser, run the following query to update the SQL query rule for the new Collection
Update Collection_Rules_SQL set SQL = ‘select SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, SMS_R_System.SMS_Unique_Identifier0, SMS_R_System.Resource_Domain_OR_Workgr0, SMS_R_System.Client0 from System_DISC AS SMS_R_System JOIN WorkstationStatus_DATA AS wks on SMS_R_System.ItemKey = wks.MachineID where DATEDIFF(dd,wks.LastHWScan,GetDate()) > 30’ where CollectionID = <CollectionID>
4. Update the Collection membership and the Collection will now contain all machines that have not reported inventory within the last 14 days. Be sure not to modify the query using the Administrator console, or the SQL will be overwritten!

SQL BI

Business Intelligence with SQL Server (by Marco Russo)

SQLServerCentral.com

Examples of SSIS Variable Usage
If you want to know how to use variables to control your package flow go here: http://www.sqlis.com/default.aspx?306
If you want to use variables in expressions to set properties of a task (termed property expressions) then there's a nice example here of setting the SQLStatementSource property of an ExecuteSQL Task: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
or here of setting various properties of the SendMail task: http://www.sqlis.com/default.aspx?59
or here of setting the ConnectionString property of a FlatFile connection manager: http://blogs.conchango.com/jamiethomson/
explains how expressions can also be used in a variable: http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx

<ViewFields>
   <FieldRef Name="ID" />
   <FieldRef Name="Title" />
</ViewFields>

and the WSDL definition for this is
 
-- <s:element minOccurs="0" maxOccurs="1" name="viewFields">
- <s:complexType mixed="true">
- <s:sequence>
  <s:any /> 
  </s:sequence>
  </s:complexType>
  </s:element>
 
 
There seems to be very few examples of people who have tried this although the following articles from rdoherty's WebLog and Klaus Salchner were helpfull.
 
Can anyone assist in suggestions as to how to overcome this ?