uninstall SQL: management

Showing posts with label management. Show all posts

Thursday, March 8, 2012

Cascade Inserts/Clone a tree

I am creating a small project management application using SQL Server. There
are three main tables (prjProjects, prjTasks, prjDetails). A project can
have 0 to many tasks and a tasks can have 0 to many details.
Many of our projects are very similar in that they would consist of
basically the same task and detail lists/records. I would like to be able to
"clone" a project using a stored procedure. I will be accessing the tables
from multiple front ends such as MS Access, ColdFusion, and ASP. A stored
procedure would allow me to create a new, cloned project from any of these
front ends.
I am not sure where to start with this. I have searched Google and news
groups without finding much useful information. Do I need to create a cursor
to loop through records and perform inserts or is there a more efficient
method? The tables, significant fields, and records are created below. There
are additional fields for descriptions, dates, etc but if I get the basic
framework, I should be able to include the other fields. I would like to
duplicate the "TITLE" field values in the new records.
Hope I have provided enough info that someone can point me in the right
direction.
Duane Hookom
MS Access MVP
CREATE TABLE [dbo].[prjProjects]
(
[PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjTasks] (
[TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
[TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TS_PR_ID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjDetails]
(
[DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DT_TS_ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
(
[PR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
(
[TS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
(
[DT_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] ADD
CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
(
[TS_PR_ID]
) REFERENCES [dbo].[prjProjects] (
[PR_ID]
)
GO
ALTER TABLE [dbo].[prjDetails] ADD
CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
(
[DT_TS_ID]
) REFERENCES [dbo].[prjTasks] (
[TS_ID]
)
GO
INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
Two',2)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
Two',2)
GO"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
First you should be to add some important constraints. These are my
assumptions of course but the keys will be important here.
ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
UNIQUE (PR_TITLE);
ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
UNIQUE (TS_TITLE, TS_PR_ID);
ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
UNIQUE (DT_TITLE, DT_TS_ID);
Now create a proc something like this (error handling omitted for brevity):
CREATE PROC dbo.usp_project_copy
(
@.pr_id INT,
@.pr_title NVARCHAR(50),
@.new_pr_id INT OUTPUT
)
AS
BEGIN
INSERT INTO dbo.prjProjects (PR_TITLE)
VALUES (@.pr_title) ;
SET @.new_pr_id = SCOPE_IDENTITY();
INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
SELECT TS_TITLE, @.new_pr_id
FROM dbo.prjTasks
WHERE TS_PR_ID = @.pr_id ;
INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
SELECT D1.DT_TITLE, T2.TS_ID
FROM dbo.prjDetails AS D1
JOIN dbo.prjTasks AS T1
ON D1.DT_TS_ID = T1.TS_ID
AND T1.TS_PR_ID = @.pr_id
JOIN dbo.prjTasks AS T2
ON T1.TS_TITLE = T2.TS_TITLE
AND T2.TS_PR_ID = @.new_pr_id
AND T1.TS_TITLE = T2.TS_TITLE ;
RETURN
END
GO
DECLARE @.pr_id INT
EXEC dbo.usp_project_copy
@.pr_id = 1,
@.pr_title = 'New Project',
@.new_pr_id = @.pr_id OUTPUT ;
SELECT *
FROM prjProjects
WHERE PR_ID = @.pr_id ;
Thanks for including the DDL. Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You could create a script file, very similar to the DDL/DML you offered,
adding additional elements (as David indicated), and then execute the script
file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
If you were to use any variables in the script file, remember that when a GO
is encountered, the variable goes out of scope, so you would have to
re-create and initialize it again.
If you want to automate the process, the script file can be executed from a
SQL Agent Job, or even from a stored procedure.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
> CREATE TABLE [dbo].[prjProjects]
> (
> [PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjTasks] (
> [TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TS_PR_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjDetails]
> (
> [DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DT_TS_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
> CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
> (
> [PR_ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
> CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
> (
> [TS_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
> CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
> (
> [DT_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjTasks] ADD
> CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
> (
> [TS_PR_ID]
> ) REFERENCES [dbo].[prjProjects] (
> [PR_ID]
> )
> GO
> ALTER TABLE [dbo].[prjDetails] ADD
> CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
> (
> [DT_TS_ID]
> ) REFERENCES [dbo].[prjTasks] (
> [TS_ID]
> )
> GO
> INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> One',1)
> GO
>
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> Two',2)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> Two',2)
> GO
>|||David and Arnie,
Thanks much. This should provide a good starting point for me. All of my
projects, tasks, and details have start and expected dates associated so I
will need to include them. I'm fairly sure I can get this on my own. If not,
I'll be back.
Duane Hookom
MS Access MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OHES$RsmGHA.3796@.TK2MSFTNGP05.phx.gbl...
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> First you should be to add some important constraints. These are my
> assumptions of course but the keys will be important here.
> ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
> UNIQUE (PR_TITLE);
> ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
> UNIQUE (TS_TITLE, TS_PR_ID);
> ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
> UNIQUE (DT_TITLE, DT_TS_ID);
>
> Now create a proc something like this (error handling omitted for
> brevity):
> CREATE PROC dbo.usp_project_copy
> (
> @.pr_id INT,
> @.pr_title NVARCHAR(50),
> @.new_pr_id INT OUTPUT
> )
> AS
> BEGIN
> INSERT INTO dbo.prjProjects (PR_TITLE)
> VALUES (@.pr_title) ;
> SET @.new_pr_id = SCOPE_IDENTITY();
> INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
> SELECT TS_TITLE, @.new_pr_id
> FROM dbo.prjTasks
> WHERE TS_PR_ID = @.pr_id ;
> INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
> SELECT D1.DT_TITLE, T2.TS_ID
> FROM dbo.prjDetails AS D1
> JOIN dbo.prjTasks AS T1
> ON D1.DT_TS_ID = T1.TS_ID
> AND T1.TS_PR_ID = @.pr_id
> JOIN dbo.prjTasks AS T2
> ON T1.TS_TITLE = T2.TS_TITLE
> AND T2.TS_PR_ID = @.new_pr_id
> AND T1.TS_TITLE = T2.TS_TITLE ;
> RETURN
> END
> GO
> DECLARE @.pr_id INT
> EXEC dbo.usp_project_copy
> @.pr_id = 1,
> @.pr_title = 'New Project',
> @.new_pr_id = @.pr_id OUTPUT ;
> SELECT *
> FROM prjProjects
> WHERE PR_ID = @.pr_id ;
> Thanks for including the DDL. Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||As an additional piece of information.
I would have a stored procedure creates the script file. By so doing, then I
could pass in the changable pieces of data, the stored procedure could
re-create all variables after encountering a GO in the script, and the end
result is a comprehensive script file that does not require me to do a
'search and destroy' (I mean search are replace) with the chance of missing
some location where a value needed to be replaced.
I've done it with two different processes. One, just use PRINT statements
line by line, then copy the generated output into a new QA window and save
(or execute the script). Or, two, using OSQL, output the generated script to
a file. I find the first choice gives me greater control on formating,
readibility, etc.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:etEkjismGHA.4164@.TK2MSFTNGP03.phx.gbl...
> You could create a script file, very similar to the DDL/DML you offered,
> adding additional elements (as David indicated), and then execute the
> script file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
> If you were to use any variables in the script file, remember that when a
> GO is encountered, the variable goes out of scope, so you would have to
> re-create and initialize it again.
> If you want to automate the process, the script file can be executed from
> a SQL Agent Job, or even from a stored procedure.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>

Wednesday, March 7, 2012

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work in
Management Studio. Further more, when I import data from my SQL 2000 and then
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.
Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.
|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in the
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:

> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> fields
> in
> then
> carriage
> Management
>
>

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work in
Management Studio. Further more, when I import data from my SQL 2000 and then
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in the
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:
> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> > I'm using SQL Server 2005 Manage Studio to edit some varchar and text
> fields
> > I have in my database. I want to insert carriage returns in my text, and I
> > tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
> in
> > Management Studio. Further more, when I import data from my SQL 2000 and
> then
> > open the table for editing in Management Studio I find that all the
> carriage
> > returns are not visible. I say not visible, because I know they're still
> > there because my application reads them fine. Is this a UI bug in
> Management
> > Studio?!!! Anyone knows of a fix or work around? Thanks.
>
>

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work i
n
Management Studio. Further more, when I import data from my SQL 2000 and the
n
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in th
e
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:

> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> fields
> in
> then
> carriage
> Management
>
>

Saturday, February 25, 2012

Capturing Processing Status and Progress Messages

When you use BI Dev Studio or SS Management Studio to process an Analysis Services database or object, there's a nice dialog box that shows very robust status and progress information.

Is there any way to capture this information if you are processing something programmatically? Specifically interested in both the ability to capture and display this information to a user when processing via a custom application. Also interested in whether or not there's a way to capture this information to a log (or table) for display and analysis after the fact (or if the custom application is running in a batch mode via a schedule).

Thanks,
Dave Fackler
I've been looking for the same thing. Were you able to find where this information is stored/logged?

Thanks!
|||One simple way to do this is using the SQL Server Profiler tool which has the option of logging trace information to files or tables as well as the UI. You can also do this programmatically using the AMO using Server.SessionTrace or by adding custom traces to Server.Traces.|||

Using the AMO, you can listen Session Trace. Sample code is following:

this.sessionTrace = server.SessionTrace;

this.sessionTrace.OnEvent += new TraceEventHandler(sessionTrace_TraceEventHandler);

this.sessionTrace.Stopped += new TraceStoppedEventHandler(sessionTrace_Stopped);

if (!this.sessionTrace.IsStarted)

{

this.sessionTrace.Start();

}

|||

You can use AMO (Microsoft.AnalysisServices.dll from "%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies") to subscribe to trace events. Sample code below. The code formatting might be lost when posting, but copy paste in VS, and then "Edit -> Advanced -> Format Document" should fix it.

//=====================================================================
//
// File: Program.cs
// Summary: Sample code for using traces with AMO.
// Date: 2006-05-23
//
//
//
// Copyright (C) Microsoft Corporation. All rights reserved.
//
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
// ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
// THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
//
//=====================================================================
using System;
using Microsoft.AnalysisServices;

namespace Microsoft.AnalysisServices.CodeSamples
{
/// <summary>
/// Demonstrates use of traces with AMO.
/// </summary>
/// <remarks>
/// This code requires the "Adventure Works" database to be present on the Analysis Services server
/// and also the "AdventureWorksDW" relational database on SQL Server (for processing objects to
/// generate trace events).
/// </remarks>
class AmoTrace
{
static int Main(string[] args)
{
//--
// There are 2 types of traces that AMO programmer can use:
// - the session trace: provides the events from the current session (established
// on Connect)
// - the custom traces: they allow to choose the particular event classes and
// columns to be traced. Unlike the session trace, a custom trace needs to be
// created and saved to server explicitly (as any other AMO object, like a
// Dimension or a Cube).
//
// This is the plan of the code:
// 1. we'll connect to an Analysis Services server
// 2. we'll locate the "Product" dimension from the "Adventure Works DW" database
// (we'll process it to generate trace events)
// 3. we'll use the session trace
// 4. we'll create, use and then delete a custom trace
//--

string connectionString = "Data Source=localhost";
string databaseName = "Adventure Works DW"; // use "Adventure Works DW Standard Edition" if you have the standard edition installed

try
{
//--
// STEP 1: connect to Analysis Services server.
//--
Server server = new Server();
server.Connect(connectionString);

try
{
//--
// STEP 2: locate the "Product" dimension.
//--
Database database = server.Databases.FindByName(databaseName);
if (database == null)
{
Console.Error.WriteLine("The [{0}] database is missing.", databaseName);
return 1;
}

Dimension productDimension = database.Dimensions.FindByName("Product");
if (productDimension == null)
{
Console.Error.WriteLine("The [Product] dimension is missing.");
return 1;
}

//--
// STEP 3: use the session trace.
//--
UseSessionTrace(server, productDimension);

//--
// STEP 4. create, use and then delete a custom trace.
//--
UseCustomTrace(server, productDimension);

return 0;
}
finally
{
server.Disconnect();
}
}
catch (Exception e)
{
Console.Error.WriteLine(e.ToString());
return 1;
}
}

/// <summary>
/// Demonstrates the use of session trace.
/// </summary>
private static void UseSessionTrace(Server server, Dimension dimensionToProcess)
{
//--
// 1. Subscribe to the session trace events.
//--
SessionTrace sessionTrace = server.SessionTrace;
TraceEventHandler onTraceEvent = new TraceEventHandler(OnTraceEvent);
TraceStoppedEventHandler onTraceStopped = new TraceStoppedEventHandler(OnTraceStopped);

sessionTrace.OnEvent += new TraceEventHandler(OnTraceEvent);
sessionTrace.Stopped += new TraceStoppedEventHandler(OnTraceStopped);
sessionTrace.Start(); // this method is not blocking, it starts a separate thread to listen for events from server

//--
// 2. Process the dimension; this will generate events that we'll display to
// the Console.
//--
try
{
dimensionToProcess.Process(ProcessType.ProcessFull); // this method blocks; while processing, events are received
}
finally
{
//--
// 3. Un-subscribe from the trace.
//--
sessionTrace.Stop();
sessionTrace.OnEvent -= onTraceEvent;
sessionTrace.Stopped -= onTraceStopped;
}
}

/// <summary>
/// Demonstrates the use of a custom trace.
/// </summary>
private static void UseCustomTrace(Server server, Dimension dimensionToProcess)
{
//--
// 1. Create and save to server a custom trace with only the
// ProgressReportBegin, ProgressReportCurrent and ProgressReportEnd events
// and the EventClass and EventSubclass columns.
//--
Trace trace = server.Traces.Add(); // a Name and ID will be generated for the new Trace
TraceEvent event1 = trace.Events.Add(TraceEventClass.ProgressReportBegin);
TraceEvent event2 = trace.Events.Add(TraceEventClass.ProgressReportCurrent);
TraceEvent event3 = trace.Events.Add(TraceEventClass.ProgressReportEnd);

event1.Columns.Add(TraceColumn.EventClass);
event1.Columns.Add(TraceColumn.EventSubclass);

event2.Columns.Add(TraceColumn.EventClass);
event2.Columns.Add(TraceColumn.EventSubclass);

event3.Columns.Add(TraceColumn.EventClass);
event3.Columns.Add(TraceColumn.EventSubclass);

// Save the newly created Trace to the server; others could use it (unlike the session
// trace which is specific to a particular session).
trace.Update();

//--
// 2. Subscribe to the newly create trace.
//--
TraceEventHandler onTraceEvent = new TraceEventHandler(OnTraceEvent);
TraceStoppedEventHandler onTraceStopped = new TraceStoppedEventHandler(OnTraceStopped);

trace.OnEvent += new TraceEventHandler(OnTraceEvent);
trace.Stopped += new TraceStoppedEventHandler(OnTraceStopped);
trace.Start(); // this method is not blocking, it starts a separate thread to listen for events from server

//--
// 3. Process the dimension; this will generate events that we'll display to
// the Console.
//--
try
{
dimensionToProcess.Process(ProcessType.ProcessFull); // this method blocks; while processing, events are received
}
finally
{
//--
// 3. Un-subscribe from the trace.
//--
trace.Stop();
trace.OnEvent -= onTraceEvent;
trace.Stopped -= onTraceStopped;
}

//--
// 4. Cleanup: delete the trace from the server. We could leave it (to be used
// in the future or by other users), but we'll delete it since it was only a
// sample.
//--
trace.Drop();
}

/// <summary>
/// Event handler for trace events, called on a separate thread by the AMO trace.
/// </summary>
/// <remarks>
/// When the Start() method is called on a Trace, AMO creates a separate thread
/// that listens for events from the server; when an event is read, this method is
/// called on that thread. There should be no heavy calculations in this method
/// because the trace thread will be blocked and events might overflow the connection's
/// buffer(s), resulting in loss of events.
/// </remarks>
private static void OnTraceEvent(object sender, TraceEventArgs e)
{
Console.WriteLine("Event ({0}, {1})", e.EventClass, e.EventSubclass);
}

/// <summary>
/// Event handler for trace stop events, called on a separate thread by the AMO trace when
/// a trace is stopped (by the user, by the server or by an exception).
/// </summary>
/// <remarks>
/// There are 3 main reasons for a trace to be stopped:
/// - the user called the Stop() method
/// - the server ended the trace (because somebody deleted it meanwhile for example)
/// - an exception occured during parsing of events (because of a network disconnect for example)
/// </remarks>
private static void OnTraceStopped( ITrace sender, TraceStoppedEventArgs e )
{
Console.WriteLine("Trace Stopped: Cause = {0}, Exception = {1}", e.StopCause, e.Exception);
}
}
}

Adrian Dumitrascu.

|||Does anyone know if there is anyway of figuring out which events support which columns from the object model? At the moment I have had to resort to hard coding the columns supported by a given event.|||Thanks for all the responses and the sample code!

I would like to capture this information using SSIS. Of course, the script tasks only supports VB as well. Is there an easy way to capture trace information from the cube processing within SSIS?

Thanks again for all the help!
|||

AMO doesn't provide the list of supported columns for a particular trace event. But, there are 2 other places containing the associations:

- the "tracedefinition90.xml" file (search for it in "%ProgramFiles%\Microsoft SQL Server"). You will need to parse it to generate a user-friendlier map. But please note that this file might change in the future (probably not the xml schema, but columns/events might be added/removed/changed).

- SQL Profiler; when you create a new AS2005 trace, in the 'Trace Properties' window, see the 'Events Selection' tab (generated from the trace definition file)

Adrian Dumitrascu

|||

I don't have yet a sample VB.NET code for using AMO traces, but a work-around is to have a separate assembly containing the trace code (written in C# from the sample posted previously) and use this assembly from the VB.NET script task in SSIS.

Adrian Dumitrascu

|||

You could try running the sample code through one of the online C# to VB.NET converters (eg. http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx). They are not perfect, but they can get you pretty close.

I don't know if the event handling code will work inside an SSIS script task. In this case one option might be to configure the trace to log to a file.

|||

Thanks Adrian, the tracedefinitions90.xml looks like it could be very useful.

The other thing I just found was that by going File -> Export -> Script Trace Definition. I can get an xmla script that will create the server side trace.

This might suit my needs as I can configure the trace using the profiler GUI, script it out and then just get my app to execute the xmla and then connect to the server side trace.

|||

Hi,

I have created an analysis services project using SQL Server 2005, in which I have included a cube, dimension, data source and data view. Now using the Analysis services Browser tab of cube I can see the Pivot table exactly the way I want where I can drag & drop table fields as per my requirements. But I am struggling to display the same cube over the Web. Can any please tell me that how I can publish the contents of Cube i.e. Pivot table on a web application so that end user can use this. I want the same functionality, which I can see in the Browser tab of Cube in AS2005 i.e. user should have freedom to drag & drop the fields exactly like in Excel Pivot table.

Any help would be highly appreciated.

Thanks in advance.

|||

Take a look at the following link on Mosha's website. It contains a set of links to web-based browsers and applications. You can probably use them as a guide to developing your own custom web-based app for doing this (or perhaps use one of the listed applications directly).

http://www.mosha.com/msolap/util.htm#ThinClients

HTH,

Dave Fackler