Deleting SP syntax check please...

I'm trying to write a SP that will delete records from a few tables and then
count to see if any records are for some unknown reason, left over. The
goal is to have the SP return "0" upon successful deletions (used and called
from asp.net code). It it return a value greater than 0 than I know
something went wrong.
Pasted below is my attempt. I keep getting a syntax error near the word
"DELETE" in the first delete command.
What am I doing wrong? Before someone suggests I create relationships
between all these tables, the answer is I can't. I'm working with another
"old-school" developer who doesn't like them and likes to do all his
relationships "programmatically" thru code. My hands are tied so I need to
delete from each table separately.
THANKS!
CREATE PROCEDURE sp_DeletelApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE ID = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count(ID) As Applications FROM Applications WHERE ID = @.intApplicationID) +
(SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GOGroove
> DELETE FROM Applications WHERE ID = @.intApplicationID
Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
> I'm trying to write a SP that will delete records from a few tables and
> then count to see if any records are for some unknown reason, left over.
> The goal is to have the SP return "0" upon successful deletions (used and
> called from asp.net code). It it return a value greater than 0 than I
> know something went wrong.
> Pasted below is my attempt. I keep getting a syntax error near the word
> "DELETE" in the first delete command.
> What am I doing wrong? Before someone suggests I create relationships
> between all these tables, the answer is I can't. I'm working with another
> "old-school" developer who doesn't like them and likes to do all his
> relationships "programmatically" thru code. My hands are tied so I need
> to delete from each table separately.
> THANKS!
>
> CREATE PROCEDURE sp_DeletelApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE ID = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count(ID) As Applications FROM Applications WHERE ID => @.intApplicationID) +
> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>|||Thanks but no luck. I enclosed all my "ID's" in brackets and still the same
error when checking the syntax:
CREATE PROCEDURE spDeleteCapitalApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =@.intApplicationID) +
(SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used and
>> called from asp.net code). It it return a value greater than 0 than I
>> know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>|||:-))),Now I see , you have missed AS in the stored procedure
CREATE PROCEDURE spDeleteCapitalApplication
@.intApplicationID Integer
AS
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
> same error when checking the syntax:
>
> CREATE PROCEDURE spDeleteCapitalApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] => @.intApplicationID) +
> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used
>> and called from asp.net code). It it return a value greater than 0 than
>> I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>|||D'oh!
(slaps forehead)
Thanks!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8PDSaXXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> :-))),Now I see , you have missed AS in the stored procedure
> CREATE PROCEDURE spDeleteCapitalApplication
> @.intApplicationID Integer
> AS
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>
>
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
>> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
>> same error when checking the syntax:
>>
>> CREATE PROCEDURE spDeleteCapitalApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =>> @.intApplicationID) +
>> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left
>> over. The goal is to have the SP return "0" upon successful deletions
>> (used and called from asp.net code). It it return a value greater than
>> 0 than I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the
>> word "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do
>> all his relationships "programmatically" thru code. My hands are tied
>> so I need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>>
>

Deleting SP syntax check please...

I'm trying to write a SP that will delete records from a few tables and then
count to see if any records are for some unknown reason, left over. The
goal is to have the SP return "0" upon successful deletions (used and called
from asp.net code). It it return a value greater than 0 than I know
something went wrong.
Pasted below is my attempt. I keep getting a syntax error near the word
"DELETE" in the first delete command.
What am I doing wrong? Before someone suggests I create relationships
between all these tables, the answer is I can't. I'm working with another
"old-school" developer who doesn't like them and likes to do all his
relationships "programmatically" thru code. My hands are tied so I need to
delete from each table separately.
THANKS!
CREATE PROCEDURE sp_DeletelApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE ID = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count(ID) As Applications FROM Applications WHERE ID =
@.intApplicationID) +
(SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GOGroove
> DELETE FROM Applications WHERE ID = @.intApplicationID
Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
> I'm trying to write a SP that will delete records from a few tables and
> then count to see if any records are for some unknown reason, left over.
> The goal is to have the SP return "0" upon successful deletions (used and
> called from asp.net code). It it return a value greater than 0 than I
> know something went wrong.
> Pasted below is my attempt. I keep getting a syntax error near the word
> "DELETE" in the first delete command.
> What am I doing wrong? Before someone suggests I create relationships
> between all these tables, the answer is I can't. I'm working with another
> "old-school" developer who doesn't like them and likes to do all his
> relationships "programmatically" thru code. My hands are tied so I need
> to delete from each table separately.
> THANKS!
>
> CREATE PROCEDURE sp_DeletelApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE ID = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count(ID) As Applications FROM Applications WHERE ID =
> @.intApplicationID) +
> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>|||Thanks but no luck. I enclosed all my "ID's" in brackets and still the same
error when checking the syntax:
CREATE PROCEDURE spDeleteCapitalApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =
@.intApplicationID) +
(SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationI
D
=@.intApplicationID) +
(SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Groove
> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>|||:-))),Now I see , you have missed AS in the stored procedure
CREATE PROCEDURE spDeleteCapitalApplication
@.intApplicationID Integer
AS
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
> same error when checking the syntax:
>
> CREATE PROCEDURE spDeleteCapitalApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =
> @.intApplicationID) +
> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE Applicatio
nID
> =@.intApplicationID) +
> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>|||D'oh!
(slaps forehead)
Thanks!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8PDSaXXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> :-))),Now I see , you have missed AS in the stored procedure
> CREATE PROCEDURE spDeleteCapitalApplication
> @.intApplicationID Integer
> AS
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>
>
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
>

