MS SQL Server - Traces, tables and triggers

Author

Traces, tables and triggers

Adrian

2006-12-12, 7:12 pm

Hello

We intend to migrate from SQL Server 2000 to SQL Server 2005. However, there
is one point that causes big troubles - if I do not solve it, then there
will be no migration. Any help is appreciated.

Here it comes. The following works fine on SQL Server 2000:

First I start the profiler and setup a trace that listens to the login-event
and that writes its data into a table T. Unfortunately, traces that write
data into a table cannot be scripted, so I cannot present any scripts. The
trace must be started by hand each time the database server is started. Then
I create a trigger "for insert" on the table T, which does some essential
work. (Please, no questions about the meaning of this mechanism - it is
really essential.)

The problem is that the same procedure does not work with SQL Server 2005.

I can start the profiler and setup the same trace. I can also create the
trigger on the table T. However, upon the next login-event the trace stops
and the profiler returns the error message "Failed to save trace data to
table".

What is wrong now?

Best regards

Adrian

Uri Dimant

2006-12-12, 7:12 pm

Adrian
What does the trigger do?

Have you tried ?

insert into T (......)select ...... from
fn_trace_gettable ( 'c:\mytrace.trc' , 1)
where .....

"Adrian" <adrian@iai.uni-bonn.de> wrote in message
news:e4QhkbeHHHA.3676@TK2MSFTNGP03.phx.gbl...
> Hello
>
> We intend to migrate from SQL Server 2000 to SQL Server 2005. However,
> there is one point that causes big troubles - if I do not solve it, then
> there will be no migration. Any help is appreciated.
>
> Here it comes. The following works fine on SQL Server 2000:
>
> First I start the profiler and setup a trace that listens to the
> login-event and that writes its data into a table T. Unfortunately, traces
> that write data into a table cannot be scripted, so I cannot present any
> scripts. The trace must be started by hand each time the database server
> is started. Then I create a trigger "for insert" on the table T, which
> does some essential work. (Please, no questions about the meaning of this
> mechanism - it is really essential.)
>
> The problem is that the same procedure does not work with SQL Server 2005.
>
> I can start the profiler and setup the same trace. I can also create the
> trigger on the table T. However, upon the next login-event the trace stops
> and the profiler returns the error message "Failed to save trace data to
> table".
>
> What is wrong now?
>
> Best regards
>
> Adrian
>

Adrian

2006-12-12, 7:12 pm

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:eYqcWfeHHHA.3540@TK2MSFTNGP02.phx.gbl...
> Adrian
Hello Uri

> What does the trigger do?
It reacts to login-events with some security processing.

>
> Have you tried ?
>
> insert into T (......)select ...... from
> fn_trace_gettable ( 'c:\mytrace.trc' , 1)
> where .....
Yes, I have. But it is of no avail, since we must react to the login-event
in real-time

Adrian

>
> "Adrian" <adrian@iai.uni-bonn.de> wrote in message
> news:e4QhkbeHHHA.3676@TK2MSFTNGP03.phx.gbl...
>
>

Uri Dimant

2006-12-12, 7:12 pm

Adrian

So you are saying that it happens only in real time, SQL Server Profiler
throws the error, am I right?

"Adrian" <adrian@iai.uni-bonn.de> wrote in message
news:%23hyt1zeHHHA.2232@TK2MSFTNGP02.phx.gbl...
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:eYqcWfeHHHA.3540@TK2MSFTNGP02.phx.gbl...
> Hello Uri
>
> It reacts to login-events with some security processing.
>
> Yes, I have. But it is of no avail, since we must react to the login-event
> in real-time
>
> Adrian
>
>
>

Razvan Socol

2006-12-12, 7:12 pm

Hello, Adrian

If you are planning to use SQL Server 2005 SP2 (which should be
released in a few days/weeks; now is in the CTP stage), you can use a
much more reliable way to do all this: logon triggers. Logon triggers
are a new type of DDL trigger, introduced in SQL 2005 SP2. For more
information, see Books Online SP2 CTP, available from:
http://www.microsoft.com/downloads/...&displaylang=en

Razvan

Adrian

2006-12-12, 7:12 pm

Hello Razvan

This is great news. I will have a look immediately.

Thank you very much for this information.

Best regards

Adrian

"Razvan Socol" <rsocol@gmail.com> wrote in message
news:1165929902.140170.8820@f1g2000cwa.googlegroups.com...
> Hello, Adrian
>
> If you are planning to use SQL Server 2005 SP2 (which should be
> released in a few days/weeks; now is in the CTP stage), you can use a
> much more reliable way to do all this: logon triggers. Logon triggers
> are a new type of DDL trigger, introduced in SQL 2005 SP2. For more
> information, see Books Online SP2 CTP, available from:
> http://www.microsoft.com/downloads/...&displaylang=en
>
> Razvan
>

Sponsored Links