Push Subscription - will it overwrite existing data first?

If I push a new subscription to an existing target database, and I choose or
check the box to include schema and data, will replication first delete or
empty tables at the target before pushing a new?
I did this, that is to push the subscription and checked the box to send
schema and data and killed the process mid-stream. At the target I found
certain tables to be empty. I am concluding that replication will first
empty or delete tables at the target.
Am I correct?
thanks,
bob
Bob,
on the article properties of a table, on the snapshot tab there is the
option to control this behaviour. By default if you look at the bcp files
per article, they will start with a drop table then a create table and after
that the rows will be inserted. It looks like you caught the process in
between these batches.
HTH,
Paul Ibison

Push replication

I need to pull the schema only with no data, then push the data back without
deletes on the publisher, then delete the client data. Next time these
deletes would not be pushed up. Only new data.
SQL Server 2005 June CTP and SQL Mobile 2005
"msmith" wrote:

> I need to pull the schema only with no data, then push the data back without
> deletes on the publisher, then delete the client data. Next time these
> deletes would not be pushed up. Only new data.

Purging, Delete or Archive

Hi All,

I have a database that has grown to over 6 GB in the last week. This database comes with a third party application tool that was implemented in our company over 3 months ago. My team tells me it's a SQL database, and to correct the current situation we'll need to purge the database or archive the records to a different server.If we purge the records will we be able to retrieve it later if needed?Why would I purge and not archive.

Excuse my questions but we don’t have a DBA on broad and I would like to better understand options we are faced with.

Well D, we don't have DBA's on broads here either (HR would frow on that!)... Anyways, the word PURGE means it's gone... now if you archive off to a different server and TEHn purge from your main database, then you have it on the archive database... You sure the row count is high, and it's not a large file because of large autogrowths? that type of thing? Look at teh database and see how large the actual USED space is... If you are using 6GB, maybe it needs to be reviewd as to why it's taking up all those rows? Might just need a shrink... Bruce|||

Don't forget to check the log file. Make sure it's backed up on a regular basis if you want the log files, or else check the box for Truncate on Checkpoint. You didn't say how large the file was 3 weeks ago to the 6 GB is is now, but check the autogrowth and the log file.

Thanks,

Angel

|||

Is the 6GB really a problem? Unless the problem is as described in the posts above and you are having 'autogrowth' issues this should not be a problem if you have the right hardware and a properly designed application.

I would never purge data unless I was absolutely sure I NEVER EVER needed it again.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Thank you all for your helpful response.

Yes the 6GB is a problem, and the time frame it took to grow to 6 GB. We are having hardware availability issues right now. We hope to rectify this problem by the end of the year.

If purging the data really boils down to permanently deleting it from the db...I don't think it the way to go. I am not sure if we'll ever need this data in the future.

Thanks,

Devon Brooks

Purging, Delete or Archive

Hi All,

I have a database that has grown to over 6 GB in the last week. This database comes with a third party application tool that was implemented in our company over 3 months ago. My team tells me it's a SQL database, and to correct the current situation we'll need to purge the database or archive the records to a different server.If we purge the records will we be able to retrieve it later if needed?Why would I purge and not archive.

Excuse my questions but we don’t have a DBA on broad and I would like to better understand options we are faced with.

Well D, we don't have DBA's on broads here either (HR would frow on that!)... Anyways, the word PURGE means it's gone... now if you archive off to a different server and TEHn purge from your main database, then you have it on the archive database... You sure the row count is high, and it's not a large file because of large autogrowths? that type of thing? Look at teh database and see how large the actual USED space is... If you are using 6GB, maybe it needs to be reviewd as to why it's taking up all those rows? Might just need a shrink... Bruce|||

Don't forget to check the log file. Make sure it's backed up on a regular basis if you want the log files, or else check the box for Truncate on Checkpoint. You didn't say how large the file was 3 weeks ago to the 6 GB is is now, but check the autogrowth and the log file.

Thanks,

Angel

|||

Is the 6GB really a problem? Unless the problem is as described in the posts above and you are having 'autogrowth' issues this should not be a problem if you have the right hardware and a properly designed application.

I would never purge data unless I was absolutely sure I NEVER EVER needed it again.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Thank you all for your helpful response.

Yes the 6GB is a problem, and the time frame it took to grow to 6 GB. We are having hardware availability issues right now. We hope to rectify this problem by the end of the year.

If purging the data really boils down to permanently deleting it from the db...I don't think it the way to go. I am not sure if we'll ever need this data in the future.

Thanks,

Devon Brooks

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnHi John
You could use SET ROWCOUNT 100 before issuing the delete statement. i.e. to
delete in blocks of 100
DECLARE @.earlierstdate datetime
SET @.earlierstdate = getdate()-90
SET ROWCOUNT 100
delete [<table>] where date < @.earlierstdate
WHILE @.@.ROWCOUNT > 0
delete [<table>] where date < @.earlierstdate
SET ROWCOUNT 0
John
"John J. Hughes II" wrote:

> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
>|||John J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.

> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...[vbcol=seagreen]
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>|||Hi John
"John J. Hughes II" wrote:

> John,
> Guess I did not see it before, thanks. By the way the BOL says to use top
> in new development since ROWCOUNT will no longer be supported. If I
> understand correctly then it should be the following:
> delete top(100) [<table>] where data < @.earlierstdate;
>
TOP is only for SQL 2005.

> I was thinking of putting this in a JOB set to when idle Do you think th
is
> would cause too much thrashing or would it be better to put it as you do b
ut
> with a waitfor delay. It would be nice to allow it to run until the serv
er
> became busy and then exit until next idle.
> Something like:
> while(@.@.rowcount > 0 and @.@.cpu < (something)
> delete top(100) [<table>] where data < @.earlierstdate;
> FROM BOL:
> Important:
> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements i
n
> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
> INSERT, and UPDATE statements in new development work, and plan to modify
> applications that currently use it. We recommend that DELETE, INSERT, and
> UPDATE statements that currently are using SET ROWCOUNT be rewritten to us
e
> TOP.
>
How you implement it will depend on how long your quiet periods are and how
many records you are deleting. I would probably start of with a single job
but delting in batches of 5000 (say) and then see if it is an issue. If you
have a policy of only keeping n days then rather than doing that as a monthy
process I would do it more gradually (say daily or weekly).
Make sure you do this deletion before you re-index.
If you do manage to get the system onto SQL 2005 then I would look at using
table partitions.
There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't think
they will be useful to you.
HTH

> regards,
> John
>
John|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>
> TOP is only for SQL 2005.
>
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>
> John
>

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnJohn J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date >
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much. I
> was hoping for something like the above but will a limit of say 100 records
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> > getdate()-90". The problem is this really puts a load on the system.
>> I would like this to be an idle job that does not load the system as
>> much. I was hoping for something like the above but will a limit of say
>> 100 records each time, is that possible?
>> Regards,
>> John
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> >
>> getdate()-90". The problem is this really puts a load on the system.
>> I
>> would like this to be an idle job that does not load the system as much.
>> I
>> was hoping for something like the above but will a limit of say 100
>> records
>> each time, is that possible?
>> Regards,
>> John
>>|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>> John,
>> Guess I did not see it before, thanks. By the way the BOL says to use
>> top
>> in new development since ROWCOUNT will no longer be supported. If I
>> understand correctly then it should be the following:
>> delete top(100) [<table>] where data < @.earlierstdate;
> TOP is only for SQL 2005.
>> I was thinking of putting this in a JOB set to when idle Do you think
>> this
>> would cause too much thrashing or would it be better to put it as you do
>> but
>> with a waitfor delay. It would be nice to allow it to run until the
>> server
>> became busy and then exit until next idle.
>> Something like:
>> while(@.@.rowcount > 0 and @.@.cpu < (something)
>> delete top(100) [<table>] where data < @.earlierstdate;
>> FROM BOL:
>> Important:
>> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements
>> in
>> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
>> INSERT, and UPDATE statements in new development work, and plan to modify
>> applications that currently use it. We recommend that DELETE, INSERT, and
>> UPDATE statements that currently are using SET ROWCOUNT be rewritten to
>> use
>> TOP.
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>> regards,
>> John
> John
>

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:

> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this da
y.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:
> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this day.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>sql

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.
Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:

> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this day.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>

purge all history from msdb

I am looking for a way to setup a periodic delete from all history tables in
msdb, to prevent excessive growth. I am wondering if the following procedures
includes all necessary deletes.
1) In the maintenance plan wizard reporting tab, setting the number of
records to retain in sys_dbmaintplanhistory; older records will be deleted.
2) Run sp_delete_backuphistory to delete records from several backup and
restore history tables.
3) Run sp_purge_jobhistory to remove history from other jobs.
Am I missing anything? There is also an sp_purgehistory, but BOL says this
is for backward compatibility only; does anyone use this?
--
MunchingBillHi Munching,
Thanks for your post.
It is not necessary for you to execute sp_purgehistory. Based on my
knowledge, these three steps you have listed are sufficient for you to
delete from all history tables.
Also, please note that this question appears to be consulting in nature. We
would also like to introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/default.aspx?pr=AdvisoryService
Outside of the US/Canada:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

purge all history from msdb

I am looking for a way to setup a periodic delete from all history tables in
msdb, to prevent excessive growth. I am wondering if the following procedure
s
includes all necessary deletes.
1) In the maintenance plan wizard reporting tab, setting the number of
records to retain in sys_dbmaintplanhistory; older records will be deleted.
2) Run sp_delete_backuphistory to delete records from several backup and
restore history tables.
3) Run sp_purge_jobhistory to remove history from other jobs.
Am I missing anything? There is also an sp_purgehistory, but BOL says this
is for backward compatibility only; does anyone use this?
--
MunchingBillHi Munching,
Thanks for your post.
It is not necessary for you to execute sp_purgehistory. Based on my
knowledge, these three steps you have listed are sufficient for you to
delete from all history tables.
Also, please note that this question appears to be consulting in nature. We
would also like to introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/defaul...AdvisoryService
Outside of the US/Canada:
http://support.microsoft.com/defaul...ernational.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

purge all history from msdb

I am looking for a way to setup a periodic delete from all history tables in
msdb, to prevent excessive growth. I am wondering if the following procedures
includes all necessary deletes.
1) In the maintenance plan wizard reporting tab, setting the number of
records to retain in sys_dbmaintplanhistory; older records will be deleted.
2) Run sp_delete_backuphistory to delete records from several backup and
restore history tables.
3) Run sp_purge_jobhistory to remove history from other jobs.
Am I missing anything? There is also an sp_purgehistory, but BOL says this
is for backward compatibility only; does anyone use this?
MunchingBill
Hi Munching,
Thanks for your post.
It is not necessary for you to execute sp_purgehistory. Based on my
knowledge, these three steps you have listed are sufficient for you to
delete from all history tables.
Also, please note that this question appears to be consulting in nature. We
would also like to introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/default...dvisoryService
Outside of the US/Canada:
http://support.microsoft.com/default...rnational.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Purge

Hi Everyone.

i have a big database and for each table i have Active field (N/Y) and i wanna delete all record the active N. how i can do that without delete from each tables and looking to details tables before. our database have a complex relations.

please any solution.

thanks alot.

There is no simple way. You will have to scan through the entire database and execute 'delete

' for each desired table. Since you're doing data scrubbing, it might be simpler to use DTS/SSIS to extract desired data (i.e. Y rows) from every table into new table(s). Recreate constraints as needed after that. Else you will have to track down all all referenced/referencing tables and delete accordingly.

This little old script should help finding the ref tables.

Code Snippet

if object_id('usp_findreferences','p') is not null
drop proc usp_findreferences
go

create procedure usp_findreferences
/**************************************************************************-*
*/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/**************************************************************************-*
*/
@.tbname sysname=null
as
set nocount on

Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end

print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go

|||

A some help can be get from "ON DELETE {CASCADE | NO ACTION |SET DEFAULT | SET NULL} " , the option of referential constraints.But for that you have to review the relationshisps of your db. You can be helped by "Database Diagrams" in Management Studio.

Public Role for SQL 2000

For SQL 2000, I see that the Public role has some privileges like SELECT,
UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
(Tables, views, &Stored procedures .)
Is that a security concern?
What will happen if I remove these privileges or revoke them?
Will that effect the rest of the users?
I read many threads on the internet, but no one could tell me the answers
for the 3 questions above.
Thanks in advance.wit1 (wit1@.hotmail.com) writes:
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
That is not the default.

> Is that a security concern?
It does not sound like the best security to me.

> What will happen if I remove these privileges or revoke them?
Impossible to tell as it depends on the application using the database.

> Will that effect the rest of the users?
Again, that depends on the application using the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, I too have same questions.
I tried to drop this public role but not able to drop.
I tried to Revoke Select permission but not able to do that. It is not
giving any error but not working as expected. Any urgent reply will be
gratly appreciated.
Thank,
Tushar Vaja
"wit1" <wit1@.hotmail.com> wrote in message
news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
>
> Is that a security concern?
> What will happen if I remove these privileges or revoke them?
> Will that effect the rest of the users?
>
> I read many threads on the internet, but no one could tell me the answers
> for the 3 questions above.
> Thanks in advance.
>
>|||> Hi, I too have same questions.
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
> Thank,
> Tushar Vaja
>
> "wit1" <wit1@.hotmail.com> wrote in message
> news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
You could try DENY, but since everyone is in public, this could have
the undesired effect that no one can access anything.
But maybe there is some misunderstanding? Could you clarify more precisely
what tables that are accessible to users who should not get there?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, thanks for reply. My problem is below:
Whenever user run my exe on his system, my app will create two DB on his
local system. 1 DB contain general info and second DB contain some very
confidenmt info. Now i do not want user(the person who has installed my app)
to access the second DB but he can access 1st DB if he wants.
I do not knwo how to implemet this thing. Please help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99337CC774783Yazorman@.127.0.0.1...
> Tushar (tushar_vaja@.yahoo.co.in) writes:
> You could try DENY, but since everyone is in public, this could have
> the undesired effect that no one can access anything.
> But maybe there is some misunderstanding? Could you clarify more precisely
> what tables that are accessible to users who should not get there?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> Whenever user run my exe on his system, my app will create two DB on his
> local system. 1 DB contain general info and second DB contain some very
> confidenmt info. Now i do not want user(the person who has installed my
> app) to access the second DB but he can access 1st DB if he wants. I do
> not knwo how to implemet this thing. Please help.
Presumably the user who installed the application will have admin rights
on the machine, and you cannot hide anything from an administrator.
You can of course, store the data in the database encrypted, and then
your application could decrypt the data as needed. But since the
application would have to hide the encryption key somewhere, it's not
safe from a user who is dead set from accessing the data, but at least
it protects you from the stray wanderer.
In any case, you need to cover this situation in the license agreement,
and explicitly say that disclosing the data in the database is not
permitted.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
The following excerpt is taken from Chapter 5 - Microsoft SQL Server 2000
Security of Microsoft SQL Server 2000 Administrator's Pocket Consultant (ISB
N
0-7356-1129-7):
public is the default role for all database users. Users inherit the
permissions and privileges of the public role, and this role represents
their minimum permissions and privileges. Any role that you assign to a
user, beyond the public role, add permissions and privileges. If you want
all database users to have specific permissions, assign the permissions to
the public role.
The following excerpt is taken from Chapter 8 - Managing SQL Serer 2005
Security of Microsoft SQL Server 2005 Administrator's Pocket Consultant:
The guest user is a special user that you can add to a database to allow
anyone with a valid SQL Server login to access the database... Before using
the guest user, you should not the following information about the account:
The guest user is a member of the public server role and inherits the
permissions of this role.
The guest user must exist in a database before anyone can access it as a
guest.
The guest user is used only when a user account has access to SQL Server
but does not have access to the database through this user account.
Other topics around public server role that may be of interest include:
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
http://www.microsoft.com/technet/pr...n/sp3sec01.mspx
http://www.microsoft.com/technet/pr...ploy/mysql.mspx
10 Steps to help Secure SQL Server 2000
https://www.microsoft.com/sql/prodi...n/sp3sec04.mspx
SQL Server 2000 SP3 Security Features and Best Practices: Implementation of
Server Level Security and Object Level Security
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
SQL Server 2005 (BOL) - Security Considerations for a SQL Server Installatio
n
http://msdn2.microsoft.com/en-us/library/ms144228.aspx
Regards,
Keith Wilson
Disclaimer: this posting is provided "as is" without implied or express
warranties.
"Tushar" wrote:

>
>