rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero after
a index rebuild in sql 2000 ?
Thanks,
Ranga
Ranga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:

> why rowmodctr in sysindexes table shows negative values instead of zero after
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga
|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know what
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For the
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero afte
r
a index rebuild in sql 2000 ?
Thanks,
RangaRanga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:

> why rowmodctr in sysindexes table shows negative values instead of zero af
ter
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know wha
t
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For t
he
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:
>sql

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero after
a index rebuild in sql 2000 ?
Thanks,
RangaRanga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:
> why rowmodctr in sysindexes table shows negative values instead of zero after
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know what
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For the
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:
> > why rowmodctr in sysindexes table shows negative values instead of zero after
> > a index rebuild in sql 2000 ?
> >
> > Thanks,
> > Ranga

Row Not Found Err w/ zero details

SQL 2005 Transactional Repl. Receiving row not found error. In SQL2000 you
could view the error details and see the exact sp call sp_MSUpdxxx command
that was trying to execute w/ param values.
Now in 2005 - this is the most detail I get below: How do I see what row
was missing? tia Chris
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x0019780A0001C586015500000000, Command ID: 1)
Error messages:
The row was not found at the Subscriber when applying the replicated
command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
The row was not found at the Subscriber when applying the replicated
command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
Chris - you should be able to use sp_briowsereplcmds which takes an optional
parameter of @.xact_seqno_start (and @.xact_seqno_end)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||sp_browseprelcmds will give all commands to be replicated. I have many pubs
on that server along w/ other servers. Was this detail removed from repl
monitor?
"Paul Ibison" wrote:

> Chris - you should be able to use sp_briowsereplcmds which takes an optional
> parameter of @.xact_seqno_start (and @.xact_seqno_end)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Chris,
I was recommending using the optional parameters @.xact_seqno_start and
@.xact_seqno_end to filter the results.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Rounding error when summing 4 numbers to 0

When I sum the 4 numbers (-550.83, 1690.65, 550.83, -1690.65), it should add up to zero. It displays correctly to 2 decimal places. I know that they are stored internally as a double. However, I use the number as a denominator in a calculated member and the test for zero fails. I have to use bounds such as > -00000001 and <0.00000001. The result is a very large number in billions if the filter is not used in the calculation.

Has anyone come across this or got a better suggestion? I can also use the VBA round function.

Thanks

If these are always going to be decimal numbers, as in the example above, does the changing the data type to "Currency" help?

http://msdn2.microsoft.com/es-es/library/ms129408.aspx

>>

SQL Server 2005 Books Online

DataType Element (ASSL)

Defines the data type of the associated element.

...

The values for DataType are defined in the System.Data.OleDb.OleDbType enumeration. However, only the enumeration values in the following table are valid in the DataType element.

Value	Description
BigInt	A 64-bit signed integer. This data type maps to the Int64 data type in Microsoft .NET Framework and the DBTYPE_I8 data type in OLE DB.
Bool	A Boolean value. This data type maps to the Boolean data type in the .NET Framework and the DBTYPE_BOOL data type in OLE DB.
Currency	A currency value ranging from -263 (or -922,337,203,685,477.5808) to 263-1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit. This data type maps to the Decimal data type in the .NET Framework and the DBTYPE_CY data type in OLE DB.

...

>>

|||

Thanks for the info and a possible solution. I have to bring it in as a double.

The source fact table is in account dimension format with only one measure DECIMAL (24,12). The measure can be pounds shipped, gross sales, or some marketing dollar allocation to 12 places. If I used currency, I would lose significant precision due to rounding to 4 decimal places.

I think the structure of my client's source fact table is already determining how I manage this. I wish SSAS would allow you to set the source format such as DECIMAL (24,12). I have the calculated measure isolated in one area, but it may impact performance when the client drills down hierarchies. Here is the code I used for the calculated measure.

IIF(([Measures].[EXPENSE DTL AMT], [WATERFALL DIM].[Waterfall].&[1]) > -0.000000001 AND
([Measures].[EXPENSE DTL AMT], [WATERFALL DIM].[Waterfall].&[1]) < 0.000000001,
0, ([Measures].[EXPENSE DTL AMT], [WATERFALL DIM].[Waterfall].&[1]))

Round producing no value

why is this producing nothing when zero?

=Round(((Fields!FeeGoal_IL.Value) / IIf(Fields!FeeSchedule.Value = 0, 1, Fields!FeeSchedule.Value)) * 100)

actually ever since I changed my format to #,### I think that's the problem

figured it out. It is not obvious in BOL (at least what I read on the lousy BOL for SSRS 2005 - lack of thorough content in SSRS 2005 BOL pages) that you can combine N and 0 so N0 did the trick for the format to format numbers with a comma less decimals (e.g. 1,200) and still render 0 for zero values