Deleting old backups

Hi,
I'd like to set up a job SQL Server agent which will delete backup files
older than several months once a backup has succeeded. How can I write a
step to do this?
Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
(them)
As you see I have no idea as to where I begin with this. Can it be done?
Thanks very much for any ideas on this
Ant
On Mar 9, 2:11 am, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I'd like to set up a job SQL Server agent which will delete backup files
> older than several months once a backup has succeeded. How can I write a
> step to do this?
> Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
> (them)
> As you see I have no idea as to where I begin with this. Can it be done?
> Thanks very much for any ideas on this
> Ant
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html
sql

Deleting from Stored Procedures

I am trying to write a stored procedure to delete something from a table but I keep getting sytax error.

This is the code I am using:

DELETE [tablename].[tablerow], [tablename].[tablerow]
FROM [tablename]
WHERE (((tablename.tablerow)="void"));

This works as an Access query but not as a stored procedure.DELETE
FROM [tablename]
WHERE tablename.tablerow)='void'
GO

Deleting Databases in SMO for C#

Hey all,

I was trying to write a C# function that would delete everything in the old database before doing anything new. The following is the code I tried to use for the deleting part. I tried "Killdatabase" but it doesn't seem to be working. Anyway, is there a way to reference the database in the server and send commands to it? The compiler gives me a error message saying that srv.Databases("Name") is wrong because I am using a property as a method.

p.s. Someone posted a similar question the other day and they solved it in visual basic by introducing a local variable. It didn't work for me on C#.

Many Thanks

Server srv = new Server(con);

srv.Databases("Name").Drop();

Database d = new Database(srv, "Name");

d.Create();

d.ExecuteNonQuery(command);

Ooops, I am sorry. I should have used [] instead of (). But I did have to use a local variable.

Thanks anyway.

Deleting columns from table

in sql server 2000, I have a table that I have to write a script for to delete several columns in this table. I am finding that I have to use alter or drop keywords or a combination of the two but not sure because I have not done this before. I am googling this but finding all kinds of other information that I dont' need to know.

I dont have rights on this table so I cannot do this manually. I have to create the script and send it on to someone else.

If anyone can provide a good script example that I can use to delete unwanted columns it would be a great thing. Thanks.

You might want to check the scripts section @. SQLServerCentral.com.Here's onethat might work for you.

Deleting all records in a table

I am trying to write a transact sql statment to delete all records in a table. What is wrong with the following statment?
DELETE * FROM tblPerson
GO
ThanksShould be just

DELETE FROM tblperson

unless you are using a WHERE CLAUSE such as

DELETE FROM tblperson WHERE id = 1|||Depending on the size of the table, you may also wish to consider truncating it. In general, a TRUNCATE statement will perform much faster than a DELETE. Try:

TRUNCATE TABLE tblPerson

Originally posted by Sia
I am trying to write a transact sql statment to delete all records in a table. What is wrong with the following statment?

DELETE * FROM tblPerson
GO

Thanks|||Originally posted by hmscott
Depending on the size of the table, you may also wish to consider truncating it. In general, a TRUNCATE statement will perform much faster than a DELETE. Try:

TRUNCATE TABLE tblPerson

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.

Delete Statement

If I have a table like below. Does anyone know how I write a delete statemen
t
to delete the Timestamps which are the earliest whenever a FromURN is the
same?
NO FromURN ToURN MoveDateMerged Timestamp
-- -- -- --
--
1 100 400 1982-06-15 00:00:00.000 2005-07-28
15:24:29.217
24 100 400 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
15:24:29.217
26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
Want to end up with this.
NO FromURN ToURN MoveDateMerged Timestamp
-- -- -- --
--
24 100 400 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
Thanks for everyone who has helped me since I started this on Monday. I
really do appreciate all the help. I'm finally getting there.Best thing would be to post some ddl and sample data int he group, but it
would be something like:
Delete from sometable
Where st.no =
(
--getting the most recent of them
Select TOP 1 no from sometable st
INNER JOIN
(
--Avaluating all with more than one presence of FromURN
Select FromURN
From Sometable
Group by FromURN
HAVING COUNT(*) > 1
) subquery
ON subquery.FromURN = st.FromURN
Where st.no = sometable.no
order by Timestamp
)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" wrote:

> If I have a table like below. Does anyone know how I write a delete statem
ent
> to delete the Timestamps which are the earliest whenever a FromURN is the
> same?
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 1 100 400 1982-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
>
> Want to end up with this.
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> Thanks for everyone who has helped me since I started this on Monday. I
> really do appreciate all the help. I'm finally getting there.|||Hi Stephen
You can try this as:
DELETE <TableName>
FROM <TableName>
INNER JOIN
(
select fromURN, max(TimeStamp) TimeStamp
from <TableName> group by fromURN
)NewTab
On NewTab.fromURN = <TableName>.fromURN AND NewTab.TimeStamp =
<TableName>.TimeStamp
Just replace <TableName> with the name of your table. Please let me know if
you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Stephen" wrote:

> If I have a table like below. Does anyone know how I write a delete statem
ent
> to delete the Timestamps which are the earliest whenever a FromURN is the
> same?
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 1 100 400 1982-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
>
> Want to end up with this.
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> Thanks for everyone who has helped me since I started this on Monday. I
> really do appreciate all the help. I'm finally getting there.|||On Fri, 29 Jul 2005 02:20:03 -0700, Stephen wrote:

>If I have a table like below. Does anyone know how I write a delete stateme
nt
>to delete the Timestamps which are the earliest whenever a FromURN is the
>same?
>NO FromURN ToURN MoveDateMerged Timestamp
>-- -- -- --
>--
>1 100 400 1982-06-15 00:00:00.000 2005-07-28
>15:24:29.217
>24 100 400 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
>15:24:29.217
>26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>
>Want to end up with this.
>NO FromURN ToURN MoveDateMerged Timestamp
>-- -- -- --
>--
>24 100 400 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>Thanks for everyone who has helped me since I started this on Monday. I
>really do appreciate all the help. I'm finally getting there.
Hi Stephen,
The solutions by Jens and Chandra will delete only the earliest row from
each set of duplicates. If three rows share the same FromURN, the
earliest is deleted and the other two are retained.
If you actually wanted to delete all but the latest row (i.e. in the
example above, delete the two earliest rows), try this:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.FromURN = MyTable.FromURN
AND b.Timestamp > MyTable.Timestamp)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Stephen,
You can try this
Delete from yourTable where NO =
(
select min(NO) FROM YourTable group by
fromURN,convert(varchar,timestamp,112)
)
I Hope this help
With warm regards
Jatinder Singh|||Hi Stephen,
Typo please replace = with in .
Sorry
With warm regards
Jatinder Singh

Delete record in sysdatabases

Hi,
in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
the DB compare the write: "(6.5 compatible)".
I don't know what this DB is. I want to remove it but by the right button of
the mouse I can't do anything.
I deleted the .mdf and .ldf files but the DB is not removed from the DB
list. It is present in the "sysdatabases" table of "master" database but I
can't remove the record.
It is possibible to delete the record from the table "sysdatabases"?
Thank you very much.Davide Franzoni wrote:
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button
of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
Have you tried dropping it using the DROP DATABASE command?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||sysdatabases is one of the critical table that sql server uses. it is not a
recommended to issue any DML command directly on it. So my answer is NOT TO
delete
vt
"Davide Franzoni" <DavideFranzoni@.discussions.microsoft.com> wrote in
message news:E4864CFC-59FB-4F52-AFD3-3E4FB46ED5FF@.microsoft.com...
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button
> of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.|||or try
sp_detach_db
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455491C7.8090402@.realsqlguy.com...
> Davide Franzoni wrote:
> Have you tried dropping it using the DROP DATABASE command?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Delete record in sysdatabases

Hi,
in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
the DB compare the write: "(6.5 compatible)".
I don't know what this DB is. I want to remove it but by the right button of
the mouse I can't do anything.
I deleted the .mdf and .ldf files but the DB is not removed from the DB
list. It is present in the "sysdatabases" table of "master" database but I
can't remove the record.
It is possibible to delete the record from the table "sysdatabases"?
Thank you very much.
Davide Franzoni wrote:
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
Have you tried dropping it using the DROP DATABASE command?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||sysdatabases is one of the critical table that sql server uses. it is not a
recommended to issue any DML command directly on it. So my answer is NOT TO
delete
vt
"Davide Franzoni" <DavideFranzoni@.discussions.microsoft.com> wrote in
message news:E4864CFC-59FB-4F52-AFD3-3E4FB46ED5FF@.microsoft.com...
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button
> of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
|||or try
sp_detach_db
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455491C7.8090402@.realsqlguy.com...
> Davide Franzoni wrote:
> Have you tried dropping it using the DROP DATABASE command?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Delete record in sysdatabases

