Lowell (3/3/2011)
ok, still not an issue; read the file from the database, and recreate the view as needed:
--we want the current trace folder
declare @path nvarchar(256)
--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1
--you can select either the real default trace, or your specific trace.
SET @path = @path + N'LookitMyTrace' --system appends .trc automatically for the filename
IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name='LookitMyTrace ')
BEGIN
SET @sql = 'ALTER VIEW LookitMyTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path +'.trc'', default)'
exec(@sql)
END
ELSE
BEGIN
SET @sql = 'CREATE VIEW LookitMyTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'
exec(@sql)
END
COMMIT TRAN
END
Whoa! Interesting idea! I will try that.
Thanks!