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 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

Excel 2010 SSAS Cube Sets.

Following up with SSAS features in Excel 2010
Besides creating sets contain only dimension members or calculated members,
you can them both in the same set:



The magic of the sets could be respected to the new "calculatedmember" property in the connection object:

ActiveWorkbook.connections("...").OLEDBConnection.CalculatedMembers.Add Name:="[Set1]", Formula:= "{....}"

Excel:SSAS:Locale Reference 語言設定參考

SSAS/EXCEL語言設定參考孖寶 SSAS/EXCEL Translation reference

1.setting up connection
http://www.extremeexperts.com/sql/Tips/ExcelDiffLocale.aspx
2.Locale code
http://www.krafft.com/scripts/deluxe-calendar/lcid_chart.htm

中文有關設定 Chinese related settings:
繁中: 1028 (TAIWAN)
簡中: 2052 (PRC)
香港地區(3076),澳門地區(5724),最後如果沒在SSAS指定,會resolve成1028(即繁中)
詳看msdn

Where to set excel "default" locale for SSAS connection

Control panel->
regional and language option->
"standards and format" section (UPPER section):
select the region
("location" section setting is NOT related)

SSAS Translation: Enterprise vs Standard
標準版只支援維度內容.
standard version support only translation of content in dimension.

metadata translation的意思是維度名稱,cube部件的名字等等...

SSAS: SSMS Dimension properties display

Looking for the effect of dimension properties on MDX in SSMS?

It's here.

Just double clicking member in the result pane

(say "Aaron A. Allen" above NT$80,450,596.98)

Excel 2010 and SQL2008 R2 Gemini

A screenshot from SQL 2008 R2 Preview

Nice to to see Gemini in SQL08 R2.

So what is Gemini? you may refer it as an upgrade on Analysis Service.

Gemini can handle a large amount of data and relationship in a "lighting" speed.

Anyway, from the points that the add-in is provided for Excel 2010 instead of Excel 2007,

we should see a much more efficient operation with Excel 2010

Office 2007 Service Pack 2 (SP2) Excel

The SP2 has been release recently.

For excel, As mentioned in summary, including compatability with Office 2003, most of the update are focused on the graphic and charts manipulation.
Why they have not included the shapes handling in marco recording? I think there should be some issue XD.

Besides, in my opinion, there are some fixes that are critical too. Not sure why they just skip it...:
Usage:
1. "Remove Duplicates" on a range of cells misses some duplicate string values.
(Not sure they fixed it completely or not, but I had see this happened :P)
2.The order of member properties in a PivotTable's Property Fields dialog box is incorrect.
It does not follow the order set by the user.
(Not met this before, just try sp2 if you see any problem)
3.Tooltips may display the wrong information about chart values.
(Holy...)

VBA: Programmatically setting activecell.Borders.ColorIndex to xlNone fails.
(Well done...)

SSAS:
AutoComplete fails in the CUBEMEMBER function for a member name that includes quotation marks, and a user cannot access the children of that member by using AutoComplete.
(Not try this out yet.)

Let's try SP2 if you feel confused XD

Learning VBA

A few years ago, I have raised the use of VBA in the company.
Now it is the time for my colleague to teach the new comers about it.

Here is a list of stuffs that where is the start to learn and where is the level becoming master.
1. Studying Excel basic functions:
a. Formulas:
i. address related: index, indirect if, iserror, array formula
ii. calculation related: sumif, subtotal
iii. resolving address: row,col, address, indirect
iv. condition: if(and/or/not), iserror
v. array formula
take a little practice also with formula auditing
b. Features :
pivot table, consolidation, import text /web
c. Charting:
be familiar with data range/axis/legend
d. Formating:
Cell formating: Custom formating, conditional formating,
Data arrangement: filter (advanced filter)/sort page
Printing: page break view/ printing options
operation: paste special,
Input form solution: window freeze(ctrl+home), protect(Tab for navigation), validation,
Position:R1C1(R1C1 vs R1C[-1]), Naming, copy and paste with formulas reference ($A$1 vs $A1)

Actually most of the above function could be replaced by some VBA code,
However, it is recommended to employ the original Excel function as they are much efficient and "bug free"

2.VBA
a. Most importantly: Record macro and edit it.
b. Basics: variable, dynaming array, looping, sub and function (By ref/By val), global/public/private, msgbox
c. Excel Related:
i. application.workbook.worksheet.range("Range Name")
ii. selection.copy/activesheet.paste
iii. Calculation, screenupdating
iv. try to launch the code in legacy version of excel (2000/XP/2003) if needed,
some option are not be available in the those legacy version, thus causing error.
d. with other application:
Files(FileSystemObject),API,Data Access(ODBC/JET), Inter office component
e. Excel as input form: Form show/Hide, sheet controls
f. programming practice: modulisation,naming convention

If you also working on other Languages too,
you should also familar with how to connect to excel too.
E.g. using com object in pre-.net language.
and using interop in .net languages.

SSAS:EXCEL 2007: Data Filter in Pivot Table

If the filters in Excel 2007 SSAS Pivot Tables could not be found,
try installing the SP2 of the SQL05.
Yes, it's problem of SQL, not Excel.

However, the feature is solely for Excel 2007 (pivot table 12). if you save the format as <2003 (.doc format), the filter feature will be loss.

It's one of BI feature as "mentioned" technet library:
What's New in SQL Server 2005 SP2
http://technet.microsoft.com/en-us/library/bb283536(SQL.90).aspx
The 2007 version of Microsoft Office requires the installation of SQL Server 2005 Analysis Services SP2 to support all its business intelligence features

The BI features in Excel 2007 is highlighted under the Excel Team Blogs
http://blogs.msdn.com/excel/archive/2007/05/06/excel-2007-and-sql-server-analysis-services-2005-service-pack-2.aspx
following filter types are enabled in PivotTables connected to Analysis Services:

• Label Filters, including the ability to filter based on member properties
• Date Filters
• Value Filters, including enhanced Top 10 Filters, evaluated in the context of the PivotTable (ex.: Top 5 customer list per year)
• Expanding/collapsing items of attribute hierarchies placed next to each other on rows or columns

Just don't know why it's not mentioned in the specification of Excel or SQL...
I found this feature accidentally when trying the Performance Point Service(PPS). The PPS has a minimum requirement of SQL service pack 2.