Hi,
in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
the DB compare the write: "(6.5 compatible)".
I don't know what this DB is. I want to remove it but by the right button of
the mouse I can't do anything.
I deleted the .mdf and .ldf files but the DB is not removed from the DB
list. It is present in the "sysdatabases" table of "master" database but I
can't remove the record.
It is possibible to delete the record from the table "sysdatabases"?
Thank you very much.Davide Franzoni wrote:
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
Have you tried dropping it using the DROP DATABASE command?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||sysdatabases is one of the critical table that sql server uses. it is not a
recommended to issue any DML command directly on it. So my answer is NOT TO
delete
vt
"Davide Franzoni" <DavideFranzoni@.discussions.microsoft.com> wrote in
message news:E4864CFC-59FB-4F52-AFD3-3E4FB46ED5FF@.microsoft.com...
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button
> of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.|||or try
sp_detach_db
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455491C7.8090402@.realsqlguy.com...
> Davide Franzoni wrote:
>> Hi,
>> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
>> the DB compare the write: "(6.5 compatible)". I don't know what this DB
>> is. I want to remove it but by the right button of the mouse I can't do
>> anything.
>> I deleted the .mdf and .ldf files but the DB is not removed from the DB
>> list. It is present in the "sysdatabases" table of "master" database but
>> I can't remove the record.
>> It is possibible to delete the record from the table "sysdatabases"?
>> Thank you very much.
> Have you tried dropping it using the DROP DATABASE command?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

delete multi records

hi,
I found there are few records have duplicate key in my databaes,
for example, title table, have server same title, how to write a query, can
delete teh duplicate entries but only keep one. how to do this? Thanks in
advance.By what criteria would you want to delete rows from this table? Just because
multiple rows have the same TitleName, that doesn't mean that the entire row
is duplicated. You didn't say what the primary key is. If the table does not
have one, then implement that into your design after cleaning up the data.
The following query will return all rows from the Title table that have a
TitleName that is duplicated in another row. Once done, you can review the
list and identify specific rows to delete.
select
*
from
Titles
where
TitleName in
(
select
TitleName
from
Titles
group by
TitleName
having
count(*) > 1
)
"js" <js@.someone@.hotmail.com> wrote in message
news:Ow%23cX8VOGHA.1028@.TK2MSFTNGP11.phx.gbl...
> hi,
> I found there are few records have duplicate key in my databaes,
> for example, title table, have server same title, how to write a query,
> can delete teh duplicate entries but only keep one. how to do this? Thanks
> in advance.
>

Delete more than one table

Is it possible to write SQL to delete data in more than one tables?
Something like this
Delete * from tableA and tableC and tableB where dat<>date()No, a SQL DELETE statement affects only one table - unless your particular DBMS does things differently.|||Thanks you very much. :)

Delete matched query

I'm working with a legacy application that stores its data in a SQL Server 2
K
database. I want to write a delete query to delete items from table A, wher
e
there is no match in table B, where the match is based on matches of the two
PK fields.
I tried the following, but got an error: Incorrect syntax near the keyword
'LEFT'
DELETE FROM A
LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
Any help would be greatly appreciated.
DaleHow about this?
DELETE A
FROM A
INNER JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL|||Try
DELETE A
From A LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
the Tables are actually name dTableA and TableB, then the First line has t
ouse the Alias, not the actual Table Name,. i.e.,
DELETE A
From TableA A Left Join TableB B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
A Much clearer way to code this is to use SQL that mirrors exactly what you
want
Delete TableA
Where Not Exists
(Select * From TableB
Where PK = TableA.PK)
"Dale Fye" wrote:

> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||I meant
Delete TableA
Where Not Exists
(Select * From TableB
Where ExerciseID = TableA.ExerciseID
And UserID = TableA.UserID)
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>|||Try this
Delete From A
Where Not Exists(
Select * From B Where B.Col1=A.Col1 And B.Col2=A.Col2
)
Dmitriy
"Dale Fye" <dale.fye@.nospam.com> wrote in message
news:B16B3312-7EDD-4D85-A2D0-7806F2064BEE@.microsoft.com...
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A,
where
> there is no match in table B, where the match is based on matches of the
two
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||Dale Fye wrote:
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Try this:
DELETE FROM A
WHERE NOT EXISTS (SELECT * FROM B
WHERE B.ExerciseID = A.ExerciseID
AND B.UserID = A.UserID)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjX6lIechKqOuFEgEQIHQQCfe67DnoBuMAKw
lKtCX8+H7Wd9ANAAmwS2
sbyoMnwLgSk2DmyMUtxtgESf
=hYSb
--END PGP SIGNATURE--|||Thanks to all who responded. I've been working in Access so long, I forgot
about Exists and Not Exists.
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>