Mark Iwaszko's Technical Blog

For a couple of days, searching my newly downloaded OneNote 2007 multiple notebooks using Windows desktop search 3 didn't work for me at all. I was able to search within the page I was in but not in multiple notebooks. I tried every combination of actions to fix this, for example, uninstalling WDS3, rebooting and reinstalling as well as repairing OneNote 2007. I did the following and it fixed things for me:- Control panel Indexing options Modify Deselected all folders except my OneNote folders (I figured that I only wanted to index my OneNote folders - indexing my whole hard drive was unnecessary and too time-consuming and in any case, I use Yahoo desktop search for that) Advanced File types Scroll down to file type ".one" Select "Index Properties and File Contents" in the button selection area at the bottom Close I left indexing to run overnight. Result - full OneNote indexing now worked well and was extremely fast too! Prior to this procedure I had selected "Index Properties and File Contents" and thought that this selection applied globally to all file types. Then I realised you have to make that selection for each specific file type.

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

Reusable Functions for Reading and Writing Variables in SSIS Script Tasks

SQL BI