2006/11/30

INNER JOIN with a comma separated values CSV field

Scenario: Sometimes we use varchar fields to store little pieces of data that, extriclty talking, should be normalized and stored on another table that references the former main table. Think for instance in a list of items referenced by a bigger entity, using a 1..n relationship. In certain particular scenarios that list might be as short as 4 or 5 items, and sure less than 10. In that case, and if you plan that you will never need to do a join with that data and another table you are tempted to store de data as a list of items (varchar), and simplify things not adding another table to your already huge schema, despite the normalization rules. And time goes by and needs change and then you need to do that JOIN that you planned you will never need to do, and you have just a list of integers such:

K1  List
-----------------
1  '1, 5, 22, 31'
2  '4, 9, 48'
3  '5, 13, 22'

And you need to generate something like:

K1  Item
-----------------
1  1
1  5
1  22
1  31
2  4
2  9
2  48
3  5
3  13
3  22

Solution: I must confess that I was not able to find a good solution by myself and asked for help on microsoft.public.es.sqlserver. Miguel Egea showed me a solution based on Itzik Ben Gan's work that was worth making this blog entry. Pure Transact-SQL, and efficient:

IF NOT OBJECT_ID('tempdb..#t') IS NULL
DROP TABLE #t
GO

SELECT 1 K1, '1, 5, 22, 31' List INTO #t
UNION ALL
SELECT 2, '4, 9, 48'
UNION ALL
SELECT 3, '5, 13, 22'
GO

/* CommonTableExpression to retrieve the first 1000 numbers */
WITH cte AS
( SELECT 1 id
  UNION ALL
  SELECT id+1 from cte WHERE id<1000
)
  --SELECT *,
  --  SUBSTRING(List, id+1, PATINDEX('%,%', SUBSTRING(List, id+1, LEN(List)))-1)
  SELECT K1,
    CONVERT(int, SUBSTRING(List, id+1, PATINDEX('%,%', SUBSTRING(List, id+1, LEN(List)))-1)) Item
  FROM cte INNER JOIN (SELECT K1, ',' + List + ',' List FROM #t) t
    ON SUBSTRING(t.List, id, 1) = ',' AND LEN(List)>id
  ORDER BY K1, Item -- OPTIONAL
  OPTION(MAXRECURSION 0)

2006/11/27

MS Word tries to connect to server hosting templates

We had set our computers running MS Office 2003 to use group templates from a corporate server so that every one uses the same templates with our company logo, font styles, etc (Tools -> Options -> File locations -> Group templates). Some years ago the templates were located on a shared resource (everyone readonly access) in one of our servers (let's call it \\OLDSERVER\OfficeTemplates$\Word). Years have passed by and with Windows 2003 R2's new features, we have moved on to use replication, DFS and domain-based namespaces to unify all the shares into a single entry point for everyone. The new group templates location was set to H:\Common files\Office templates\Word. Everything seemed to be perfectly set up and working. Eventually, the server that used to host the templates was removed and the problem showed up: Every time you try to open a .doc file that was created using the original path template (based on a shared resource) MS Word took from 2 to 3 minutes to show up the file. It finally opens, but most users think there is a problem with the servers, the file, MS Word or their computers and rush to call IT support. We started to investigate the issue and found that Adam Leinss was experiencing the same problem. From Adam Leinss’ Tech Tips: Remove Novell and Microsoft Word Goes Ape:

Our company is moving from Novell to Microsoft for our file and directory services. We removed the Novell client from everyone’s workstation and that seemed to work just fine. Then we removed everyone’s rights to said Novell server and everything is still fine. Then shut down said Novell server and bam: opening some Word documents takes 2 to 3 minutes!
We tried to use the macro/script he suggests. You need to download dsofile.dll - Microsoft Developer Support OLE File Property Reader 2.0 Sample (KB 224351) in order to make it run and still make some modifications to the code since there seem to be two different dsofile.dll versions and minor changes are needed in order to run it using the new dsofile.dll version. Extracted from March 2005 Tales from the Script (Historias de Secuencias de Comandos):
Old dsofile.dll version:
Set objPropertyReader = CreateObject("DSOleFile.PropertyReader")
Set objDocument = objPropertyReader.GetDocumentProperties("C:\Scripts\New_users.xls")
Wscript.Echo "Author: " & objDocument.Author

Code modified to make it run with the new version:

Set objFile = CreateObject("DSOFile.OleDocumentProperties")
objFile.Open("C:\Scripts\New_users.xls")
Wscript.Echo "Author: " & objFile.SummaryProperties.Author

Summing up, here is the modified version of the script that we used:

Sub TemplateBatchChangeModified()
    Dim objPropertyReader
    Dim strFolder As String
    Dim strFileName As String
    Dim objThisDoc As Word.Document
    Dim strFindTemplate As String
    Dim strReplaceTemplate As String
    Dim strAffectedDocs As String

    On Error Resume Next

    'Create the PropertyReader object

    Set objPropertyReader = CreateObject("DSOFile.OleDocumentProperties")
    If Err.Number <> 0 Then
        MsgBox "You must install the DSOleFile component. See " & _
            "http://support.microsoft.com/support/kb/articles/Q224/3/51.ASP"
        GoTo FinishUp
    End If

    'Get the template names
    strFindTemplate = UCase(InputBox("Name of template to find (exclude the .dot)") & ".dot")

    strReplaceTemplate = InputBox("Name of replacement template (exclude the .dot)") & ".dot"

    'Make sure it's a valid template. Try to create a new document based on it.
    Set objThisDoc = Word.Documents.Add(strReplaceTemplate, Visible:=False)
    If Err.Number <> 0 Then
        'No such template
        MsgBox "There is no accessible template named " & strReplaceTemplate
        GoTo FinishUp
    End If
    'Close the test document
    objThisDoc.Close wdDoNotSaveChanges

    On Error GoTo ErrorHandler
    'Get the current documents path
    strFolder = "drive:\directory from with you want to replace templates\"

    'Examine all Word documents in the directory

    'Get the first document name
    strFileName = Dir(strFolder & "*.doc")

    While strFileName <> ""
        'Look at the template name
        objPropertyReader.Open (strFolder & strFileName)
        If UCase(objPropertyReader.SummaryProperties.Template) = strFindTemplate Then
            objPropertyReader.Close

            'It matches. Open the document
            Set objThisDoc = Word.Documents.Open(FileName:=strFolder & strFileName, _
                                                 Visible:=False)

            'Change the attached template
            objThisDoc.AttachedTemplate = strReplaceTemplate

            'Save the change
            objThisDoc.Close wdSaveChanges

            'Note the document
            strAffectedDocs = strAffectedDocs & strFileName & ", "
        End If
        'Get the next document
        strFileName = Dir
    Wend

    'Report the results
    If strAffectedDocs = "" Then
        MsgBox "No documents were changed.", , "Template Batch Change"
    Else
        'Remove the trailing comma and space
        strAffectedDocs = Left(strAffectedDocs, Len(strAffectedDocs) - 2)

        MsgBox "These documents were changed: " & strAffectedDocs, , "Template Batch Change"
    End If
    GoTo FinishUp

ErrorHandler:
    Set objThisDoc = Nothing
    Set objPropertyReader = Nothing
    Err.Raise vbError + 1001, "TemplateBatchChange", "TemplateBatchChange encountered an error: " & Err.Description

FinishUp:
    'Release object references
    Set objThisDoc = Nothing
    Set objPropertyReader = Nothing
End Sub

There is however a problem with this script, or with MS Word API to be more precise: In our case the old and the new templates are called the same. The only change is WHERE do they are located (the unexisting shared resource and the new location). Having that in mind, if you run: /

objThisDoc.AttachedTemplate = strReplaceTemplate

no change is actually done, and the file is not modified at all (despite that the scripts tells you that it has been changed). The only way to effectively change the files showing this delay when being opened is to change the name of the template. For instance we had a template called Corporate+TÜV Envelope English.dot, we would need to create a copy of it with the name Corporate+TÜV Envelope EN.dot for instance and then run the script telling to change Corporate+TÜV Envelope English.dot into Corporate+TÜV Envelope EN.dot FOR EVERY .DOC FILE created during the last 3 years that resides on the server, and this script repeated FOR EVERY template we use. If you have set your group templates on a shared resource I advise you not doing it any more. You will run into problems whenever you need to replace your server. You can check how the full path to the template is saved with your documents saving them as a .xml file and the opening it with notepad. You will find the following key:

<w:attachedTemplate w:val="\\OLDSERVER\OfficeTemplates$\Word\Corporate+TÜV Envelope English.dot"/>

We have finally decided not to change any single file of our servers. Instead of doing such a lot of changes, we just have created an A record in our DNS Servers to point OLDSERVER to the IP address of any of the live servers. Having done that MS Words 2003 opens the files that poing to \\OLDSERVER\OfficeTemplates$\Word without the delay of 3 minutes. There is no need to create the share on the server pointed by OLDSERVER, it only needs to be there, alive and 'pingable'. It is not a solution, just a workaround. The real solution is MS Word being updated (maybe in the next SP or in Office 2007) so that it does not look for the path of the original template anymore.

2006/11/06

Rename files to match (or include) their date/time stamp

This article is quite old but I find it really useful. I'm sure you have had such a need sometime and either you managed without it, circumventing the problem or solving it yourself (doing a little in-home application). The article is related to digital cameras and renaming the files in a more convenient way instead of the useless schemas that they use. However this can be applied to rename a backup file just after it has finished, using the date of the file so so that the next backup does not overwrites the old one or any other scenario that you can think about. Rename Files to Match Date/Time Stamp
My digital camera generates filenames like IMG001.jpg, IMG002.jpg, and so on. This isn't useful, because the filenames don't describe anything, and I often run into problems with duplicate filenames when I transfer images to my computer. Is there a simple way in Windows XP Professional to rename a group of files, with each filename made up of the date and time of file creation? For example, I'd like to see filenames such as 20030730_143222.
A sample that just echoes the names of the files in the current directory including the date/time just before the file's extension.
@ECHO OFF
FOR %%V IN (%1) DO ECHO %%V [%%~tV] %%~xV
And here the final command. In this case the original file name is completely discarded.
FOR %%V IN (%1) DO FOR /F "tokens=1-6 delims=/: " %%J IN ("%%~tV") DO IF EXIST %%L%%J%%K_%%M%%N%%O%%~xV (ECHO Cannot rename %%V) ELSE (RENAME "%%V" %%L%%J%%K_%%M%%N%%O%%~xV)
If you want to add date/time information before the extension and keep the oringinal name in place, you can use:
RENAME "%%V" "%%V-%%L%%J%%K_%%M%%N%%O%%~xV"
Instead the former rename.

2006/11/03

Error 0x80070002 applying updates

We are permanently receiving the following error message in System EventLog of a particular server:
Event Type: Error
Event Source: Windows Update Agent
Event Category: Instalación
Event ID: 20
Date:  03/11/2006
Hour:  12:36:11
User:  Not available
Server: HERCULES
Description:
Error de instalación: Windows no puede instalar la siguiente instalación con error 0x80070002: MSXML 6.0 RTM Security Update (925673).

Para obtener más información, vea el Centro de ayuda y soporte técnico en http://go.microsoft.com/fwlink/events.asp.
...

The rest of servers had this update (MSXML 6.0 RTM Security Update 925673)applied without any problem. The problem persists through restarts and the update is permanently shown in system tray bar as pending. We deliver updates using Windows Server Update Services (WSUS) 2.0.

After some research we found KB922582 - Error message when you try to update a Microsoft Windows-based computer: "0x80070002" (last reviewed September 19, 2006).

SYMPTOMS When you try to update a Microsoft Windows-based computer by using any of the following Microsoft tools, you may receive error code 0x80070002:
  • The Windows Update Web site
  • The Microsoft Update Web site
  • The Inventory Tool for Microsoft Updates (ITMU) for Microsoft Systems Management Server (SMS) 2003
  • Software Update Services (SUS) 1.0
  • Windows Server Update Services (WSUS) 2.0

Nov, 7, 2006 Update: Even though the server experiencing this problem was patched with the former KB922582 and rebooted several times and even re-patched and rebooted once again, the pending MSXML 6.0 RTM Security Update was still there and the error appeared once and again. At the end we decided to manually download the pending update and install it interactively instead of allowing it to be applied through WSUS2.0: Vulnerabilities in Microsoft XML Core Services 6.0 RTM Could Allow Remote Code Execution (925673) The server experiencing this behaviour was a Dell PowerEdge 2950 (Dual Xeon with 4Gb RAM) running Server 2003 R2 x64 Standard and serving SQL Server 2005 Standard as domain controller.

2007-01-12 Update: Too late for me, but if you run into this problem, please see KB910336: You receive a "0x80070002" or "0x80070003" error code after you download an update from Windows Update, from Microsoft Update, or from Windows Server Update Services for more information and resolution about this issue. The article is last reviewed on June 30, 2006 but I couldn't find it when I needed. Maybe you are luckier if you reach this point...