Dear All,

I've experiencing problem cannot update sourcetable
property on partition using DSO Programming/script,
similar sympthom with
http://support.microsoft.com/default.aspx?scid=kb;en-
us;818969&Product=sql2k => which is cannot update
estimated rows property on partiion after customized it
using DSO Programming/script.

My code is just similar such as sample of creating new
partition in SQL2K Resource Kit.

Is there any fixing for this bug, because we really need
it to automate partition detection and creation.

Regards,


Nugon


-=-=-=-=-=-=-

'/************************************************** *******
************
' File: ClonePart.vbs
'
' Desc: This sample script creates a new partition in the
FoodMart 2000
' Sales cube, based on the latest partition in the
cube. The
' purpose of the script is to show the kinds of DSO
calls that
' are used to clone a partition. The resulting
partition is
' processed, but adds no data to the cube.
'
' Users of this script may want to delete the
resulting partition
' after running the script and exploring the results.
'
' Parameters: None
'************************************************* *********
***********/


Sub Main()

On Error Resume Next

Dim intDimCounter, intErrNumber
Dim strOlapDB, strCube, strDB, strAnalysisServer,
strPartitionNew
Dim dsoServer, dsoDB, dsoCube, dsoPartition,
dsoPartitionNew

' Initialize server, database, and cube name variables.
strAnalysisServer = "localhost"
'strAnalysisServer = "DB-BI"
'strOlapDB = "Foodmart 2000"
'strCube = "Sales"
strOlapDB = "BI_FIF"
strCube = "RiskProfile"

' VBScript does not support direct use of enumerated
constants.
' However, constants can be defined to supplant
enumerations.
Const stateFailed = 2
Const olapEditionUnlimited = 0

' Connect to the Analysis server.
Set dsoServer = CreateObject("DSO.Server")
dsoServer.Connect strAnalysisServer

' If connection failed, then end the script.
If dsoServer.State = stateFailed Then
MsgBox "Error-Not able to connect to '" &
strAnalysisServer _
& "' Analysis server.", , "ClonePart.vbs"
Err.Clear
Exit Sub
End If

' Certain partition management features are available
only
' in the Enterprise Edition and Developer Edition
releases
' of Analysis Services.
If dsoServer.Edition <> olapEditionUnlimited Then
MsgBox "Error-This feature requires Enterprise or "
& _
"Developer Edition of SQL Server to " & _
"manage partitions.", , "ClonePart.vbs"
Exit Sub
End If

' Ensure that a valid data source exists in the
database.
Set dsoDB = dsoServer.MDStores(strOlapDB)
If dsoDB.DataSources.Count = 0 Then
MsgBox "Error-No data sources found in '" & _
strOlapDB & "' database.", , "ClonePart.vbs"
Err.Clear
Exit Sub
End If

' Find the cube.
If (dsoDB.MDStores.Find(strCube)) = 0 Then
MsgBox "Error-Cube '" & strCube & "' is missing.", ,
_
"ClonePart.vbs"
Err.Clear
Exit Sub
End If

' Set the dsoCube variable to the desired cube.
Set dsoCube = dsoDB.MDStores(strCube)

' Find the partition
If dsoCube.MDStores.Count = 0 Then
MsgBox "Error-No partitions exist for cube '" &
strCube & _
"'.", , "ClonePart.vbs"
Err.Clear
Exit Sub
End If

' Set the dsoPartition variable to the desired
partition.
Set dsoPartition = dsoCube.MDStores
(dsoCube.MDStores.Count)
MsgBox "New partition will be based on existing
partition: " _
& Chr(13) & Chr(10) & _
dsoDB.Name & "." & dsoCube.Name & "." & _
dsoPartition.Name, , "ClonePart.vbs"

' Get the quoting characters from the datasource, as
' different databases use different quoting characters.
Dim sLQuote, sRQuote
sLQuote = dsoPartition.DataSources(1).OpenQuoteChar
sRQuote = dsoPartition.DataSources(1).CloseQuoteChar

'************************************************* *********
***********
' Create the new partition based on the desired partition.
'************************************************* *********
***********

' Create a new, temporary partition.
strPartitionNew = "ZNewPartition" &
dsoCube.MDStores.Count
Set dsoPartitionNew = dsoCube.MDStores.AddNew("~temp")


''' Base Partition has SourceTable =
'' FACT_RISK_PROF_2004_05
' Clone the properties from the desired partition to the
' new partition.
dsoPartition.Clone dsoPartitionNew

' Change the partition name from "~temp" to the
' name intended for the new partition.
dsoPartitionNew.Name = strPartitionNew
dsoPartitionNew.AggregationPrefix = strPartitionNew
& "_"

' Set the fact table for the new partition.
dsoPartitionNew.SourceTable = _
sLQuote & "FIFDBA" & sRQuote & "." & sLQuote
& "FACT_RISK_PROF_2004_06" & sRQuote
'sLQuote & "FACT_RISK_PROF_2004_06" & sRQuote

' Set the FromClause and JoinClause properties of the
new
' partition.
dsoPartitionNew.FromClause = Replace
(dsoPartition.FromClause, _
dsoPartition.SourceTable,
dsoPartitionNew.SourceTable)

dsoPartitionNew.JoinClause = Replace
(dsoPartition.JoinClause, _
dsoPartition.SourceTable,
dsoPartitionNew.SourceTable)

' Change the definition of the data slice used by the
new
' partition, by changing the SliceValue properties of
the
' affected levels and dimensions to the desired values.
dsoPartitionNew.Dimensions("Time
Closing").CustomProperties("DimensionSlice").Value = "All
Time Closing.2004.Q2.Juni"
dsoPartitionNew.Dimensions("Time Closing").Levels
("All").SliceValue = "All Time Closing"
dsoPartitionNew.Dimensions("Time Closing").Levels
("Year").SliceValue = "2004"
dsoPartitionNew.Dimensions("Time Closing").Levels
("Quarter").SliceValue = "2"
dsoPartitionNew.Dimensions("Time Closing").Levels
("Month").SliceValue = "06"


' Estimate the rowcount.
dsoPartitionNew.EstimatedRows = 3000000

' Add another filter. The SourceTableFilter provides an
additional
' opportunity to add a WHERE clause to the SQL query
that will
' populate this partition. We're using this filter to
ensure our new
' partition contains zero rows. For the purposes of
this sample code
' we don't want to change the data in the FoodMart
cube. Comment out
' this line if you want to see data in the new
partition.

' dsoPartitionNew.SourceTableFilter =
dsoPartitionNew.SourceTable _
& "." & sLQuote & "time_id" & sRQuote & "=100"

dsoPartitionNew.SourceTableFilter = sLQuote & "FIFDBA"
& sRQuote & "." & sLQuote & "FACT_RISK_PROF_2004_06" &
sRQuote & "." & sLQuote & "FACT_AR_CLOSING_TIME_ID" &
sRQuote & ">= TO_DATE('01062004','ddmmyyyy') AND " & _
sLQuote & "FIFDBA" & sRQuote & "." & sLQuote
& "FACT_RISK_PROF_2004_06" & sRQuote & "." & sLQuote
& "FACT_AR_CLOSING_TIME_ID" & sRQuote & "< TO_DATE
('01072004','ddmmyyyy')"

' Save the partition definition in the metadata
repository
dsoPartitionNew.Update

' Check the validity of the new partition structure.
If Not dsoPartitionNew.IsValid Then
MsgBox "Error-New partition structure is invalid."
Err.Clear
Exit Sub
End If

MsgBox "New partition " & strPartitionNew & " has been
created and " _
& "processed. To see the new partition in Analysis
Manager, you " _
& "may need to refresh the list of partitions in the
Sales cube " _
& "of FoodMart 2000. The new partition contains no
data.", , _
"ClonePart.vbs"

' The next statement, which is commented out, would
process the partition.
' In a real partition management system, this would
likely be a separate
' process, perhaps managed via DTS.
' dsoPartitionNew.Process

' Clean up.
Set dsoPartition = Nothing
Set dsoPartitionNew = Nothing
Set dsoCube = Nothing
Set dsoDB = Nothing
dsoServer.CloseServer
Set dsoServer = Nothing


End Sub


-=-=-=-=-
==> Result of Source Table on New Partition is
FACT_RISK_PROF_2004_05, equal with previous/base
partition, it should be FACT_RISK_PROF_2004_06 not
FACT_RISK_PROF_2004_05
..

Dear All,
Cannot Update SourceTable Property of
Partition Through DSO can be solved using KB # 299930 SQL
Server (general, not specific to SQL2K) - PRB: Unable to
Set SourceTable Property of Partitions to a Different
Source Table.

Just input, may be related Engineer should test bug or
problem of older version to the newest version of each
product, for example SQL, Office, etc. and then update related Knowledge Base