HELP! More deleted records!

I have a replicated database and hundreds of records keep "disappearing" from
one table in particular. The table is a child table storing the answers to a
number of questions. Also;
1. The tables have referential integrity enforced, with cascade delete.
2. The relationships are enforced for replication.
3. There are no stored procedures which delete records from the offending
table.
4. There is no way of manually deleting records directly from the offending
table using the front-end application.
5. The parent tables have exactly the same records in as previously.
6. There are no replication conflicts.
Any ideas?
I am keeping transaction logs for 3 weeks. Is there any way that I can find
out from the transaction logs when the records were deleted? Is there any way
I can find out why the records would have been deleted?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
Nigel,
you don't say if this is on the publisher or on the subscriber, but I'm
guessing the latter. It could be that some updates are replicated as a
delete-insert pair for transactional replication in ceertain cases (see
http://support.microsoft.com/default...NoWebContent=1).
Perhaps this is your case?
Also I'm not clear why you're using cascading constraints for the subscriber
which is normally treated as RO - or maybe I have misunderstood?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
It's an ftp merge replication. I'm not sure where the records are
disappearing from first but the publisher is definately causing the other
subscribers (of which there are about 12) to lose the records too.
The relationships (i.e. cascade delete and enforce for replication) were
automatically created when the subscription was first created from the
publisher.
With the delete-insert pair the records would be recreated wouldn't they?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
|||In fact, when I use your spBrowseMergeChanges procedure on the offending
table, there are a load more records marked for deletion and without a
replacement!
Nigel Taylor
http://www.tinit.co.uk/
|||Nigel,
as you're using merge, and assuming you have all the related tables being
replicated, I'd modify all relationships to be not enforced for replication.
The delete-insert pairs are a problem for transactional and not merge, but
with merge there are other reasons the FK constraints are not applied (see
http://www.replicationanswers.com/Me...derArticle.asp). This
will simplify the setup somewhat and hopefully allow us to see if this is
the cause of the problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Okay, thanks Paul. I've removed the 'enforce relationship for replication'
flag and removed the cascade deletes on the offending tables. Hopefully it'll
work and I can get back to doing something else.
Cheers,
Nigel Taylor
http://www.tinit.co.uk/

HELP! Dynamic binding of schema element names

Hi all,
Somehow I lost the thread where I originally posted this query. Thanks
Erland and GregO for your answers. Very helpful indeed.
Although I think I can solve my problem with what you guys suggested, I'd
really like to know your opinion and maybe offer solutions
of achieving my goal. Let me put my problem forth in more detail:
My application has all it's logic in a COM component and all SQL queries are
issued from there.
The application itself was never designed with security and access-control
in mind
(which I'm cursing it for and have to incorporate now :-(( ). So, now I have
a
few thousand queries that I don't want to affect drastically.
I have a bunch of users in a [USER] table and a bunch of resources in a
[RESOURCE] table. What ultimately should happen is that
every user should be able to see resources only entitled to her based on
some security policy.
Here is how I think it can be done.
A new entity [SEC_GROUP] can be introduced where each user is part of one or
more security groups. For each security group,
I will create a view on the [RESOURCE] table: [admingrp_resource],
[generaluser_resource] and so on.
So, when a user issues a query like,
SELECT * FROM [RESOURCE]
I will actually substitute [RESOURCE] with a function like
get_resource_view(userid)
EXEC( 'SELECT * FROM ' + get_resource_view(userid))
The get_resource_view( ) function would get the appropriate resource view
for the user based on her security group.
The above would be rather easy if the user is part of only one security
group. If there are more, I might have to do
some unions. This would impact the queries quite a bit, but I can't think of
another way to implement this.
Once again, thanks for your responses, I appreciate your help and looking
forward to more suggestions.
Best regards,
--Abhi
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
>
> Here's a simple select statement
>
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
>
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
>
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition = > 1.
>
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.aspAbhijith Das (adas@.expeditevcs.com) writes:
> My application has all it's logic in a COM component and all SQL queries
> are issued from there. The application itself was never designed with
> security and access-control in mind (which I'm cursing it for and have
> to incorporate now :-(( ). So, now I have a few thousand queries that I
> don't want to affect drastically.
> I have a bunch of users in a [USER] table and a bunch of resources in a
> [RESOURCE] table. What ultimately should happen is that every user
> should be able to see resources only entitled to her based on some
> security policy. Here is how I think it can be done. A new entity
> [SEC_GROUP] can be introduced where each user is part of one or more
> security groups. For each security group, I will create a view on the
> [RESOURCE] table: [admingrp_resource], [generaluser_resource] and so
> on.
First of all, for this to be meaningful, you need to revoke access to
the tables from the users. Keep in mind that there are other means to
connecting to SQL Server, and a skilled user could for instance use
Query Analyzer to access the data.
> So, when a user issues a query like,
> SELECT * FROM [RESOURCE]
> I will actually substitute [RESOURCE] with a function like
> get_resource_view(userid)
> EXEC( 'SELECT * FROM ' + get_resource_view(userid))
But why do you want to have this function in SQL? Since you apparently
have all your logic client-side, why not stick to that? Depending on
the size of the data stored for these security groups, you could read
this data once, and keep it in memory. (May need some refresh mechanism
in case the security configuration is changed.)
From this follows that the user will need to have SELECT access to
the table what defines the security groups and the resources. (Unless
you use an application role.)
> The get_resource_view( ) function would get the appropriate resource
> view for the user based on her security group. The above would be rather
> easy if the user is part of only one security group. If there are more,
> I might have to do some unions. This would impact the queries quite a
> bit, but I can't think of another way to implement this.
A common approach to row-level security is to have views that includes
conditions like:
AND userid = SYSTEM_USER
Although one should be aware of that a skilled user with a query tool can
still be able to carve out glimpses of data he is not intended to see.
For a more complex security scheme, you could have table-valued
functions, but you would have to one for each base table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp