Mirror a view to a table

I would like to replicate a single view to a table that is stored on another db server (connected as linked server object).
Is there a way to imitate the behavior of a trigger (insert, delete, update) for a view?
I could assign the triggers to the table that provides the primary key.
So I could handle insert, delete events.
But what about updates that affect row in other tables that are used in this view?

Code Snippet

CREATE TRIGGER mirror_tableA_insert
ON [TESTDB].[dbo].[tableA]
FOR INSERT
AS
BEGIN
set nocount on
SET XACT_ABORT ON
set REMOTE_PROC_TRANSACTIONS off
INSERT INTO OPENQUERY(TESTLINKED, 'SELECT * FROM tableA')

SELECT *
FROM [TESTDB].[dbo].[myView] orig
INNER JOIN inserted i
ON i.prim = orig.prim

END

Thanks in advance for any hints!

Marcus

Use replication service instead of using the Trigger.

|||

It is not totally clear what you are attempting to accomplish.

Why are you using OPENQUERY instead of a LinkedServer?

And you may be better served by exploring an 'INSTEAD OF' TRIGGER.

|||

Arnie Rowland wrote:

It is not totally clear what you are attempting to accomplish.
Why are you using OPENQUERY instead of a LinkedServer?
And you may be better served by exploring an 'INSTEAD OF' TRIGGER.

I would like to replicate this view to a MySQL database that is used for a website.

Basically it's data synchronization job. So all dml statements that affect this view should trigger a procedure that synchronizes the MySQL table with the view on the SQLServer.

Yes I managed to set up a linked server for the MySQL DB through the MyODBC 3.51 driver.
First I tried to copy all the rows in this view to a MySQL table using a INSERT INTO OPENQUERY statement.

But a trigger can't be added to a view.

Manivannan.D.Sekaran wrote:

Use replication service instead of using the Trigger.

Yes the replication service would be the way to go when the target server (subscriber) would be a DB2 or Oracle database. But my target datatbase is MySQL (using it for a website).
Maybe it's possible to define this OLE DB data source as subscriber?

|||

You can't do directly, but there are some thrid party tools available. C-JDBC: Clustered JDBC is one of the tool.(http://c-jdbc.objectweb.org/)

|||

Marc Cicero wrote:

But a trigger can't be added to a view.

As I wrote earlier, you may wish to explore an INSTEAD OF TRIGGER.

An INSTEAD OF TRIGGER can work on a VIEW.

Mining structure Processing Event Notification

Hello,

Can anyone spot what i am missing here ? The problem is that i am getting a null object for e.TextData in the t_OnEvent(object sender, TraceEventArgs e) function below. I am trying to get event- notifications while processing the data mining structure.

thanks

anil

//-code below-

using Microsoft.AnalysisServices;

private void ProcessMiningStructure(MiningStructure mStruct)
{
Trace t;
TraceEvent e;

t = server.Traces.Add();
e = t.Events.Add(TraceEventClass.ProgressReportCurrent);
e.Columns.Add(TraceColumn.TextData);

t.OnEvent += new TraceEventHandler(t_OnEvent);
t.Update();
try
{
t.Start();
mStruct.Process(ProcessType.ProcessFull);
t.Stop();
}
catch (Exception ex)
{

}
t.Drop();

}
//

void t_OnEvent(object sender, TraceEventArgs e)
{
SetText(e.TextData);
}

//

Hey Anil,

Your code is almost correct, the only part missing is the column definition for event's columns. See below:

using System;

using Microsoft.AnalysisServices;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

Server srv = new Server();

srv.Connect("localhost");

Trace t = srv.Traces.Add();

TraceEvent ev = t.Events.Add(TraceEventClass.ProgressReportCurrent);

ev.Columns.Add(TraceColumn.ObjectID);

ev.Columns.Add(TraceColumn.TextData);

ev.Columns.Add(TraceColumn.EventClass);

ev.Columns.Add(TraceColumn.EventSubclass);

t.OnEvent += new TraceEventHandler(Progress_Event_Report);

t.Update();

t.Start();

srv.Databases["Adventure Works DW"].MiningStructures["Market Basket"].MiningModels["Market Basket"].Process(ProcessType.ProcessFull);

t.Stop();

t.Drop();

srv.Disconnect();

}

static void Progress_Event_Report(object sender, TraceEventArgs e_args)

{

Console.WriteLine("{0}\t{1}\t{2}\t{3}", e_args.ObjectID, e_args.EventClass, e_args.EventSubclass, e_args.TextData);

}

}

}

Hope this helps,

--

Raymond

|||

Raymond

This helped fix the issue

thanks for your response

-anil

Mining structure Processing Event Notification

Hello,

Can anyone spot what i am missing here ? The problem is that i am getting a null object for e.TextData in the t_OnEvent(object sender, TraceEventArgs e) function below. I am trying to get event- notifications while processing the data mining structure.

thanks

anil

//-code below-

using Microsoft.AnalysisServices;

private void ProcessMiningStructure(MiningStructure mStruct)
{
Trace t;
TraceEvent e;

t = server.Traces.Add();
e = t.Events.Add(TraceEventClass.ProgressReportCurrent);
e.Columns.Add(TraceColumn.TextData);

t.OnEvent += new TraceEventHandler(t_OnEvent);
t.Update();
try
{
t.Start();
mStruct.Process(ProcessType.ProcessFull);
t.Stop();
}
catch (Exception ex)
{

}
t.Drop();

}
//

