Hi,

I need to know wich SQL stetement that fires my trigger using fn_get_sql.
When I use this function now, it gives me the SQL statement of my Trigger!
not the Sql Statement that fired the trigger!

Does anybody knows a solution for this?


The Trigger looks like this now:

CREATE TRIGGER Trigger_Transactions_tblRules_FN
ON tblRules
FOR INSERT, UPDATE, DELETE AS
BEGIN
--Clean up the display
SET NOCOUNT ON
DBCC TRACEON (2861)
--Declare variable to hold SPID handle
DECLARE @SPIDHandle BINARY(20), @start INT, @end INT

--Obtain SPID handle
SELECT @SPIDHandle = sql_handle, @start = stmt_start, @end = stmt_end
FROM master.dbo.sysprocesses WHERE spid = @@SPID

--See statement text
INSERT INTO tblFN
SELECT *, @start as s_start, @end AS s_end FROM ::fn_get_sql(@SPIDHandle)

DBCC TRACEOFF (2861)
END


-> So the problem is in my opinion: I use the @@SPID of the Trigger, not the
@@SPID of the SQL Statement that fired the Trigger...

Thanks a lot in advance,

Pieter

[ Post a follow-up to this message ]

Hi
Use Vyas example to show how to use it
IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ShowCodeLine'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'PROCEDURE'
)
BEGIN
PRINT 'Procedure ShowCodeLine already exists...Dropping it and recreating'
DROP PROC dbo.ShowCodeLine
END
ELSE
BEGIN
PRINT 'Creating procedure ShowCodeLine'
END
GO

CREATE PROC dbo.ShowCodeLine
(
@SPID smallint,
@WAIT tinyint = 0,
@NoLoop bit = 0
)
AS
BEGIN
--Author: Narayana Vyas Kondreddi
--Date Created: 20031016
--Source: http://vyaskn.tripod.com

SET NOCOUNT ON

DECLARE @sql_handle binary(20), @handle_found bit
DECLARE @stmt_start int, @stmt_end int
DECLARE @line nvarchar(4000), @wait_str varchar(8)

SET @handle_found = 0

IF @WAIT NOT BETWEEN 0 AND 60
BEGIN
RAISERROR('Valid values for @WAIT are from 0 to 60 seconds', 16, 1)
RETURN -1
END
ELSE
BEGIN
SET @wait_str = '00:00:' + RIGHT('00' + CAST(@WAIT AS varchar(2)), 2)
END

WHILE 1 = 1
BEGIN
SELECT @sql_handle = sql_handle,
@stmt_start = stmt_start/2,
@stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID
AND ecid = 0

IF @sql_handle = 0x0
BEGIN
IF @handle_found = 0
BEGIN
RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)
RETURN -1
END
ELSE
BEGIN
RAISERROR('Query/Stored procedure completed', 0, 1)
RETURN 0
END
END
ELSE
BEGIN
SET @handle_found = 1
END

SET @line =
(
SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end - @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle)
)

RAISERROR(@line, 0, 1) WITH NOWAIT

IF @NoLoop = 1
BEGIN
RETURN 0
END

WAITFOR DELAY @wait_str

END

END
GO



Now, to examine a process with spid 55, run the following:

EXEC dbo.ShowCodeLine 55
GO

To examine a process with spid 55, but every 2 seconds (instead of the
default 0 seconds):

EXEC dbo.ShowCodeLine 55, 2
GO




"DraguVaso" <pietercoucke@hotmail.com> wrote in message
news:ewfA6ZmSFHA.3156@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> I need to know wich SQL stetement that fires my trigger using fn_get_sql.
> When I use this function now, it gives me the SQL statement of my Trigger!
> not the Sql Statement that fired the trigger!
>
> Does anybody knows a solution for this?
>
>
> The Trigger looks like this now:
>
> CREATE TRIGGER Trigger_Transactions_tblRules_FN
> ON tblRules
> FOR INSERT, UPDATE, DELETE AS
> BEGIN
>  --Clean up the display
> SET NOCOUNT ON
> DBCC TRACEON (2861)
> --Declare variable to hold SPID handle
> DECLARE @SPIDHandle BINARY(20), @start INT, @end INT
>
> --Obtain SPID handle
> SELECT @SPIDHandle = sql_handle, @start = stmt_start, @end = stmt_end
> FROM master.dbo.sysprocesses WHERE spid = @@SPID
>
> --See statement text
> INSERT INTO tblFN
> SELECT *, @start as s_start, @end AS s_end FROM ::fn_get_sql(@SPIDHandle)
>
> DBCC TRACEOFF (2861)
> END
>
>
> -> So the problem is in my opinion: I use the @@SPID of the Trigger, not
the
> @@SPID of the SQL Statement that fired the Trigger...
>
> Thanks a lot in advance,
>
> Pieter
>
>
>
>

