Any way to track who/when data was deleted ?

Hello,
I'm using merge replication with SQL Server 2000 on Windows 2003 server,
with MSDE2000A clients with anon pull subscriptions.
Some data has been (mysteriously) deleted and I would like to find out more
about who/when it was deleted.
Is there any way (system tables, transaction log ?) of determining who/when
certain records were deleted ?
I noticed that in my publication DB the MSmerge_tombstone table has a field
that shows either "user delete" or "system delete" - what is a System
Delete?
Thanks for your help.
Darren
No, you have to write custom triggers to track this information.
What you will get is the user that makes this change on the local server,
and then the account your merge agent runs under. So if you make a change on
your publisher, there is no way to have this user name transferred to the
subcriber, unless you also replicated an audit table.
User delete is when a user deletes a record, system delete is when a system
process (normally in response to a conflict) deletes the record.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Darren Wallace" <darren@.pcresources.com.au> wrote in message
news:eQdrh$FmEHA.3524@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm using merge replication with SQL Server 2000 on Windows 2003 server,
> with MSDE2000A clients with anon pull subscriptions.
> Some data has been (mysteriously) deleted and I would like to find out
more
> about who/when it was deleted.
> Is there any way (system tables, transaction log ?) of determining
who/when
> certain records were deleted ?
> I noticed that in my publication DB the MSmerge_tombstone table has a
field
> that shows either "user delete" or "system delete" - what is a System
> Delete?
> Thanks for your help.
> Darren
>
|||www.lumigent.com
"Darren Wallace" <darren@.pcresources.com.au> wrote in message
news:eQdrh$FmEHA.3524@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm using merge replication with SQL Server 2000 on Windows 2003 server,
> with MSDE2000A clients with anon pull subscriptions.
> Some data has been (mysteriously) deleted and I would like to find out
more
> about who/when it was deleted.
> Is there any way (system tables, transaction log ?) of determining
who/when
> certain records were deleted ?
> I noticed that in my publication DB the MSmerge_tombstone table has a
field
> that shows either "user delete" or "system delete" - what is a System
> Delete?
> Thanks for your help.
> Darren
>

Any way to make this shorter?

I have a T-SQL query that is used to pull up some data for once-a-day export, just out of curiosity more then anything, is there a way to make this shorter?

SELECT DISTINCT
u.userId,
u.lastName,
u.firstName,
u.address1,
u.address2,
u.city,
u.state,
u.zip,
CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)= 0
THEN 'Corporate'
WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)<> 0
THEN 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))
WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0
THEN 'Corporate'
WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) <> 0
THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0))
WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0
THEN 'Corporate'
WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) <> 0
THEN 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0))
END CPGkey
FROM [...] JOIN [...]

I'm hoping there is something in a way of...
If userClass Between 1 and 3 Then
IF COALESCE(u.RetailerNumber_fk,0)= 0 Then 'Corporate'
ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))
End IF as CPGKey,

Thanks in advace.Not really. This is a little more concise, as it avoids duplicating comparisons:CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 THEN
CASE WHEN COALESCE(u.RetailerNumber_fk,0)= 0 THEN 'Corporate'
ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0)) END
WHEN u.UserClassID_fk BETWEEN 4 AND 8 THEN
CASE WHEN COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0 THEN 'Corporate'
ELSE 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0)) END
WHEN u.UserClassID_fk BETWEEN 9 AND 14 THEN
CASE WHEN COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0 THEN 'Corporate'
ELSE 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0)) END
END CPGkeysql