void t_OnEvent(object sender, TraceEventArgs e)
{
SetText(e.TextData);
}

//

Hey Anil,

Your code is almost correct, the only part missing is the column definition for event's columns. See below:

using System;

using Microsoft.AnalysisServices;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

Server srv = new Server();

srv.Connect("localhost");

Trace t = srv.Traces.Add();

TraceEvent ev = t.Events.Add(TraceEventClass.ProgressReportCurrent);

ev.Columns.Add(TraceColumn.ObjectID);

ev.Columns.Add(TraceColumn.TextData);

ev.Columns.Add(TraceColumn.EventClass);

ev.Columns.Add(TraceColumn.EventSubclass);

t.OnEvent += new TraceEventHandler(Progress_Event_Report);

t.Update();

t.Start();

srv.Databases["Adventure Works DW"].MiningStructures["Market Basket"].MiningModels["Market Basket"].Process(ProcessType.ProcessFull);

t.Stop();

t.Drop();

srv.Disconnect();

}

static void Progress_Event_Report(object sender, TraceEventArgs e_args)

{

Console.WriteLine("{0}\t{1}\t{2}\t{3}", e_args.ObjectID, e_args.EventClass, e_args.EventSubclass, e_args.TextData);

}

}

}

Hope this helps,

--

Raymond

|||

Raymond

This helped fix the issue

thanks for your response

-anil

Mining structure Processing Event Notification

Hello,

Can anyone spot what i am missing here ? The problem is that i am getting a null object for e.TextData in the t_OnEvent(object sender, TraceEventArgs e) function below. I am trying to get event- notifications while processing the data mining structure.

thanks

anil

//-code below-

using Microsoft.AnalysisServices;

private void ProcessMiningStructure(MiningStructure mStruct)
{
Trace t;
TraceEvent e;

t = server.Traces.Add();
e = t.Events.Add(TraceEventClass.ProgressReportCurrent);
e.Columns.Add(TraceColumn.TextData);

t.OnEvent += new TraceEventHandler(t_OnEvent);
t.Update();
try
{
t.Start();
mStruct.Process(ProcessType.ProcessFull);
t.Stop();
}
catch (Exception ex)
{

}
t.Drop();

}
//

void t_OnEvent(object sender, TraceEventArgs e)
{
SetText(e.TextData);
}

//

Hey Anil,

Your code is almost correct, the only part missing is the column definition for event's columns. See below:

using System;

using Microsoft.AnalysisServices;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

Server srv = new Server();

srv.Connect("localhost");

Trace t = srv.Traces.Add();

TraceEvent ev = t.Events.Add(TraceEventClass.ProgressReportCurrent);

ev.Columns.Add(TraceColumn.ObjectID);

ev.Columns.Add(TraceColumn.TextData);

ev.Columns.Add(TraceColumn.EventClass);

ev.Columns.Add(TraceColumn.EventSubclass);

t.OnEvent += new TraceEventHandler(Progress_Event_Report);

t.Update();

t.Start();

srv.Databases["Adventure Works DW"].MiningStructures["Market Basket"].MiningModels["Market Basket"].Process(ProcessType.ProcessFull);

t.Stop();

t.Drop();

srv.Disconnect();

}

static void Progress_Event_Report(object sender, TraceEventArgs e_args)

{

Console.WriteLine("{0}\t{1}\t{2}\t{3}", e_args.ObjectID, e_args.EventClass, e_args.EventSubclass, e_args.TextData);

}

}

}

Hope this helps,

--

Raymond

|||

Raymond

This helped fix the issue

thanks for your response

-anil

Migration Wizard Error

Hi !,

The migration of OLAP cubes with aggregations to SSAS, generate the following error:

"Another 'Aggregation' object has the '31 3314' ID."

and then the migration process stops.

The dimension structure is changed during the process and a posible solution is deleting all the aggregation before migration.

But I would like to know if it is really a bug or there is a known reason for getting such error.

Thanks in advance !!!

Leandro

Looks like you somewhow got 2 aggregations in AS2000 with the same name.

Now, when trying to migrate AS2005 trying to create aggregations for you and stops at the point when meets aggregation with the duplicate name.

Several options.

Try and get rid of the duplicate aggregation using Parition Manager sample application shipped with SQL Server 2000 resource kit.

Try re-desingn aggregations using Analysis Manager.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

That's right.

So Instead of migrating directly from a production server, would it be better to copy AS2000 objects to another database/server, clean all duplicate aggregations, and then run the migration wizard ?.

We have high volume partitions, but fortunately users dont browse freely the cubes. An application runs a lot of specific MDX queries. So we decided to design aggregations manually because generating an aditional one was too costly and it was better to define a lower-level aggregation that serves two similar types of MDX queries, than adding two aggregations by using the Usage Based Optimization.

I dont know If the last algorithm was optimized to support such type of aggregations design criteria.

Thanks very much.

Leandro

Thanks Edward,

|||

I would definitely try make sure that any modifications are done in the test environment.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights