SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE  PROCEDURE dbo.dba_Trc_Record

    @FileName   nvarchar(256) -- File to load
  , @StartDT    datetime
  , @Sec        int      = 600  -- Seconds of duration
  , @Resp       nvarchar(255) -- Who is responsible
  , @Desc       nvarchar(2000)-- Description
  , @SQLtraceID int -- SQL Server's ID of the Trace
  , @dbaTraceID int OUTPUT -- Id of dbaTrace use
AS
/*
* Creates a row in dbaTrace row to record a trace file.  The
* trace file can be loaded later.
*
* Example:
DECLARE @TraceID int, @RC int, @StartDT datetime
SET @StartDT = GETDATE()
EXEC @RC = dbo.dba_Trc_Record 'c:\ExampleTrace.trc', @StartDT
          , 600, 'Andy', 'Example Trace', 1, @TraceID OUTPUT
PRINT 'RC = '+ CONVERT (varchar(9), @RC)
     + ' New ID =' + CONVERT(varchar(9), @TraceID)
****************************************************************/

DECLARE @myError         int        -- Local copy of @@Error
      , @myRowcount      int     -- Local copy of @@ROWCOUNT

SET NOCOUNT ON -- Don't report the count of effected rows

INSERT INTO dbaTrace (TraceID, StartDT, EndDT, [FileName]
                    , Responsible, [Description])
       VALUES (@SQLtraceID, @StartDT
             , DATEADD (s, @Sec, @StartDT)
             , @FileName, @Resp, @Desc)

SELECT @myError = @@Error, @myRowcount = @@Rowcount
IF @myError <> 0 or @myRowCount <> 1 BEGIN
   RETURN @myError
END

SELECT @dbaTraceID = SCOPE_IDENTITY()

RETURN 0