DTS Fully process dimensions code

Using the following code to fully process dimension in a catalog on SQL Server 2000.

for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process processFull
Next

However this appears to do an incremental process of dimension rather than full process. (I would expect the cubes to be unprocessed after a full process.

Whats wrong with the code.
Cheers.
Mark.for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process (1)
Next

Have tested this and believe it to work. Still do not understand why above did not work.|||I assume you are using vbscript to process. If so you need to reference to the Constant "processFull" so that it applies it correctly. Otherwise it always just does it as a process default.sql

DTS Fully process dimensions code

Using the following code to fully process dimension in a catalog on SQL Server 2000.

for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process processFull
Next

However this appears to do an incremental process of dimension rather than full process. (I would expect the cubes to be unprocessed after a full process.

Whats wrong with the code.
Cheers.
Mark.for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process (1)
Next

Have tested this and believe it to work. Still do not understand why above did not work.|||I assume you are using vbscript to process. If so you need to reference to the Constant "processFull" so that it applies it correctly. Otherwise it always just does it as a process default.

DTS Execute from Com Object (Workgroup Version)

Hey Guys,

I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.

Thanks in advance!

JayStang wrote:

Hey Guys,

I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.

Thanks in advance!

I recommend you try the DTS newsgroup microsoft.public.sqlserver.dts

DTS Code works only with VS 2003 but not with VS 2005 ?

Hi;

I wanted to use the following code to run a DTS package from a 2005 Web Page code behind partial class. This code works fine in a VB 2003 module

going against SQl Srvr 2000.

Here is the code: (It initiates from a button click handler)

Dim conn As New SqlConnection("initial catalog=MY_Data;server= XYZ081552X7X441\TRSQL;integrated security=SSPI")

Dim hold1 As Integer

Dim hold_source As String = ""

Dim hold_desc As String = ""

Try

conn.Open()

Catch ex1 As Exception

MsgBox("The Test connection failed to open" & vbCrLf & ex1.Message)

End Try

MsgBox("About to create a DTS object")

Dim oPackage As New DTS.Package2Class (Compiler doesn't like this line) Type DTS.Package2Class is not defined.

Dim oStep As DTS.Step (Or this one) Type DTS.step is not defined.

oPackage.LoadFromSQLServer("XYZ81552X7X441\TRSQL", , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , "cpyPrinters2Excel", )

For Each oStep In oPackage.Steps

oStep.ExecuteInMainThread = True

Next

oPackage.Execute()

For Each oStep In oPackage.Steps

If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then

oStep.GetExecutionErrorInfo(hold1, , )

Else

End If

Next

oPackage.UnInitialize()

oPackage = Nothing

conn.Close()

Has Microsoft changed the DTS objects so that they work only with SQL Srv 2005 ?

Thanks for your insights.

DTS has been replaced with SSIS, so yes, plenty of things have changed.

DTS questions can be asked over at the DTS news group, if that helps: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||Thanks very much.

DTS between SQL 7 and SQL 2000

Hi!!!
I am a newbie and need know how can I use the DTS to transfer one DB from
SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
SQL_Latin1_General_CI_AS.
What I need to check during DTS operation to preserve SQL 7 code page in
order to avoid weird characters in SQL 2000?
Thanks for any information.> I am a newbie and need know how can I use the DTS to transfer one DB from
> SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
> SQL_Latin1_General_CI_AS.
> What I need to check during DTS operation to preserve SQL 7 code page in
> order to avoid weird characters in SQL 2000?
In SQL 2000, you can create a database with different collation than servers
default collation. So you can have a database on SQL 2000 with the same
collation that is present on SQL 7.0.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

DTS between SQL 7 and SQL 2000

Hi!!!
I am a newbie and need know how can I use the DTS to transfer one DB from
SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
SQL_Latin1_General_CI_AS.
What I need to check during DTS operation to preserve SQL 7 code page in
order to avoid weird characters in SQL 2000?
Thanks for any information.> I am a newbie and need know how can I use the DTS to transfer one DB from
> SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
> SQL_Latin1_General_CI_AS.
> What I need to check during DTS operation to preserve SQL 7 code page in
> order to avoid weird characters in SQL 2000?
In SQL 2000, you can create a database with different collation than servers
default collation. So you can have a database on SQL 2000 with the same
collation that is present on SQL 7.0.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

DTS between SQL 7 and SQL 2000

Hi!!!
I am a newbie and need know how can I use the DTS to transfer one DB from
SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
SQL_Latin1_General_CI_AS.
What I need to check during DTS operation to preserve SQL 7 code page in
order to avoid weird characters in SQL 2000?
Thanks for any information.
> I am a newbie and need know how can I use the DTS to transfer one DB from
> SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
> SQL_Latin1_General_CI_AS.
> What I need to check during DTS operation to preserve SQL 7 code page in
> order to avoid weird characters in SQL 2000?
In SQL 2000, you can create a database with different collation than servers
default collation. So you can have a database on SQL 2000 with the same
collation that is present on SQL 7.0.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

DTS and ASP

Suppose i create procedure in DTS and I want to manage call of this
procedure from ASP code. How I can manage this. I have SQL 2000 and for me
both ASP and ASP.net are interesting\
Thank you
Execute a package from Active Server Pages (ASP)
(http://www.sqldts.com/default.aspx?207)

DTS ActiveX Script error

The 1st 4 lines of code were copied verbatim from a VBS file that works
perfectly.
Why does it fail when running from a DTS ActiveX script?
Function Main()
set objShell = WScript.CreateObject("Wscript.Shell")
wait = true
strEXEC = "C:\FTPTest\FTPSNAP.BAT"
objShell.run strEXEC, 1, wait
Main = DTSTaskExecResult_Success
End Function
Debugging is not an option until I know how to make JIT debugging work...
(see my other posts on JIT Debugging)
Regards,
John> set objShell = WScript.CreateObject("Wscript.Shell")
The WScipt object is available from a Windows Scripting Host environment but
not from a DTS ActiveX script task.
It looks to me like you should use a DTS ExecuteProcess task instead.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Keith" <JohnKeith@.discussions.microsoft.com> wrote in message
news:FF140623-C03B-4DB6-BE33-852C933E0849@.microsoft.com...
> The 1st 4 lines of code were copied verbatim from a VBS file that works
> perfectly.
> Why does it fail when running from a DTS ActiveX script?
> Function Main()
> set objShell = WScript.CreateObject("Wscript.Shell")
> wait = true
> strEXEC = "C:\FTPTest\FTPSNAP.BAT"
> objShell.run strEXEC, 1, wait
> Main = DTSTaskExecResult_Success
> End Function
> Debugging is not an option until I know how to make JIT debugging work...
> (see my other posts on JIT Debugging)
> --
> Regards,
> John|||http://www.codeproject.com/useritems/DTS__VBNET_.asp|||Yep, thats what what needed.
Thanks!
--
Regards,
John
"Dan Guzman" wrote:

> The WScipt object is available from a Windows Scripting Host environment b
ut
> not from a DTS ActiveX script task.
> It looks to me like you should use a DTS ExecuteProcess task instead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Keith" <JohnKeith@.discussions.microsoft.com> wrote in message
> news:FF140623-C03B-4DB6-BE33-852C933E0849@.microsoft.com...
>
>|||Thanks for the reply!
I am not using VBNET, yet.
All my VB experience comes from MS Office with VBA.
Do you have a link that covers the same or similar topics from the
standpoint of using VB6 projects or an excel macro with VBA code?
From looking at the code samples on your link... I don't recognize the "Try"
and "End Try" statements nor the "Catch exc" I assume those are some new NE
T
commands?
Regards,
John
"vipinjosea" wrote:

> http://www.codeproject.com/useritems/DTS__VBNET_.asp
>

DTS + .net code

Hi,
My web application take data from excel sheet and store in the
database using dts. it is working fine when i run directly dts package
on sql server 2000 but i call sp ( exec master..xp_cmdshell 'dtsrun /S
/N /E ' ) from dot net code then it gives error
"A severe error occurred on the current command. The results, if any,
should be discarded "
*** Sent via Developersdex http://www.codecomments.com ***Have you tried running the command using Query Analyzer? If that works, try
adding 'no_output' to the statement executed by the application:
exec master..xp_cmdshell 'dtsrun /S /N MyPackage /E', no_output
Hope this helps.
Dan Guzman
SQL Server MVP
"Pooja Sharma" <pooja.sharma@.siliconbiztech.com> wrote in message
news:%23zgue0RSHHA.1364@.TK2MSFTNGP06.phx.gbl...
> Hi,
> My web application take data from excel sheet and store in the
> database using dts. it is working fine when i run directly dts package
> on sql server 2000 but i call sp ( exec master..xp_cmdshell 'dtsrun /S
> /N /E ' ) from dot net code then it gives error
> "A severe error occurred on the current command. The results, if any,
> should be discarded "
>
> *** Sent via Developersdex http://www.codecomments.com ***

DTS & ADO Password woes

Hi all,

I'm having a problem with DTS. I need to use DTS to connect to a DB using ADO.

Is there any way I do not have to hard code my user ID and Password within the DTS package? Or is there any setting to set the password and userID else where?

PLease help.

Thanks!Are you using an ActiveX script component? Or are you using the DTS connection object? I assume (because it's ADO you referred to) that you are using the ActiveX object (and that you are using VBScript). If so, what's the DB you are connecting to? If it is SQL, then try using integrated security:

oConn.ConnectionString =
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=MyServer

If the DTS package is scheduled by the SQL Agent service, just make sure that the SQL Agent service account (don't use local system) has the right privileges.

If it is not a SQL Server you are connecting to, you will need to provide some additional info...

Regards,

Hugh Scott

Originally posted by tswern
Hi all,

I'm having a problem with DTS. I need to use DTS to connect to a DB using ADO.

Is there any way I do not have to hard code my user ID and Password within the DTS package? Or is there any setting to set the password and userID else where?

PLease help.

Thanks!|||Originally posted by hmscott
Are you using an ActiveX script component? Or are you using the DTS connection object? I assume (because it's ADO you referred to) that you are using the ActiveX object (and that you are using VBScript). If so, what's the DB you are connecting to? If it is SQL, then try using integrated security:

oConn.ConnectionString =
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=MyServer

If the DTS package is scheduled by the SQL Agent service, just make sure that the SQL Agent service account (don't use local system) has the right privileges.

If it is not a SQL Server you are connecting to, you will need to provide some additional info...

Regards,

Hugh Scott

Hi hmscott,

You guessed correctly! Would give it a shot and see what happens.

Thanks!

DTS - Write Exceptions to table?

Can DTS write exceptions to a database table? In the code
below, I copy fname, lname and (at the end) company from
the source to the destination. However, with title, if the
title is executive or president, the title is copied over;
if the title is ceo it is entered into the destination
table as ceo/chairman; if none of these conditions apply
(i.e. title is owner) I want the entire row written to a
new table.

If I use Main = DTSTransformStat_SkipRow, rows that meet
the title criteria are entered into the destination table,
and the rows that don't meet the criteria are not entered.
I can get DTS to output the exeption file to a text file,
however, with the large number of records that don't meet
the criteria, it would be better to have them in a new
table.

Thanks,

Jay

----------

Function Main()
DTSDestination("fname") = DTSSource("fname")
DTSDestination("lname") = DTSSource("lname")

'Check Titles
If lcase(DTSSource("title")) = "executive" then
DTSDestination("title") = DTSSource("title")
ElseIf lcase(DTSSource("title")) = "president" then
DTSDestination("title") = DTSSource("title")
ElseIf lcase(DTSSource("title")) = "ceo" then
DTSDestination("title") = "CEO/Chairman"
Else
Main = DTSTransformStat_ExceptionRow
'Main = DTSTransformStat_SkipRow
Exit Function
End if



DTSDestination("company") = DTSSource("company")
Main = DTSTransformStat_OK
End FunctionYou can break your task in 2 phases:

First Transformation Task:
Insert / Update all rows that meet the criteria

Second Transformation Task:
Insert rows that does not meet the criteria in a anther table

Remeber the you can have more than one transformatrion task between two connections|||Thanks - I will give that a try.

DTS - SQL Task question

Hi,

I have a DTS package. One of the steps is SQL task step. I have the following code under SQL statment.

declare
@.p_dtProcessDate DateTime,
@.p_iResultCode int
SET @.p_dtProcessDate = getdate()
EXEC SPCreateMLRDailyRpt @.p_dtProcessDate,@.p_iResultCode out

I want to evaluate @.p_iResultCode and if it is not equal to zero, I want to make this SQL task step to be failed so that the entire DTS package itself fails.

Does any one know how to do this ? I appreciate all your help.

-BheemNot that familure with DTS but how about RAISERROR?|||Paul,

Thanx for the response. But that does not fail the step. It says DTS package completed successfully.

-Bheem|||Using RAISERROR is the correct answer. Please post your code that did not function correctly using RAISERROR.|||A RAISERROR will trigger the 'On Failure' workflow. This was used in the flowing past post

Problem with package creation (http://dbforums.com/showthread.php?s=&threadid=314650)|||My code is below.

declare @.p_iResultCode int

EXEC spValidateCLLITemp @.p_iResultCode out
IF @.p_iResultCode = 0
RAISERROR('Failed',16,1)

When I run the same query using query analyzer, RAISERROR works fine with the message displayed as 'Failed'.

But in DTS the SQL task step is completed successfully without any problem. What am I doing wrong here ?

Thanx for all the responses.

-Bheem|||Have you tried the same code but leaving out the "if" statement ? Basically, forcing a RAISERROR.|||Yes, I did. It is not working either. Basically RAISERROR is not finctioning in DTS.

Does anyone know why this code is not working ?

-Bheem|||What version/service pack of sql server are you using ?|||I am using

SQL Server 7.0
Version 4.0
Service pack 6

If I use the following in DTS SQL task it works.

declare @.test int
set @.test = 0
if @.test = 0
RAISERROR('Failed', 16,1)

But RAISERROR is not working when I use EXEC <stored proc>
Thanx..
-Bheem|||I've created a small DTS package to test RAISERROR. I use a OLEDB connect that points to the local server and Pubs database using NT authentication. It starts with a SQL Task that get the milliseconds of the current time using DATEPART. If the value is less than 500 then RAISERROR and else do nothing. The SQL Task has 2 workflows ON SUCCESS and ON FAILURE, each with a message box. I've attached the DTS package see if you can run this.

I had to rename the extension from .DTS to .TXT so that I could attach|||achorozy,

The attachment is unreadable. Can you please paste your code here.

thanx..
-Bheemsen|||It's a DTS package that you can't read with notepad but import into DTS using Enterprise Manager.

Long form:

1) Create OLEDB connection to any database
2) Create SQL Task, set connection to OLEDB created in (1). Set SQL statement toif (SELECT DATEPART(ms,GETDATE())) < 500
raiserror('',16,1)
3) Create ActiveX Script called Good, code
Function Main()
Msgbox "Good"
Main = DTSTaskExecResult_Success
End Function
5) Set workflow 'ON SUCCESS' from SQL Task to ActiveX 'Good'
6) Create ActiveX Script called Bad, code
Function Main()
Msgbox "Bad"
Main = DTSTaskExecResult_Success
End Function
7) Set workflow 'ON FAILURE' from SQL Task to ActiveX 'Bad'

Run the package, it should flip from msgbox 'Good' to 'Bad' as the milliseconds in GETDATE() change from over 500 to under 500.|||achorozy,

Thanx for your response. Your code perfectly works. As I said, I had already tested RAISERROR with a SQL statment in the SQL task. That works.

But the RAISERROR is not working in my following code.

declare @.p_iResultCode int

EXEC spValidateCLLITemp @.p_iResultCode out
IF @.p_iResultCode = 0
RAISERROR('Failed',16,1)

When I run the same query using query analyzer, RAISERROR works fine with the message displayed as 'Failed'.

But in DTS the SQL task step says it is "completed successfully", it should fail actually. What am I doing wrong here ?

-Bheemsen|||Now I've changed my SQL Task.

I created a stored procedure :
CREATE PROC sp_Test (@.x int OUTPUT)
AS
SET NOCOUNT ON

SELECT @.x = DATEPART(ms,GETDATE())
Then I modified my SQL Task's SQL Statement to be
declare @.x int

exec sp_Test @.x OUTPUT

IF (@.x < 500)
raiserror('',16,1) This works fine for me. I get the RAISERROR sometimes (@.x < 500) and sometomes not (@.x >= 500).|||Are you still having the problem ?|||rnealejr,

Yes, I still have the problem. It is really weird. The same code works well in query analyzer, but not in DTS sql task step. It never fails there.

Your code is very perfect. It is wroking fine in DTS sql step as well as in query analyzer.

Looks like may be something wrong with my procedure. It is working fine when I write a small stored procedure and test the same return code in sql task step.

It is still a mystery for me.

Thanx for all responses. Please let me know if you find something for me. All the information regarding my problem is in this thread.

-Bheemsen|||There is a problem with the OLEDB provider for SQL. You need to download the latest mdac for your machine. If install version 2.6 or greater you should be ok. This should correct the non-failure in your dts script. If not, let me know.

Good luck.|||rnealejr,

I am sorry to ask this question. How do I check my OLEDB version and from where I should download the latest mdac ?

Thanx for all your help.
-Bheemsen|||Search for sqloledb.dll - right click on the file and go to properties - go to the version tab. Let me know which version you have installed.|||The OLEDB version installed is 07.01.0819

When I run "select @.@.version" in query analyzer, I got the following.

Microsoft SQL Server 7.00 - 7.00.842 (Intel X86)
Mar 2 2000 06:49:37
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

Thanx..
-Bheemsen|||Go to the following web site for mdac version 2.6:

mdac (http://www.microsoft.com/data/download_260rtm.htm)

After the installation, make a note as to which version is installed now. It should begin with 2000. You can also upgrade to 2.7 version.
FYI: There is a service pack for 2.6.

Good luck.|||rnealejr,

Thanx a million for all that help. It worked like a charm. It looks like this bug fix is incorporated by Microsoft in its recent SP4 also. I had installed SP4 too on my machine. I need to inform my production support people to install the same.

Thanx again.
-Bheemsen|||Good deal. Be careful, because not even sp4 has the latest drivers and your dts problem will still be around. You can test it out on your existing servers and let me know (or uninstall the sp4 on your machine).

Good luck.|||Thanx. I will. How did you figure out this fix ??

Also, when you have time, can you please take a look at my another question at thread "Delete query to remove duplicate rows"

-Bheemsen

DTS

Hi,
(i).I wanted to transfer data from SQL server to access through code.
or Activex Script.
(ii) how can i devide a table in two parts means one table but
vertually two tables.
from
Killer
doller
www.sqldts.com
"doller" <sufianarif@.gmail.com> wrote in message
news:1125895448.595557.6480@.g44g2000cwa.googlegrou ps.com...
> Hi,
> (i).I wanted to transfer data from SQL server to access through code.
> or Activex Script.
> (ii) how can i devide a table in two parts means one table but
> vertually two tables.
>
> from
> Killer
>
|||Hi Uri,
Canu pls tell me the exact link to that page where sqldts.com have
informagtion about me question.
And what about my second question.
from
doller
|||(i).I wanted to transfer data from SQL server to access through code.
or Activex Script
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988732.096701.123580@.z14g2000cwz.googlegr oups.com...
> Hi Uri,
> Canu pls tell me the exact link to that page where sqldts.com have
> informagtion about me question.
> And what about my second question.
> from
> doller
>

DTS

Hi,
(i).I wanted to transfer data from SQL server to access through code.
or Activex Script.
(ii) how can i devide a table in two parts means one table but
vertually two tables.
from
Killerdoller
www.sqldts.com
"doller" <sufianarif@.gmail.com> wrote in message
news:1125895448.595557.6480@.g44g2000cwa.googlegroups.com...
> Hi,
> (i).I wanted to transfer data from SQL server to access through code.
> or Activex Script.
> (ii) how can i devide a table in two parts means one table but
> vertually two tables.
>
> from
> Killer
>|||Hi Uri,
Canu pls tell me the exact link to that page where sqldts.com have
informagtion about me question.
And what about my second question.
from
doller|||(i).I wanted to transfer data from SQL server to access through code.
or Activex Script
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988732.096701.123580@.z14g2000cwz.googlegroups.com...
> Hi Uri,
> Canu pls tell me the exact link to that page where sqldts.com have
> informagtion about me question.
> And what about my second question.
> from
> doller
>

DTS

Hi,
(i).I wanted to transfer data from SQL server to access through code.
or Activex Script.
(ii) how can i devide a table in two parts means one table but
vertually two tables.
from
Killerdoller
www.sqldts.com
"doller" <sufianarif@.gmail.com> wrote in message
news:1125895448.595557.6480@.g44g2000cwa.googlegroups.com...
> Hi,
> (i).I wanted to transfer data from SQL server to access through code.
> or Activex Script.
> (ii) how can i devide a table in two parts means one table but
> vertually two tables.
>
> from
> Killer
>|||Hi Uri,
Canu pls tell me the exact link to that page where sqldts.com have
informagtion about me question.
And what about my second question.
from
doller|||(i).I wanted to transfer data from SQL server to access through code.
or Activex Script
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988732.096701.123580@.z14g2000cwz.googlegroups.com...
> Hi Uri,
> Canu pls tell me the exact link to that page where sqldts.com have
> informagtion about me question.
> And what about my second question.
> from
> doller
>

DSO partitioning problem

Hi,
I have some DSO code which is partitioning a cube very nicely by deleting
and recreating old monthly partitions by cloning an original month partition.
When I actually come to test the data present in the cube I only ever get the
the original partitions data.
If I use the partition wizard to check out the filter settings for the new
partitions they all look OK - so I get an original partition for August and
then new ones for September, October, November.
In code I am setting the DimensionSlice, SliceValue and MemberKeyColumn
fields of the new partitions but get the feeling I am missing something.
Any ideas out there?
Yup. Find a copy of the SQL Server 2000 Resource Kit. On it was a utility
called the Metadata Scripter. When you installed it into Analysis Manager,
you can right-click on an object and generate a VB6 program which will
re-create that object. It generated DSO code. So the sequence of events to
figure out how to program with DSO is:
1) start from a known system.
2) do whatever you do in your custom application, then use the metadata
scriptor to generate "ProgA.vb".
3) go back to your known system
4) do whatever you do using Analysis Manager, then use the metadata scripter
to generate "ProgB.vb"
5) do a windiff comparing ProgA.vb and ProgB.vb -- and you will see that
there is some property or setting which your application is missing, but
Analysis Manager catches.
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in
message news:6D35B127-A2CB-442A-83BF-A63E4DBBE564@.microsoft.com...
> Hi,
> I have some DSO code which is partitioning a cube very nicely by deleting
> and recreating old monthly partitions by cloning an original month
> partition.
> When I actually come to test the data present in the cube I only ever get
> the
> the original partitions data.
> If I use the partition wizard to check out the filter settings for the new
> partitions they all look OK - so I get an original partition for August
> and
> then new ones for September, October, November.
> In code I am setting the DimensionSlice, SliceValue and MemberKeyColumn
> fields of the new partitions but get the feeling I am missing something.
> Any ideas out there?

DSO partitioning problem

Hi,
I have some DSO code which is partitioning a cube very nicely by deleting
and recreating old monthly partitions by cloning an original month partition
.
When I actually come to test the data present in the cube I only ever get th
e
the original partitions data.
If I use the partition wizard to check out the filter settings for the new
partitions they all look OK - so I get an original partition for August and
then new ones for September, October, November.
In code I am setting the DimensionSlice, SliceValue and MemberKeyColumn
fields of the new partitions but get the feeling I am missing something.
Any ideas out there?Yup. Find a copy of the SQL Server 2000 Resource Kit. On it was a utility
called the Metadata Scripter. When you installed it into Analysis Manager,
you can right-click on an object and generate a VB6 program which will
re-create that object. It generated DSO code. So the sequence of events to
figure out how to program with DSO is:
1) start from a known system.
2) do whatever you do in your custom application, then use the metadata
scriptor to generate "ProgA.vb".
3) go back to your known system
4) do whatever you do using Analysis Manager, then use the metadata scripter
to generate "ProgB.vb"
5) do a windiff comparing ProgA.vb and ProgB.vb -- and you will see that
there is some property or setting which your application is missing, but
Analysis Manager catches.
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in
message news:6D35B127-A2CB-442A-83BF-A63E4DBBE564@.microsoft.com...
> Hi,
> I have some DSO code which is partitioning a cube very nicely by deleting
> and recreating old monthly partitions by cloning an original month
> partition.
> When I actually come to test the data present in the cube I only ever get
> the
> the original partitions data.
> If I use the partition wizard to check out the filter settings for the new
> partitions they all look OK - so I get an original partition for August
> and
> then new ones for September, October, November.
> In code I am setting the DimensionSlice, SliceValue and MemberKeyColumn
> fields of the new partitions but get the feeling I am missing something.
> Any ideas out there?

DSO Cell Calculation Problem

Hi
I am trying to set up a cell calculation using DSO and can not figure
out why it fails. The code is in VB6 (also tried in c#) but to no
avail.
Any help - thanks a lot
--
Dim dsoServer As New DSO.Server
Dim dsodb As Variant
dsoServer.Name = "ServerName"
dsoServer.Connect "ServerName"
Set dsodb = dsoServer.MDStores.Item("DBName") ' Name of AS Database
Dim dsoCube As DSO.Cube
Set dsoCube = dsodb.MDStores.Item("Test") ' Name of Cube
Dim dsoCommand As Variant
' Remove if found
If dsoCube.Commands.Find("CommandName") Then
dsoCube.Commands.Remove ("CommandName")
End If
Set dsoCommand = dsoCube.Commands.AddNew("CommandName")
dsoCommand.CommandType = DSO.CommandTypes.cmdCreateCellCalculation
dsoCommand.Description = "CommandDescription"
dsoCommand.Statement = "CREATE CELL CALCULATION [Test].[x] FOR
'{[Account].&[5102]}' AS '1001'"
dsoCube.Update
dsoServer.CloseServer
--
It creates a cell calulation object and saves it in the cube, but when
I look at it in analysis manager I get 2 errors...
1. Unable to pase the CREATE CELL FORMULA command. Please use DSO to
edit this command. Automation Error.
2. Unable to pase the CREATE CELL CALCULATION command. Please use DSO
to edit this command. Automation Error.
thanksSOLVED
The Statement needed "( )" around the sets in the FOR part (which would
normally indicate a tuple of the sets - which does not make sense to
me)... so it looks like this...
dsoCommand.Statement = "CREATE CELL CALCULATION CURRENTCUBE.[x] FOR
'({[Account].&[5102]})' AS '1001'"
I also used CURRENTCUBE instead of naming the cube.
No I can put the other 400 calcs in the cube and see how it performs
Hope this saves someone some time.

DSO

I was using DSO to make atree based on this example. But my Question is where could i put code for complexity_penalty or minimum_leaf_case in my code below?

And what's complexity_penalty or minimum_leaf_case use for? are they important in our coding?

Public Sub CreateRelMiningModel()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoDS As DSO.DataSource
Dim dsoDMM As DSO.MiningModel
Dim dsoColumn As DSO.Column
Dim dsoRole As DSO.Role

Dim strLQuote As String, strRQuote As String
Dim strFromClause As String

' Constants used for DataType property
' of the DSO.Column object.
' Note that these constants are identical to
' those used in ADO in the DataTypeEnum enumeration.
Const adInteger = 3
Const adWChar = 130

' Connect to the server on this computer.
dsoServer.Connect "LocalHost"

' Select the FoodMart 2000 database.
Set dsoDB = dsoServer.MDStores("FoodMart 2000")

' Retrieve the open and close quote characters for
' the FoodMart data source.
strLQuote = dsoDB.DataSources("FoodMart").OpenQuoteChar
strRQuote = dsoDB.DataSources("FoodMart").CloseQuoteChar

' The Customer table is the fact table for this
' relational data mining model; this variable will
' make it easier to understand the code that
' follows.
strFromClause = strLQuote & "customer" & strRQuote

' Check for the existence of the model on this computer.
If Not dsoDB.MiningModels("CustSalesModelRel") Is Nothing Then
' If this model exists, delete it.
dsoDB.MiningModels.Remove "CustSalesModelRel"
End If

' Create a new relational mining model
' called CustSalesModelRel.
Set dsoDMM = dsoDB.MiningModels.AddNew("CustSalesModelRel", _
sbclsRelational)

' Create a new mining model role called All Users
Set dsoRole = dsoDMM.Roles.AddNew("All Users")

' Set the needed properties for the new mining model.
With dsoDMM
.DataSources.AddNew "FoodMart", sbclsRegular
' Set the description of the model.
.Description = "Analyzes the salaries " & _
"of customers"
' Set the case table for the model to the
' Customer table.
.FromClause = strFromClause
' Select the algorithm provider for the model.
.MiningAlgorithm = "Microsoft_Decision_Trees"
' Let DSO define the training query.
.TrainingQuery = ""
' Save the existing structure.
.Update
End With

' Create the columns pertinent to the new model.

' Create the CustomerID column as a key column.
Set dsoColumn = dsoDMM.Columns.AddNew("CustomerID", _
sbclsRegular)
' Set the column properties for the new column.
With dsoColumn
' Set the source field from the case table for
' the column.
.SourceColumn = strFromClause & "." & strLQuote & _
"customer_id" & strRQuote
.DataType = adInteger
.IsKey = True
.IsDisabled = False
End With

' Create the Gender column as an attribute column.
Set dsoColumn = dsoDMM.Columns.AddNew("Gender", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"gender" & strRQuote
.DataType = adWChar
.IsDisabled = False
End With

' Create the Marital Status column as an attribute column.
Set dsoColumn = dsoDMM.Columns.AddNew("Marital Status", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"marital_status" & strRQuote
.DataType = adWChar
.IsDisabled = False
End With

' Create the Education column as an attribute column.
Set dsoColumn = dsoDMM.Columns.AddNew("Education", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"education" & strRQuote
.DataType = adWChar
.IsDisabled = False
End With

' Create the Yearly Income column as an predictable column.
Set dsoColumn = dsoDMM.Columns.AddNew("Yearly Income", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"yearly_income" & strRQuote
.DataType = adWChar
.IsInput = False
.IsPredictable = True
.IsDisabled = False
End With

' Save the data mining model.
With dsoDMM
' Set the LastUpdated property of the new mining model
' to the present date and time.
.LastUpdated = Now
' Save the model definition.
.Update
End With

' Process the data mining model.
With dsoDMM
' Lock the mining model for processing
.LockObject olapLockProcess, _
"Processing the data mining model in sample code"
' Fully process the new mining model.
' This may take up to several minutes.
.Process processFull
' Unlock the model after processing is complete.
.UnlockObject
End With

' Clean up objects and close server connection
Set dsoRole = Nothing
Set dsoColumn = Nothing
Set dsoDMM = Nothing

dsoServer.CloseServer
Set dsoServer = Nothing

End Sub

thank's a lot

COMPLEXITY_PENALTY and MINIMUM_LEAF_CASES control the growth of decision trees - a higher complexity_penalty lowers the likelihood of splits; increasing minimum_leaf_cases prevents generation of leaf nodes with low support (number of cases that fall into that node).

You can add training parameters using the MiningModel object's Parameters collection. This download includes the source code for an Analysis Mgr Add-in that edits mining model parameters using DSO: http://www.sqlserverdatamining.com/DMCommunity/SQLServer2000/Links_LinkRedirector.aspx?id=97