October 28, 2011 at 3:07 am
Hi Team,
Using below script Loading data into a table but 1st time data was loading perfectly when i will execute this script, but right now
The File size was 4000 KB the dynamically data was not loading into the table is there any correction please correct me.
CREATE PROCEDURE start_trace @tracefile nvarchar(50) AS
DECLARE @TraceID int
declare @rc int
declare @stoptime datetime
declare @maxfilesize bigint
select @maxfilesize = 5
-- Strip any .trc from the file nmae.
if right(lower(@tracefile), 4) = '.trc'
select @tracefile = substring(@tracefile, 1, len(@tracefile) - 4)
-- Start the trace
exec @rc = sp_trace_create @TraceID output,
@options = 2,
@tracefile = @tracefile,
@maxfilesize = @maxfilesize,
@stoptime = NULL,
@filecount=2;
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 162,3,@on
exec sp_trace_setevent @TraceID, 22,3, @on
exec sp_trace_setevent @TraceID, 33,3, @on
exec sp_trace_setstatus @TraceID, 1
select @TraceID as TraceID, @rc as RC
go
CREATE PROCEDURE stop_trace @tracefile nvarchar(50) AS
DECLARE @TraceID int
-- Populate a variable with the trace_id of the current trace
SELECT @TraceID = TraceID FROM fn_trace_getinfo(DEFAULT) WHERE Value = @tracefile;
-- Stop and close the trace.
IF @TraceID IS NOT NULL
BEGIN
EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2
END
ELSE
BEGIN
RAISERROR('No such tracefile %s', 16, 1, @tracefile)
RETURN 1
END
BEGIN TRANSACTION;
BEGIN TRY
-- Attempt to load the trace file.
IF OBJECT_ID(N'dbo.xxx ') IS NOT NULL
DROP TABLE dbo.xxx;
SELECT * INTO xxx--Load into a new table
FROM fn_trace_gettable(@tracefile, DEFAULT);
SELECT * FROM xxx
END TRY
BEGIN CATCH
-- Report any issues that may have arised from the attempt
SELECT
ApplicationName, ServerName,SPID,LoginName,StartTime from xxx
-- Kick back the transaction if there was a problem
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
October 28, 2011 at 3:15 am
Hi Team,
I m planning to put these stored procedures in a job
in the job STEP 1: 1st sp
STEP 2 : 2nd SP
Then i will schedule this jobs certaine time
When ever job execute the trace file data will loading into the table right
any thing wrong please correct me.
Tx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply