Sql-server – Replay trace file and filter on error>0

From the POWER documentation:

Syntax

POWER ( float_expression , y )

Arguments

float_expression
Is an expression of type float or of a type that can be implicitly converted to float.

y
Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).

The first input is implicitly cast to float if necessary.

The internal calculation is performed using float arithmetic by the standard C Runtime Library (CRT) function pow.

The float output from pow is then cast back to the type of the left hand operand (implied to be numeric(3,1) when you use the literal value 10.0).

Using an explicit float works fine in your case:

SELECT POWER(1e1, 38);
SELECT POWER(CAST(10 as float), 38.0);

An exact result for 10³⁸ cannot be stored in a SQL Server decimal/numeric because it would require 39 digits of precision (1 followed by 38 zeros). The maximum precision is 38.

Does that mean the server-side trace is always only checking the database it's been creating in?

No, the server-side trace has the scope of just that: the server. When I create (what I think is) the exact trace definition and export it, I get the following snippet:

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

I am using SQL Server Profiler 2008 R2 as well. If you do not filter on the database name, then you will be capturing trace events for all of the instance databases. As to why your export didn't seem to include that portion of the filter, I can't say for sure. I know this goes without saying, but I'd check your trace definition within Profiler before you export to ensure the database name is in fact included as a filter (again, I know you've probably double and tripple checked this, but I had to say it).