Why is SQL Profiler showing empty for database name and database id?

Question

I have some code that uses Entity Framework to call a stored procedure in SQL Server. If I run SQL Profiler and filter by the database it is calling, I can see that the profiler correctly shows the call.

However, the database name and database id column are always empty. Anyone know why it is not showing this information?

I'm using this version:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Answer 1

Confirm the trace is actually gathering those columns. The following query will show the events and columns being captured for each trace (except the default system trace):

SELECT TraceID = t.id
 , TracePath = t.path
 , EventName = te.name
 , ColumnName = tc.name
FROM sys.traces t
 CROSS APPLY sys.fn_trace_geteventinfo(t.id) ei
 LEFT JOIN sys.trace_events te ON ei.eventid = te.trace_event_id
 LEFT JOIN sys.trace_columns tc ON ei.columnid = tc.trace_column_id
WHERE t.id > 1 /* exlude the system trace */
ORDER BY t.id
 , ei.columnid;

The results look something like this (depending on the events and columns you've defined for your trace):

╔═════════╦═════════════════╦═════════════╦═════════════════╦═════════╦══════════╗
║ TraceID ║ TracePath ║ EventName ║ ColumnName ║ eventid ║ columnid ║
╠═════════╬═════════════════╬═════════════╬═════════════════╬═════════╬══════════╣
║ 2 ║ C:\temp\trc.trc ║ CursorClose ║ ApplicationName ║ 78 ║ 10 ║
║ 2 ║ C:\temp\trc.trc ║ CursorClose ║ SPID ║ 78 ║ 12 ║
║ 2 ║ C:\temp\trc.trc ║ CursorClose ║ EventSequence ║ 78 ║ 51 ║
╚═════════╩═════════════════╩═════════════╩═════════════════╩═════════╩══════════╝

Answer 2

After using the query from Hannah’s answer for troubleshooting, I came back to it when thinking about documenting how a trace template was configured, since a screenshot can’t show all columns (and the window size is fixed). So, then I thought I would try to make it mimic the appearance of the UI.

Of course, the script is overkill, but here’s what I came up with (showing the "Standard" trace template): sql trace script output

Compared to the UI: trace properties window

I couldn’t determine the order of the columns used by the UI, and I’m not sure if it’s possible to know if a column is available but unchecked.

DECLARE @tid AS INT
SELECT @tid=MAX(id) FROM sys.traces
DECLARE @ColumnID INT, @ColumnName NVARCHAR(100);
DECLARE @col AS NVARCHAR(MAX) = N''
DECLARE @blanks AS NVARCHAR(MAX) = N''
DECLARE @names AS NVARCHAR(MAX) = N''
 
DECLARE cursor_results CURSOR FOR
SELECT DISTINCT ei.columnid, tc.name
FROM sys.traces t
 CROSS APPLY sys.fn_trace_geteventinfo(t.id) ei
 LEFT JOIN sys.trace_columns tc ON ei.columnid = tc.trace_column_id
WHERE t.id = @tid
ORDER BY ei.columnid
 
OPEN cursor_results
FETCH NEXT FROM cursor_results into @ColumnID, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN 
 SET @col=@col + N', ISNULL(MAX(CASE WHEN ei.columnid=' + CAST(@ColumnID AS NVARCHAR) 
 + N' THEN N''✅'' END),'''') AS [' + @ColumnName + N']'
 SET @blanks=@blanks + N', '''' AS [' + @ColumnName + N']'
 SET @names=@names + N', [' + @ColumnName + N']'
FETCH NEXT FROM cursor_results into @ColumnID, @ColumnName
END
CLOSE cursor_results;
DEALLOCATE cursor_results;
DECLARE @sql AS NVARCHAR(4000)
SET @sql =
 N'
SELECT Decoration AS '' '', EventName AS Events' + @names + N'
FROM (
 SELECT N'' ✅'' AS Decoration, tcat.name Category, N'' '' + te.name EventName' + @col + N'
 FROM sys.traces t
 CROSS APPLY sys.fn_trace_geteventinfo(t.id) ei
 LEFT JOIN sys.trace_events te ON ei.eventid = te.trace_event_id
 LEFT JOIN sys.trace_categories tcat ON te.category_id=tcat.category_id
 WHERE t.id = ' + CAST(@tid AS NVARCHAR) + N'
 GROUP BY tcat.name, te.name
 UNION ALL
 SELECT DISTINCT N''⊟'' AS Decoration, tcat.name + '' '', tcat.name' + @blanks + N'
 FROM sys.traces t
 CROSS APPLY sys.fn_trace_geteventinfo(t.id) ei
 LEFT JOIN sys.trace_events te ON ei.eventid = te.trace_event_id
 LEFT JOIN sys.trace_categories tcat ON te.category_id=tcat.category_id
 WHERE t.id = ' + CAST(@tid AS NVARCHAR) + N'
) AS x
ORDER BY Category, CASE WHEN Category=EventName THEN 1 ELSE 0 END DESC';
EXEC sp_executesql @sql;
SELECT tc.name [Column], CASE WHEN f.logical_operator=0 THEN 'AND' ELSE 'OR' END AS Operator,
CASE WHEN f.comparison_operator=0 THEN '='
WHEN f.comparison_operator=1 THEN '<>'
WHEN f.comparison_operator=2 THEN '>'
WHEN f.comparison_operator=3 THEN '<'
WHEN f.comparison_operator=4 THEN '>='
WHEN f.comparison_operator=5 THEN '<='
WHEN f.comparison_operator=6 THEN 'LIKE'
WHEN f.comparison_operator=7 THEN 'NOT LIKE'
END AS Comparison, f.value
FROM fn_trace_getfilterinfo(@tid) f
LEFT JOIN sys.trace_columns tc ON f.columnid = tc.trace_column_id ;