[ Post a follow-up to this message ]

Ok thanks, but I had found this alreaddy on the net.
The problem is that I'm not able to integrate it in a Trigger to find the
statement that fired the trigge!r
I can get the SQL of the trigger itself with it, but not the SQL Query that
the user ran on the table, and that fired the trigger...

Anybody has any idea for my problem?

Thanks,

Pieter

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:Od1ejgmSFHA.3972@TK2MSFTNGP14.phx.gbl...
> Hi
> Use Vyas example to show how to use it
> IF EXISTS
> (
>  SELECT 1
>  FROM INFORMATION_SCHEMA.ROUTINES
>  WHERE ROUTINE_NAME = 'ShowCodeLine'
>  AND ROUTINE_SCHEMA = 'dbo'
>  AND ROUTINE_TYPE = 'PROCEDURE'
> )
> BEGIN
>  PRINT 'Procedure ShowCodeLine already exists...Dropping it and
recreating'
>  DROP PROC dbo.ShowCodeLine
> END
> ELSE
> BEGIN
>  PRINT 'Creating procedure ShowCodeLine'
> END
> GO
>
> CREATE PROC dbo.ShowCodeLine
> (
>  @SPID smallint,
>  @WAIT tinyint = 0,
>  @NoLoop bit = 0
> )
> AS
> BEGIN
>  --Author: Narayana Vyas Kondreddi
>  --Date Created: 20031016
>  --Source: http://vyaskn.tripod.com
>
>  SET NOCOUNT ON
>
>  DECLARE @sql_handle binary(20), @handle_found bit
>  DECLARE @stmt_start int, @stmt_end int
>  DECLARE @line nvarchar(4000), @wait_str varchar(8)
>
>  SET @handle_found = 0
>
>  IF @WAIT NOT BETWEEN 0 AND 60
>  BEGIN
>   RAISERROR('Valid values for @WAIT are from 0 to 60 seconds', 16, 1)
>   RETURN -1
>  END
>  ELSE
>  BEGIN
>   SET @wait_str = '00:00:' + RIGHT('00' + CAST(@WAIT AS varchar(2)), 2)
>  END
>
>  WHILE 1 = 1
>  BEGIN
>   SELECT @sql_handle = sql_handle,
>    @stmt_start = stmt_start/2,
>    @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
>    FROM master.dbo.sysprocesses
>    WHERE spid = @SPID
>     AND ecid = 0
>
>   IF @sql_handle = 0x0
>   BEGIN
>    IF @handle_found = 0
>    BEGIN
>     RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)
>     RETURN -1
>    END
>    ELSE
>    BEGIN
>     RAISERROR('Query/Stored procedure completed', 0, 1)
>     RETURN 0
>    END
>   END
>   ELSE
>   BEGIN
>    SET @handle_found = 1
>   END
>
>   SET @line =
>   (
>    SELECT
>     SUBSTRING( text,
>       COALESCE(NULLIF(@stmt_start, 0), 1),
>       CASE @stmt_end
>        WHEN -1
>         THEN DATALENGTH(text)
>        ELSE
>         (@stmt_end - @stmt_start)
>           END
>      )
>       FROM ::fn_get_sql(@sql_handle)
>     )
>
>   RAISERROR(@line, 0, 1) WITH NOWAIT
>
>   IF @NoLoop = 1
>   BEGIN
>    RETURN 0
>   END
>
>   WAITFOR DELAY @wait_str
>
>  END
>
> END
> GO
>
>
>
> Now, to examine a process with spid 55, run the following:
>
> EXEC dbo.ShowCodeLine 55
> GO
>
> To examine a process with spid 55, but every 2 seconds (instead of the
> default 0 seconds):
>
> EXEC dbo.ShowCodeLine 55, 2
> GO
>
>
>
>
> "DraguVaso" <pietercoucke@hotmail.com> wrote in message
> news:ewfA6ZmSFHA.3156@TK2MSFTNGP15.phx.gbl... 
fn_get_sql.[vbcol=seagreen] 
Trigger![vbcol=seagreen] 
 ::fn_get_sql(@SPIDHandle)[vbcol=seagreen
] 
> the 
>
>

[ Post a follow-up to this message ]

See if this helps:
http://vyaskn.tripod.com/tracking_s...by_triggers.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


"DraguVaso" <pietercoucke@hotmail.com> wrote in message
news:OgWRypmSFHA.2964@TK2MSFTNGP15.phx.gbl...
Ok thanks, but I had found this alreaddy on the net.
The problem is that I'm not able to integrate it in a Trigger to find the
statement that fired the trigge!r
I can get the SQL of the trigger itself with it, but not the SQL Query that
the user ran on the table, and that fired the trigger...

Anybody has any idea for my problem?

Thanks,

Pieter

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:Od1ejgmSFHA.3972@TK2MSFTNGP14.phx.gbl...
> Hi
> Use Vyas example to show how to use it
> IF EXISTS
> (
>  SELECT 1
>  FROM INFORMATION_SCHEMA.ROUTINES
>  WHERE ROUTINE_NAME = 'ShowCodeLine'
>  AND ROUTINE_SCHEMA = 'dbo'
>  AND ROUTINE_TYPE = 'PROCEDURE'
> )
> BEGIN
>  PRINT 'Procedure ShowCodeLine already exists...Dropping it and
recreating'
>  DROP PROC dbo.ShowCodeLine
> END
> ELSE
> BEGIN
>  PRINT 'Creating procedure ShowCodeLine'
> END
> GO
>
> CREATE PROC dbo.ShowCodeLine
> (
>  @SPID smallint,
>  @WAIT tinyint = 0,
>  @NoLoop bit = 0
> )
> AS
> BEGIN
>  --Author: Narayana Vyas Kondreddi
>  --Date Created: 20031016
>  --Source: http://vyaskn.tripod.com
>
>  SET NOCOUNT ON
>
>  DECLARE @sql_handle binary(20), @handle_found bit
>  DECLARE @stmt_start int, @stmt_end int
>  DECLARE @line nvarchar(4000), @wait_str varchar(8)
>
>  SET @handle_found = 0
>
>  IF @WAIT NOT BETWEEN 0 AND 60
>  BEGIN
>   RAISERROR('Valid values for @WAIT are from 0 to 60 seconds', 16, 1)
>   RETURN -1
>  END
>  ELSE
>  BEGIN
>   SET @wait_str = '00:00:' + RIGHT('00' + CAST(@WAIT AS varchar(2)), 2)
>  END
>
>  WHILE 1 = 1
>  BEGIN
>   SELECT @sql_handle = sql_handle,
>    @stmt_start = stmt_start/2,
>    @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
>    FROM master.dbo.sysprocesses
>    WHERE spid = @SPID
>     AND ecid = 0
>
>   IF @sql_handle = 0x0
>   BEGIN
>    IF @handle_found = 0
>    BEGIN
>     RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)
>     RETURN -1
>    END
>    ELSE
>    BEGIN
>     RAISERROR('Query/Stored procedure completed', 0, 1)
>     RETURN 0
>    END
>   END
>   ELSE
>   BEGIN
>    SET @handle_found = 1
>   END
>
>   SET @line =
>   (
>    SELECT
>     SUBSTRING( text,
>       COALESCE(NULLIF(@stmt_start, 0), 1),
>       CASE @stmt_end
>        WHEN -1
>         THEN DATALENGTH(text)
>        ELSE
>         (@stmt_end - @stmt_start)
>           END
>      )
>       FROM ::fn_get_sql(@sql_handle)
>     )
>
>   RAISERROR(@line, 0, 1) WITH NOWAIT
>
>   IF @NoLoop = 1
>   BEGIN
>    RETURN 0
>   END
>
>   WAITFOR DELAY @wait_str
>
>  END
>
> END
> GO
>
>
>
> Now, to examine a process with spid 55, run the following:
>
> EXEC dbo.ShowCodeLine 55
> GO
>
> To examine a process with spid 55, but every 2 seconds (instead of the
> default 0 seconds):
>
> EXEC dbo.ShowCodeLine 55, 2
> GO
>
>
>
>
> "DraguVaso" <pietercoucke@hotmail.com> wrote in message
> news:ewfA6ZmSFHA.3156@TK2MSFTNGP15.phx.gbl... 
fn_get_sql.[vbcol=seagreen] 
Trigger![vbcol=seagreen] 
 ::fn_get_sql(@SPIDHandle)[vbcol=seagreen
] 
> the 
>
>

[ Post a follow-up to this message ]