Error when querying linked server from 64-bit SQL2005/SQL2008 to 32-bit SQL 2000

When you got following message when accessing 32-Bit sql2000 linked server

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "". The provider supports the interface, but returns a failure code when it is used.

Try the method as mentioned in here:

1. Make sure the sql server is upgraded to sp3/sp4

2. Launch the following command from DOS:
osql -E -S \ -i \instcat.sql

Where location is the instance installation path
(Default:C:\Program Files\Microsoft SQL Server\MSSQL\Install)

...It will takes one or few minutes for the sql to finish...

3. When the command is finished, you can run the linked server query again without restarting the servers.

Excel 2010 Object Model Changes

Seems the VBA changes on Excel 2010 has been fixed.
A official comparsion to 2007/2003 could be found here


Luckly, there are no critical changes like FileFind in 2007.
Most of them are related to conditional formatings.

Excel:1-minute Tricks Series Vol.1:Format Painter Special feature

Without re-do formatting or re-entry data every time,
"Format Painter" providing a convenient ways on copying formats from one position to others.

Just like copy and paste, select the cells to copy first.
And then, click on the format painter


Notice that the cursor will become brush style


Select the target location/range, done!


I think many of you already know the above...
So what about the "special feature"?

You can also paste the format to multiple locations but without copying multiple times.

Double click the format painter when copy.
Even something has been pasted, the button will not be restored.

To stop the format painter, press "Esc" on keyboard.

This feature also applied to Excel 97 and later on version.

That's all of the first trick!

Office 2010 Beta Activation issue

When there is a problem on activiting the office beta:
"The setup controller has encountered a problem during install. Please review the log files for further information on the error."


Try the steps below:
1. Launch the setup program "setup.exe" under this location
C:\Program Files\Common Files\microsoft shared\OFFICE14\Office Setup Controller

2. Select "Enter a product key"


3. Click on continue

4. Wait the excel for re-deploy.... and click OK

5. Launch Excel again (or any Office program you like). This option is now back.


6. Wait again for the activiton. Click close when reaching this screen.


7. Restart the Excel. Finish!

Excel VBA Stub: Rearranging Sheets

Codes for re-ordering worksheet
subSplitAndHideSheets: split and hide sheets that not appeared on list: strSheetsAll

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

Sub subLaunch
    dim strSheetsAll as string
    strSheetsAll = "Sheet1;Sheet3;Sheet2"
    call subSplitAndHideSheets(strSheetsAll)
end sub


Sub subSplitAndHideSheets(wbCurr As Workbook, strSheetsAll as string)

    Dim strSheets() As String
    Dim strSheetsSD
    Dim intSheetsCounter As Integer
    Dim intSheetsCount As Integer
    Dim intCurrSheet As Integer


    If strSheetsAll = "" Then
        Exit Sub 'Exit sub if not given
    End If

    Set strSheetSD = CreateObject("Scripting.Dictionary")

    strSheets = Split(strSheetsAll, ";")
    intCurrSheet = 1
    intSheetsCount = UBound(strSheets)
    For intSheetsCounter = 0 To UBound(strSheets)
        If funcChkSheetExist(wbCurr, strSheets(intSheetsCounter)) Then
            strSheetSD.Add (UCase(strSheets(intSheetsCounter))), Nothing
            wbCurr.Sheets(strSheets(intSheetsCounter)).Move before:=Sheets(intCurrSheet)
            intCurrSheet = intCurrSheet + 1
        End If
    Next

    intSheetsCount = wbCurr.Worksheets.Count
    If intSheetsCount <= 1 Then
        Exit Sub
    End If
    For intSheetsCounter = 1 To wbCurr.Worksheets.Count
        If wbCurr.Sheets(intSheetsCounter).Visible = xlSheetVisible Then
        'set as hidden: if Sheet not exist in given string array and name is  not parameters
            If Not strSheetSD.exists(UCase(wbCurr.Sheets(intSheetsCounter).Name)) And _
                UCase(wbCurr.Sheets(intSheetsCounter).Name) <> STRSHEETNAME_PARAMETERS Then
                wbCurr.Sheets(intSheetsCounter).Visible = xlSheetHidden
            End If
        End If
    Next
End Sub

Function funcChkSheetExist(wbCurr As Workbook, strSheet As String)
On Error GoTo errHandling
    Dim tmpVal
    tmpVal = wbCurr.Sheets(strSheet).Range("A1")
    funcChkSheetExist = True
    Exit Function
errHandling:
    funcChkSheetExist = False
End Function