deleting publications

Hi,
I created a few push subscriptions in continuous mode for testing
merge replication. I stopped the synchronization before I tried to
delete the publication. I got an error stating, can't delete because
the publication is being used for replication. I can't delete the
subscriptions on the subscriber as well. The tables associated with
the publications also can't be deleted. I don't want to use the Wizard
to delete all the publications.
Does anyone know how I can delete the publications?
Thanks!
PS.
PS,
try using sp_dropmergesubscription for each subscription, followed by
sp_dropmergepublication. Please report back the exact error message if there
is one.
When we can get the publication deleted: to ultimately drop the table you
can use a stored procedure to do this called sp_MSunmarkreplinfo which takes
a tablename as a parameter. Alternatively, running sp_removedbreplication
can be used to remove all traces of replication in the subscriber database,
but obviously must only be done if this database is not also configured as a
publisher.
HTH,
Paul Ibison
|||Hi,
I tried what you suggested and I got rid of the subscriptions and
publications.This is what I did:
I stopped synchronization, and deleted the publication entry under
Replication.
I then called sp_MSunmarkreplinfo to delete the table used for
replication.
On the subscriber however,
I had to delete the entries from 'sysmergearticles' and
'sysmergepublications' in order to delete the subscription entry under
'replication->subscriptions' and then call sp_MSunmarkreplinfo' to
delete the table.
Thanks for the help!
PS.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<urzlrrQVEHA.584@.TK2MSFTNGP09.phx.gbl>...
> PS,
> try using sp_dropmergesubscription for each subscription, followed by
> sp_dropmergepublication. Please report back the exact error message if there
> is one.
> When we can get the publication deleted: to ultimately drop the table you
> can use a stored procedure to do this called sp_MSunmarkreplinfo which takes
> a tablename as a parameter. Alternatively, running sp_removedbreplication
> can be used to remove all traces of replication in the subscriber database,
> but obviously must only be done if this database is not also configured as a
> publisher.
> HTH,
> Paul Ibison

Deleting Legacy maintenance plans

Hi,
I am not able to delete the Legacy maintenance plans created:
for example I created a maintenance plan: "MyPlan" with:
master.dbo.xp_sqlmaint
and the plan created at:
Management\Legacy\Database Maintenance Plans\MyPlan

After this I tryed to delete this using SQL Server Management Studio, it is
deleted (right click on the plan and select delete menu item)
But when I refresh the server, the "MyPlan" is back again.

Any idea how to delete Legacy Maintenance plans?

Thanks,
Venkat

What service pack level are you on? This is something that could potentially have been resolved in SP2. Or even SP1 if you are on RTM. At any rate here are a couple

At any rate here are a couple links that might point you in the right direction... http://mssqltips.com/tip.asp?tip=1137 that article explains where these plans are stored (you can also run profiler while you drill into that section - note it gets populated before you click on maintenance plans, so start your trace before you even click the plus before Legacy.. It may stay cached so maybe just start Management Studio from scracth. That trace should confirm above and show a query towards the tables indicated. This will not be supported and should be tested thoroughly first but you should be able to delete from the tables referenced once you understand the information for the plan you are discussing.

Here is another forum article of someone who also had issues and there results..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=454044&SiteID=1

|||

Hi,

i am using the following SQL 2005 software:

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

I resolved the problem by explicitly depeting the records from the maintenance plan tables.Looks like the deletion from UI has some serious problem, it says deleted, but when i reopne the SQL Server management studio, i can still see my plans.

Any way the problem was solved, hopefully this will get fixed by MS in later service packs.

Thanks,

Venkat

|||

So it sounds like the links to the tables helped you out then.

Just a quick point, you say you hope this will be fixed in a later service pack.. You are not currently on the latest version of SQL Server 2005. You might want to try and upgrade first and see if it is fixed in a "later" service pack.

Deleting from sql question.

I have a data source which I created a custom statement for which I need to delete from. I created the folowing selecct statement

SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @.UserName)

I wrote the following DELETE statement

DELETE FROM SrcComputer WHERE (CompId = @.CompId)

I have tested it in SQL server management and it does what I want it to do, but I don't know how to use it in my code. I put it inside of the DELETE tab inside of my custome statement and added a delete button inside of the gridview which utializes my data source, but it's not working. I don't know how to set the CompId variable? I want the statement to delete the row that the user clicks on. Can anyone give me some advice?

You can set the delete parameter like in this sample:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SNo" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="SNo" HeaderText="SNo" ReadOnly="True" SortExpression="SNo" />
<asp:BoundField DataField="CQNo" HeaderText="CQNo" SortExpression="CQNo" />
<asp:BoundField DataField="WorkDate" HeaderText="WorkDate" SortExpression="WorkDate" />
<asp:BoundField DataField="Analysis" HeaderText="Analysis" SortExpression="Analysis" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MSDN_forumConnectionString %>"
DeleteCommand="DELETE FROM [effort] WHERE [SNo] = @.SNo"
SelectCommand="SELECT [SNo], [CQNo], [WorkDate], [Analysis] FROM [effort]"
UpdateCommand="UPDATE [effort] SET [CQNo] = @.CQNo, [WorkDate] = @.WorkDate, [Analysis] = @.Analysis WHERE [SNo] = @.SNo">
<DeleteParameters>
<asp:Parameter Name="SNo" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CQNo" Type="String" />
<asp:Parameter Name="WorkDate" Type="DateTime" />
<asp:Parameter Name="Analysis" Type="Decimal" />
<asp:Parameter Name="SNo" Type="Int32" />
</UpdateParameters>

</asp:SqlDataSource>

|||

ok I believe that I have done what you said. Most of it was already there the Parameter CompId's type wasn't there but the rest was there here is what I have.

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:srcConnectionString1 %>"SelectCommand="SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @.UserName)"OnSelecting="SqlDataSource2_Selecting"DeleteCommand="DELETE FROM SrcComputer WHERE [CompId] = @.CompId"><SelectParameters><asp:SessionParameterName="UserName"SessionField="UserName"Type="String"/></SelectParameters><DeleteParameters><asp:ParameterName="CompId"Type="int64"/></DeleteParameters></asp:SqlDataSource>

My question is where do assign a value for CompId. When I click the delete link it does nothing. Just seems to refresh the page and nothing changes.

|||You need to set DataKeyNames="CompId" from your gridview .|||

limno,

Thank you. That is exactly what I needed. I have one other question for you. If I am already using the DataKeyNames for a different variable is it possible to have 2? It just so happened in this case I could use the same variable for both selecting and deleting.

Thanks, Matt

|||

Matt,

You can have more than one as your DataKeyNames, just separate them with comma like DataKeyNames="id1, id2".

|||Thanks that's good to know also.sql

Deleting Duplicated Rows

Hi,
I have a table named "std_attn", where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK. So Now tell me the way to remove the duplicaies..............
thnxDROP DATABASE database_name

:)

just kidding

do you want to remove the duplicates, or remove all but one duplicate?

and if all but one, how do you pick which one?

is there some column that is incrementing, like an IDENTITY or a dateadded column or something? if so, you could keep the last one of each group...|||Since there is no PK, insert the duplicate records into a temp table with an identity column. You can then delete the records from the source table and insert a SELECT DISTINCT from the temp table back into your source table. After this is done, put a constraint on the table and fix the application. :)|||derrick, how does this isolate which one of the duplicates to keep?

personally, i like the approach offered in this article:
Advanced SQL Techniques - Part 1: Analyzing Duplicate Records (http://www.15seconds.com/issue/011009.htm)|||? They're duplicates. Who cares which one you keep. This gets rid of all of them, then inserts one back in.|||Hi,
No Derrick, its not that I don't want any of the duplicate rows. I need one. I will tell you, what actually i was trying..........

1. I made a temporary table which was the copy of the original one
2. Then copied all the rows from original to the temporary one.
3. Then deleted all rows from the original one
4. Defind the PK in the original table
5. Added a column in temporary table with allow null property. The data type was boolean. It was made for the reason to update the it with the value "yes" if the rows was having any duplicate row and "no" in the row is unique.
6. Then insert into the original from temporary where the column value is equal to "no"

But unfortunetly I failed at the 5th step only. I could not get the correct SQL stetement...

So it'll be a great help, if any one can get me the correct SQL statement

thnx|||did you try the method in the article i gave you the link for?|||Ok guys, this isn't that hard. The article you referred to is in reference to tables that have an identity column, which means there's something in the row that makes it intrinsically unique even though it's just an ID field. This works great, except for the fact his table does not have an identity column. This means the idea in the article does not work.

That's why he needs to do what I told him to do, which does work. There's really not a "better" way to do it if there's nothing to distinguish between the rows in the table. You were doing fine until you tried to get fancy.

1. Insert the duplicate records into a temp table with an identity column.
2. Delete the duplicate records only from the source table.
3. Insert a SELECT DISTINCT from the temp table back into your source table.
>>This part is really important and you kind of missed it last time around.
4. Put a constraint on the table and fix the application.|||? They're duplicates. Who cares which one you keep. This gets rid of all of them, then inserts one back in.

Wanna bet...

U$1000.00 That they're not......betcha something is different|||Then he needs to ask the right question. I'm answering the one he asked, so I really don't care if something is different. :) How ya' doing Brett. I just keep seeing you on these forums. lol|||But just like the day to day job you're in...is the question asked EVER what the real question is, and what the real answer is suppose to be?

Ever?

OK, sometimes...but rarley....

And yeah...an addiction is an addiction...sorry...I just can't help myself...|||If he did ask the question appropriately though, the answer is correct. If not, then the article suggested should work. We both know the real issue is the fact he got the rows in the first place. The reality is that the application needs fixed and constraints need to be created so it doesn't happen in the first place. :)|||Hi,
where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK.

Yeah Derrick...I'm not disagreeing with you ...the answers solve the the question asked...

I just don't believe the question...

Some bad coding...puuuuuuleeeeeeeeeeze...

Some bad db design...

A good db design FORCES the developers to do the right thing...

@.@.ERROR <> 0

and to be TOTALLY politically incorrect...

EDIT: ...nope...uncalled for...sorry

Damn...it's Monday again...

Sorry for the foul mood...|||You were doing fine until you tried to get fancy. me? fancy? that'll be the day

1. Insert the duplicate records into a temp table with an identity column.why does the temp table need an identity column?|||Literacy is a wonderful thing. :) You don't. I'm smoking crack and up too late. You do need the rest though.

It's just that Monday thing. (We'll all use that as an excuse today.)|||(We'll all use that as an excuse today.)

i even use it on other days as well

;)|||There is nothing to laugh brett.........

Database can be wroungly designed....................After all its human act n instinct. Else there won't b any difference between u n god. And I assume that, ur nt GOD. Just a human........................who is bound to do the mistakes................n that is wht it hapnd with th table i was talking about.
But it will more more disgusting, if that wrong design is not corrected in time. And that is why we are here to discuss. Don't make fuss of the issue. Even you may had mistakes in your early days of career. And lemme tell you one thing...... the person who has designed the databse is from genetics background. So these are the expected errors. And now I have been assigned to remove the duplicated rows, and make the table perfect, in the sense that, no more dulicated rows should be created.|||Brett ... Should we call that a second one :p

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35538|||And now I have been assigned to remove the duplicated rows, and make the table perfect, in the sense that, no more dulicated rows should be created.

What's with getting all bent out of shape about?

My POINT...is that you do not have duplicates...Where's the table DDL?

What do you call a duplicate?

Where's the sample data?

Where's ANYTHING you can supply the good people of dbforums

And why are you so offended...can you point out what I said that caused so much angst?

So I'll know to be more careful next time?

And hey...didn't you notice my label?|||After all its human act n instinct. .

Well it was a human that started all of this business...more dba's should understand his concepts

http://en.wikipedia.org/wiki/Edgar_F._Codd

Just a human who is bound to do the mistakes

Sure enough...kinda like answering questions

the person who has designed the databse is from genetics background. So these are the expected errors.

Watch your genome...if he asks you to help in a research project...look out...

And now I have been assigned to remove the duplicated rows, and make the table perfect, in the sense that, no more dulicated rows should be created.

So now back to the real issue.

Please

Give us the DDL of the table

Like

CREATE TABLE myTable99(Col1 int, Col2 char(10), ect...

Some sample "duplicate" data

INSERT INTO myTable99(Col1, Col2, ect..
SELECT data,data,data UNION ALL
SELECT data,data,data UNION ALL
SELECT data,data,data UNION ALL
SELECT data,data,data UNION ALL
...
SELECT data,data,data

And what the expected results should be...

OK?|||this is like pushing a rope

never mind the ddl, brett

ask them merely to define what a duplicate is

remind them that autonumber/identity columns used as the pk merely masks the real issue

the real issue is, what's a duplicate?

let them do the work, not you :cool:|||OK...

Define a Duplicate

(shameful, isn't it?)

:rolleyes:|||Haven't we already defined what a duplicate is. lol I'm sooooo confused now. All these thoughts duplicating in my heads. I mean it's so confusing. All those thoughts duplicating in my headers...my heads...my my my

I'm confused.

Really.

Deleting database backups and logs

New to SQL server. During my 'experimental' phase I created backups plans
to back up databases. Missed the part where the data would be removed after
XX time/days, can I safely delete the old TRN and BAK up file in the backup
folder located in the MSSQL directory directly from Windows Explorer?
The short answer is yes.
Rick Sawtell
MCT, MCSD, MCDBA
<msnews.microsoft.com> wrote in message
news:uZCO4EclEHA.1712@.TK2MSFTNGP09.phx.gbl...
> New to SQL server. During my 'experimental' phase I created backups plans
> to back up databases. Missed the part where the data would be removed
after
> XX time/days, can I safely delete the old TRN and BAK up file in the
backup
> folder located in the MSSQL directory directly from Windows Explorer?
>
|||You can also modify the Maintenance Plan once it has been created. In SQL
Enterprise Manager, open the "Maintenance" node. Select Maintenance Plans,
then double-click the maintenance plan that you created. In here you can
modify the properties for the maintenence plan (including the property to
delete old backups). However the answer to your question is - yes you can
delete the files manually through Explorer. These are backup files and SQL
does not hold a lock on these files once it has completed writing them out.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Deleting database backups and logs

New to SQL server. During my 'experimental' phase I created backups plans
to back up databases. Missed the part where the data would be removed after
XX time/days, can I safely delete the old TRN and BAK up file in the backup
folder located in the MSSQL directory directly from Windows Explorer?The short answer is yes.
Rick Sawtell
MCT, MCSD, MCDBA
<msnews.microsoft.com> wrote in message
news:uZCO4EclEHA.1712@.TK2MSFTNGP09.phx.gbl...
> New to SQL server. During my 'experimental' phase I created backups plans
> to back up databases. Missed the part where the data would be removed
after
> XX time/days, can I safely delete the old TRN and BAK up file in the
backup
> folder located in the MSSQL directory directly from Windows Explorer?
>|||You can also modify the Maintenance Plan once it has been created. In SQL
Enterprise Manager, open the "Maintenance" node. Select Maintenance Plans,
then double-click the maintenance plan that you created. In here you can
modify the properties for the maintenence plan (including the property to
delete old backups). However the answer to your question is - yes you can
delete the files manually through Explorer. These are backup files and SQL
does not hold a lock on these files once it has completed writing them out.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Deleting Data From DB

I have edited the aspnet_Users_CreateUser stored procedure so that the UserId that is created when a new user is created is copied to a UserId field in another table. However, when I use the website administration tool to delete users that have been created, it gives me an error saying the delete statement conflicted with the reference constraint. I then added the following code in the aspnet_Users_DeleteUser procedure...

IF((@.TablesToDeleteFrom & 16) <> 0AND

(

EXISTS(SELECT name FROMsysobjectsWHERE(name= N'vw_tt')AND(type ='V'))))BEGINDELETE FROMdbo.userclasssetWHERE@.UserId = UserIdSELECT@.ErrorCode = @.@.ERROR,

@.RowCount = @.@.ROWCOUNT

IF( @.ErrorCode <> 0 )GOTOCleanupIF(@.RowCount <> 0)SELECT@.NumTablesDeletedFrom = @.NumTablesDeletedFrom + 1 hdhd

END

This code was then added to the function at the end which deletes the data from the aspnet_Users table when everything else has been removed

(@.TablesToDeleteFrom & 16) <> 0

AND

Now when I delete a user in the website admin tool, it "deletes" (with no error) the user from the list but doesnt actually physically delete it from the database.

Any ideas?

Problem fixed. The following code seemed to do the trick. I just needed to place it in the right point in the code which was just before the deletion of the UserId information in the membership table. Sorry to waste peoples time!

DELETE FROM dbo.userclassset WHERE @.UserId = UserId

Deleting Conflict tables

Hi,
While trying Merge replication for various publications, many conflict
tables were created in the publisher database. Even after deleting the
publications, they haven't gone off and neither can I delete them.
Could someone suggest, how to delete the conflict tables?
Thanks!
PS.
Sometimes the sp_removedbreplication will not remove them. If you ALTER the
tables, then you should be able to just drop them.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Thanks for the response. I tried deleting them through Enterprise
Manager and I got errors, but for some reason, I am able to drop the
conflict tables by dropping them from query analyzer.
PS.
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<g3Y0gn6UEHA.1996@.cpmsftngxa10.phx.gbl>...
> Sometimes the sp_removedbreplication will not remove them. If you ALTER the
> tables, then you should be able to just drop them.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
sql

Deleting and recreating database with same name

Question...
I created a database, but I want to totally blow that database away
and recreate it. I right-clicked on the DB and deleted it, no longer
there. Now when I go to recreate the database with the same name, I
get the following error:
Error 5170: Cannot create file 'C:\...MDF' because it already exists.
CREATE DATABASE failed. Some file names listed could not be created.
Check previous errors.
Okay, when I tell SQL I want to delete a database, I want it 100%
deleted. Apparently SQL's idea of Delete and mine are different, so
any suggestions on how I can totally remove this database? Since MS
is known to do plenty hidden the background, I don't want to just
delete the .MDF file, since I'm sure this will confuse SQL quite a
bit.
So back to the root question... how do I 100% delete a database where
I can create another DB with the same name?
Thanks,
Alex.You'll need to delete the MDF file it's complaining about from the OS.
HTH
Ryan Waight, MCDBA, MCSE
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.|||> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted.
This is what DROP DATABASE does, which is the command called from QA and EM. Unless you detach it,
of course.
> Apparently SQL's idea of Delete and mine are different
No, something strange must have happened.
Just try to delete the database files. If the database still exists in the eyes of SQL Server, then
the files are locked and you can't delete them.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.

Deleting all rows from table with RI

Many thanks for the responses however I didn't clarify one
important issue:
The tables in question were created without this option
yet have corresponding FK values in other tables. My job
is to delete all data in these tables. If I attempt to do:
DELETE FROM TABLE XYZ it fails complaining about
constraints. I can't find any examples of syntax such as
DELETE FROM TABLE XYZ WITH CASCADE which I thought would
be appropriate. How do I go about deleting all data
within these tables. I know I can simply delete all the
data in the referencing tables prior to doing the
reference tables or drop the constraints but I don't want
to do this if possible?
TIA
GRANT|
| Many thanks for the responses however I didn't clarify one
| important issue:
| The tables in question were created without this option
| yet have corresponding FK values in other tables. My job
| is to delete all data in these tables. If I attempt to do:
| DELETE FROM TABLE XYZ it fails complaining about
| constraints. I can't find any examples of syntax such as
| DELETE FROM TABLE XYZ WITH CASCADE which I thought would
| be appropriate. How do I go about deleting all data
| within these tables. I know I can simply delete all the
| data in the referencing tables prior to doing the
| reference tables or drop the constraints but I don't want
| to do this if possible?
--
Hi Grant,
I apologise if I misunderstand your real requirements. You said you want to
delete all data in these tables? Would it be feasible to just TRUNCATE
TABLE the foreign table before TRUNCATEing TABLE the primary table next?
TRUNCATE TABLE would be more efficient because it is not logged.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||You can't TRUNCATE a table if it is referenced by a foreign key, but you can
still use DELETE FROM <table name> if you clear out the referencing table
first.
The simplest thing to do is generate a database diagram in Enterprise
Manager, write down the tables that reference table XYZ (and the tables that
reference those tables etc), start with deleting from the last one and work
your way up.
--
Jacco Schalkwijk
SQL Server MVP
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:E25pB%23uvDHA.3532@.cpmsftngxa07.phx.gbl...
> |
> | Many thanks for the responses however I didn't clarify one
> | important issue:
> | The tables in question were created without this option
> | yet have corresponding FK values in other tables. My job
> | is to delete all data in these tables. If I attempt to do:
> | DELETE FROM TABLE XYZ it fails complaining about
> | constraints. I can't find any examples of syntax such as
> | DELETE FROM TABLE XYZ WITH CASCADE which I thought would
> | be appropriate. How do I go about deleting all data
> | within these tables. I know I can simply delete all the
> | data in the referencing tables prior to doing the
> | reference tables or drop the constraints but I don't want
> | to do this if possible?
> --
> Hi Grant,
> I apologise if I misunderstand your real requirements. You said you want
to
> delete all data in these tables? Would it be feasible to just TRUNCATE
> TABLE the foreign table before TRUNCATEing TABLE the primary table next?
> TRUNCATE TABLE would be more efficient because it is not logged.
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>|||Many thanks to both...that answers my question ie no it can't be done as a simple one-off delete statement. I guess that has its advantages in preventing mishaps!
Cheers
Grant
>--Original Message--
>You can't TRUNCATE a table if it is referenced by a foreign key, but you can
>still use DELETE FROM <table name> if you clear out the referencing table
>first.
>The simplest thing to do is generate a database diagram in Enterprise
>Manager, write down the tables that reference table XYZ (and the tables that
>reference those tables etc), start with deleting from the last one and work
>your way up.
>-- >Jacco Schalkwijk
>SQL Server MVP
>
>"Eric Cardenas" <ecard@.anonymous.com> wrote in message
>news:E25pB%23uvDHA.3532@.cpmsftngxa07.phx.gbl...
>> |
>> | Many thanks for the responses however I didn't clarify one
>> | important issue:
>> | The tables in question were created without this option
>> | yet have corresponding FK values in other tables. My job
>> | is to delete all data in these tables. If I attempt to do:
>> | DELETE FROM TABLE XYZ it fails complaining about
>> | constraints. I can't find any examples of syntax such as
>> | DELETE FROM TABLE XYZ WITH CASCADE which I thought would
>> | be appropriate. How do I go about deleting all data
>> | within these tables. I know I can simply delete all the
>> | data in the referencing tables prior to doing the
>> | reference tables or drop the constraints but I don't want
>> | to do this if possible?
>> --
>> Hi Grant,
>> I apologise if I misunderstand your real requirements. You said you want
>to
>> delete all data in these tables? Would it be feasible to just TRUNCATE
>> TABLE the foreign table before TRUNCATEing TABLE the primary table next?
>> TRUNCATE TABLE would be more efficient because it is not logged.
>> Hope this helps,
>> --
>> Eric C=E1rdenas
>> SQL Server support
>
>.
>|||If you change your foreign keys to - on delete cascade
you will be able to do it...
Bojidar Alexandrov

Deleting additional data files in a database primary filegroup

I created two additional data files in our database so that when the primary
location was full it would write to the added files. I though it would write
to the 2nd file and fill it before writing to the 3rd. It is writing to
both. Is it writing redundant data, or different data for better efficiency?
Also, how can I delete the 3rd data file without losing information? The
3rd location is not a RAID device, so I would like to keep as much
information in the data file in the 2nd location as possible. Any ideas or
suggestions?
kdirks
Rainbow Energy Marketing
If your files are part of the PRIMARY filegroup, SQL Server is free to
place data in either one. It doesn't write the same data to both.
Moving the second file to a RAID device makes good sense. See the
ALTER DATABASE topic in books online for a complete explanation
(including removing files, if you're worried about the extra). Here's
a quick example I used to move the tempDB data files off my primary
data array...
alter database tempdb modify file (name='tempdev',filename=
'e:\mssql\MSSQL$SQL2K\data\tempdb_data.mdf')
|||Oh, yes, I believe you will need to restart your sql server for the
actual file move to take place.

Deleting additional data files in a database primary filegroup

I created two additional data files in our database so that when the primary
location was full it would write to the added files. I though it would writ
e
to the 2nd file and fill it before writing to the 3rd. It is writing to
both. Is it writing redundant data, or different data for better efficiency
?
Also, how can I delete the 3rd data file without losing information? The
3rd location is not a RAID device, so I would like to keep as much
information in the data file in the 2nd location as possible. Any ideas or
suggestions?
--
kdirks
Rainbow Energy MarketingIf your files are part of the PRIMARY filegroup, SQL Server is free to
place data in either one. It doesn't write the same data to both.
Moving the second file to a RAID device makes good sense. See the
ALTER DATABASE topic in books online for a complete explanation
(including removing files, if you're worried about the extra). Here's
a quick example I used to move the tempDB data files off my primary
data array...
alter database tempdb modify file (name='tempdev',filename=
'e:\mssql\MSSQL$SQL2K\data\tempdb_data.mdf')|||Oh, yes, I believe you will need to restart your sql server for the
actual file move to take place.

Deleting additional data files in a database primary filegroup

I created two additional data files in our database so that when the primary
location was full it would write to the added files. I though it would write
to the 2nd file and fill it before writing to the 3rd. It is writing to
both. Is it writing redundant data, or different data for better efficiency?
Also, how can I delete the 3rd data file without losing information? The
3rd location is not a RAID device, so I would like to keep as much
information in the data file in the 2nd location as possible. Any ideas or
suggestions?
--
kdirks
Rainbow Energy MarketingIf your files are part of the PRIMARY filegroup, SQL Server is free to
place data in either one. It doesn't write the same data to both.
Moving the second file to a RAID device makes good sense. See the
ALTER DATABASE topic in books online for a complete explanation
(including removing files, if you're worried about the extra). Here's
a quick example I used to move the tempDB data files off my primary
data array...
alter database tempdb modify file (name='tempdev',filename='e:\mssql\MSSQL$SQL2K\data\tempdb_data.mdf')|||Oh, yes, I believe you will need to restart your sql server for the
actual file move to take place.

Deleting a record older then 10 minutes

Hey Guys,
I have been trying to work out how I would delete a record that was created more then 10 minutes ago.
I can use this to delete records older then a day.
DELETE FROM DownloadQueue WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < GETDATE() - 1
Just need something now that will do it for just 10 minutes.
Cheers.You could use this:
DELETE FROM DownloadQueue
WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < DATEADD(mi,-10,GETDATE())

deleting a backup device

I have created two backup devices for a database: 1 for the data and 1 for the logs. I back up transaction logs to the device over the course of a day and then do a differential backup for the data nightly.

Once a week, I do a full backup of the database. After the full backup is done, I want to delete the log file device since it's no longer needed. I've done it before, but haven't in a while and can't remember the syntax in the job step setup. Can someone please refresh me?

I know it's a CmdExec job, but the syntax of the step is eluding my memory.

Thanks for any help.just bumping for assistance.|||re-bumping... any assistance would be greatly appreciated.|||why delete the device? don't you want to leave it there to use it again. just set the backup log init to overwrite the previous log file.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
ChrisI have same problem. Did you ever get a reply? If so, what do I do? I hav
e
several orphan jobs I cannot delete. Thanks for any info.|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
Chris
I have same problem. Did you ever get a reply? If so, what do I do? I have
several orphan jobs I cannot delete. Thanks for any info.
|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
Chris
I have same problem. Did you ever get a reply? If so, what do I do? I have
several orphan jobs I cannot delete. Thanks for any info.
|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
ChrisI have same problem. Did you ever get a reply? If so, what do I do? I have
several orphan jobs I cannot delete. Thanks for any info.|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleted Stored Procedure Caused Backup Job to Failed

Hello,

I created a test stored procedure in MS SQL 2000. When the problem in my app was fixed, I deleted this test stored procedure. But the backup job thinks this procedure still exist and so backup job would failed. How can I fix this problem?

Thank in advance for your assistance.

Correct the 'backup job' so that it doesn't reference the deleted stored procedure.

I don't think that a database backup would fail because of the inclusion or deletion of a particular object, especially a stored procedure -unless that stored procedure is running the process.

|||

Arnie Rowland wrote:

I don't think that a database backup would fail because of the inclusion or deletion of a particular object, especially a stored procedure -unless that stored procedure is running the process.

Correct, a database backup doesn't care about the objects within the database.