Deleting rows

Hi there,
can you help me?
I'm trying to delete rows in a single table.
When i exec select , rows returne are 6098 (right number)
SELECT distinct FLD_USERID FROM
(
SELECT distinct FLD_USERID, MIN(FLD_LEVEL)as a
FROM TBL_CHARACTERbackup
GROUP BY FLD_USERID
HAVING (COUNT(*) > 2)
) f
When i try to delete that records rows deleted are 13708 (wrong)
DELETE TBL_CHARACTERbackup WHERE FLD_USERID IN
(
SELECT distinct FLD_USERID FROM
(
SELECT distinct FLD_USERID, MIN(FLD_LEVEL)as a
FROM TBL_CHARACTERbackup
GROUP BY FLD_USERID
HAVING (COUNT(*) > 2)
) f
)
Donno why, please help
TksThe DELETE statement will delete every row in your table which has a
FLD_USERID value that is duplicated, whereas the SELECT will only show
ONE row per FLD_USERID.
Possibly this is what you intended:
DELETE FROM TBL_CHARACTERbackup
WHERE EXISTS
(SELECT *
FROM TBL_CHARACTERbackup AS T
WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
AND T.fld_level < TBL_CHARACTERbackup.fld_level)
but that's only a guess.
David Portas
SQL Server MVP
--|||
"David Portas" ha scritto:

> The DELETE statement will delete every row in your table which has a
> FLD_USERID value that is duplicated, whereas the SELECT will only show
> ONE row per FLD_USERID.
> Possibly this is what you intended:
> DELETE FROM TBL_CHARACTERbackup
> WHERE EXISTS
> (SELECT *
> FROM TBL_CHARACTERbackup AS T
> WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
> AND T.fld_level < TBL_CHARACTERbackup.fld_level)
> but that's only a guess.
> --
> David Portas
> SQL Server MVP
Ok what i need is to delete all fld_userid with lower fld_level
just to have
only one records for each fld_userid with highest fld_level
How can i do?
Please help.
> --
>|||You haven't told us what the key of your table is. Assuming
(fld_userid, fld_level) is unique then just turn around the sign in my
first attempt:
DELETE FROM TBL_CHARACTERbackup
WHERE EXISTS
(SELECT *
FROM TBL_CHARACTERbackup AS T
WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
AND T.fld_level > TBL_CHARACTERbackup.fld_level)
(still untested)
If (fld_userid, fld_level) isn't unique that means there may be more
than one row with the same maximum value of fld_level so you will have
to add some other criteria to the WHERE clause. If you need more help
then the following article explains the best way to describe the
details of your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
Do you have any say over naming conventions in your design? Why the
useless tbl_ and fld_ prefixes? They tell us nothing (we know what
tables and columns are) and just make the names harder to read. Also,
the usual convention is to use the term "column" not "field" when
referring to relational data. Some people find this distinction more
important than others and associate the two terms with totally
different concepts but almost everyone loathes to see prefixes on
column names.
Hope this helps.
David Portas
SQL Server MVP
--|||
"David Portas" wrote:

> You haven't told us what the key of your table is. Assuming
> (fld_userid, fld_level) is unique then just turn around the sign in my
> first attempt:
> DELETE FROM TBL_CHARACTERbackup
> WHERE EXISTS
> (SELECT *
> FROM TBL_CHARACTERbackup AS T
> WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
> AND T.fld_level > TBL_CHARACTERbackup.fld_level)
> (still untested)
> If (fld_userid, fld_level) isn't unique that means there may be more
> than one row with the same maximum value of fld_level so you will have
> to add some other criteria to the WHERE clause. If you need more help
> then the following article explains the best way to describe the
> details of your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Do you have any say over naming conventions in your design? Why the
> useless tbl_ and fld_ prefixes? They tell us nothing (we know what
> tables and columns are) and just make the names harder to read. Also,
> the usual convention is to use the term "column" not "field" when
> referring to relational data. Some people find this distinction more
> important than others and associate the two terms with totally
> different concepts but almost everyone loathes to see prefixes on
> column names.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
I'll explain :
each row_USERID corrisponds more records
ex
FLD_USERID FLD_LEVEL CHARACTERS
daniele 1 PIPPO
daniele 27 PLUTO
daniele 37 Paperino
I must keep in table
FLD_USERID FLD_LEVEL CHARACTERS
daniele 27 PLUTO
daniele 37 Paperino
That means only 2 records for FLD_USERID wiyh 2 higher FLD_LEVEL levels
Please help
> --
>|||Try:
DELETE FROM TBL_CHARACTERbackup
WHERE fld_level =
(SELECT MIN(fld_level)
FROM TBL_CHARACTERbackup AS T
WHERE fld_userid = TBL_CHARACTERbackup.fld_userid)
David Portas
SQL Server MVP
--

Deleting records from multiple tables in SQL server

I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.

I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):

Table 1: Classes --Columns: ClassID, ClassName

Table 2: Roster--Columns: ClassID, StudentID, Student Name

Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName

Table 4: Scores--StudentID, AssignmentID, Score

What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.

What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.

The stored procedure I created was

DELETE FROM Classes WHERE ClassID=@.classid

I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:

The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.

What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?

I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.

Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.

Further, is there something else I need to do besides assigning primary keys and foreign keys?

WHen you create a foreign key, there are some additional options you have to set to tell it to do the cascade delete.

If you are creating the foreign key through T-SQL you must append the ON DELETE CASCADE option to the foreign key:

Code Snippet

ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> FOREIGN KEY (<columnname(s)>)
REFERENCES <referencedtablename> (<columnname(s)>)

ON DELETE CASCADE;

If using SSMS, modify the table and go to where you created the relationship. If you look around in there you should see an option to set the "Delete Rule" Set that to CASCADE.

|||

OK, the concept of deleting rows from multiple tables in a single delete statement cannot be done in just that statement. There is the concept of triggers on the tables that do deletes in a cascading style, but I would not recommend you do it that way for sake of control of the actions of the data.

But I udnerstand what you want to do, and the best way to explain it is this:

Say you have these tables and each one has a relationship up the chain. If you have Classes, and they are on Rosters, and Assignments are given to Classes and Scores have Students and Assignments you have to see which is the last one in the chain.

So in your case you have Classes that is the base and then you have Classes in Rosters (this is the next level) and you have Classes in Assignments (same level as Rosters).

So you have a relationship like

Classes - ClassID

|__Roster - ClassId

|__Assignments - ClassId

|Scores - AssignmentId

So say you no longer had ClassId 3 and you wanted to just get rid of all the records that are associated with ClassId 3. Here are the steps that you would need to take. You would delete in the reverse order than you inserted.

So in this case, you would want to use the ClassId = 3 to get all the assignments that have that ClassId and delete the Scores that have the AssignmentId and then delete the Assignments with the ClassId = 3

Then you would delete the Rosters with the ClassId = 3 and then finally delete the Classes with ClassId = 3

SQL:

DELETE Scores

FROM Assignments A

INNER JOIN Scores S ON A.AssignmentId = S.AssignmentId

WHERE A.ClassId = 3

DELETE Assignments

WHERE ClassId = 3

DELETE Roster

WHERE ClassId = 3

DELETE Classes

WHERE ClassId = 3

So you really just need to delete the Foreign Key tables records with the Primary Key record in it first and then delete the Primary Key records in the Primary table or Base table last.

HTH.

Ben Miller

|||I disagree with you an the answer that this cannot be done within one statement as the suggestions from Andrew about Cascading deletes should solve the problem, if the architecture is appropiate for the original poster.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Ben

I also disagree with your presentation. With a properly established set of relationships, CASCADE DELETE works wonderfully. The PK-FK relationships must be properly set-up, and there cannot be any circular relationships.

So you have a relationship like

Classes - ClassID

|__Roster - ClassId

|__Assignments - ClassId

|Scores - AssignmentId

In this situation, a deletion on [Classes] will remove related data from all lower tables. Deleting [Assignments] will also delete related data from [Scores]. A deletion on [Roster] or [Scores] will only affect those tables.

Deleting Records From A Table

I AM TRYING TO DELETE RECORDS FROM THE TABLE.

PLEASE CORRECT ME WITH THE SYNTAX

DELETE From DPT_NEW_BINS WHERE (
Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_Temp_NEW_BINS o
WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)

THE SELECT QUERY WORKS FOR ME BUT ONCE I ADD THE WHERE CLAUSE IT GIVES ME AN ERROR...CAN ANYBODY HELP ME OUT?

Quote:

Originally Posted by desirocks

I AM TRYING TO DELETE RECORDS FROM THE TABLE.

PLEASE CORRECT ME WITH THE SYNTAX

DELETE From DPT_NEW_BINS WHERE (
Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_Temp_NEW_BINS o
WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)

THE SELECT QUERY WORKS FOR ME BUT ONCE I ADD THE WHERE CLAUSE IT GIVES ME AN ERROR...CAN ANYBODY HELP ME OUT?

Please check the 'Closing Brackets' in ' Where ' to your delete query you haven't mention any condition...
Tell me if that works|||

Quote:

Originally Posted by desirocks

I AM TRYING TO DELETE RECORDS FROM THE TABLE.

PLEASE CORRECT ME WITH THE SYNTAX

DELETE From DPT_NEW_BINS WHERE (
Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_Temp_NEW_BINS o
WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)

THE SELECT QUERY WORKS FOR ME BUT ONCE I ADD THE WHERE CLAUSE IT GIVES ME AN ERROR...CAN ANYBODY HELP ME OUT?

Please POST your exact ERROR for my reference. The syntax of the DELETE statement is WRONG. Check below syntax:

DELETE FROM <table_name> WHERE <column_name> IN (SELECT <column_name FROM <table_name>)

Deleting more duplicates

Hello,
I have a stored procedure that deletes duplicates
in one table:

....
AS
BEGIN
DELETE FROM mytable
WHERE Id IN(
SELECT Max(id)
from mytable
group by date, idsens
having count(*) >1
)
END

sometimes it happens that I have >2 rows with duplicated values.
How can I write a new stored procedure that delete all rows with
duplicated infomrations (leaving only one row with those values)?

Thanks
M.A.On Tue, 25 Jul 2006 10:36:10 GMT, Maury wrote:

Quote:

Originally Posted by

>Hello,
>I have a stored procedure that deletes duplicates
>in one table:
>
>...
>AS
>BEGIN
>DELETE FROM mytable
>WHERE Id IN(
>SELECT Max(id)
>from mytable
>group by date, idsens
>having count(*) >1
>)
>END
>
>sometimes it happens that I have >2 rows with duplicated values.
>How can I write a new stored procedure that delete all rows with
>duplicated infomrations (leaving only one row with those values)?

Hi Maury,

For SQL Server 2005:

WITH DeDupe (Id, rn)
AS (SELECT Id, ROW_NUMBER() OVER (PARTITION BY date, idsens ORDER BY Id)
FROM mytable)
DELETE DeDupe
WHERE rn 1;

For all versions of SQL Server:

DELETE FROM mytable
WHERE EXISTS
(SELECT *
FROM mytable AS m2
WHERE m2.date = mytable.date
AND m2.idsens = mytable.idsens
AND m2.Id < mytable.Id);

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis ha scritto:

Quote:

Originally Posted by

WITH DeDupe (Id, rn)
AS (SELECT Id, ROW_NUMBER() OVER (PARTITION BY date, idsens ORDER BY Id)
FROM mytable)
DELETE DeDupe
WHERE rn 1;

This is very very interesting (I didn't know these commands)
I have to search a good reference
or stored procedure language manual
for sql server 2005
some hints?
thanks!!!!
M.A.|||Maury (maurizio.alberti_TOGLI_@.gmail.com) writes:

Quote:

Originally Posted by

This is very very interesting (I didn't know these commands)
I have to search a good reference
or stored procedure language manual
for sql server 2005
some hints?

Books Online. Ships with SQL Server. Update available from the link below.

--
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

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 data using table prefix

I can run a select to retrieve data using a prefix 'a' for the specific table involved. However when I try to run a delete using the same criteria it fails telling me

Msg 102, Level 15, State 1,.......Line 1

Incorrect syntax near 'a'

The Select statement looks like:

select count(*) from schema.table a where a.customer_id=1234

The Delete looks like:

delete from schema.table a where a.customer_id=1234

What am I doing wrong here? and how can I prefix the table, because the command I want to run is much more complicated than the example above and it needs the prefix

According to BOL there is no alias for delete statement

Thanks

|||

You can use the TSQL extension for the DELETE statement like:

delete from schema.table

from schema.table a

where a.customer_id=1234

Deleted Log file

Hi,
I have a database that use two log files. I deleted one of the logs going to
database/properties/files select the log file, and remove.
Everything goes well, but now when I go to database/properties/files I still
see the tow log files (the folder and file for one of the logs doesn’t exi
st
anymore).
Any idea how to resolve this?
ThanksHi
You don't say which version of SQL Server you are using? Have you tried
sp_helpfile to see what that gives and the
ALTER DATABASE REMOVE FILE logical_file_name
command to see if it can be removed that way?
John
"Tecnica" wrote:

> Hi,
> I have a database that use two log files. I deleted one of the logs going
to
> database/properties/files select the log file, and remove.
> Everything goes well, but now when I go to database/properties/files I sti
ll
> see the tow log files (the folder and file for one of the logs doesn’t e
xist
> anymore).
> Any idea how to resolve this?
> Thanks
>|||Hi John,
in fact i didn′t mencioned that the SQL is version 2005.
The ALTER DATABASE REMOVE FILE logical_file_name doesn′t work because
phisicaly the file does not exist.
If you run SELECT * FROM sys.database_files you see the file mencioned in
one row, but i don′t know if i can modify this system table.
Rui
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't say which version of SQL Server you are using? Have you tried
> sp_helpfile to see what that gives and the
> ALTER DATABASE REMOVE FILE logical_file_name
> command to see if it can be removed that way?
> John
>
> "Tecnica" wrote:
>|||Hi
You should not need to modify system catalogs. Do you have the number/text
for the error for the ALTER DATABASE command. Are you using SP1?
John
"Tecnica" wrote:
[vbcol=seagreen]
> Hi John,
> in fact i didn′t mencioned that the SQL is version 2005.
> The ALTER DATABASE REMOVE FILE logical_file_name doesn′t work because
> phisicaly the file does not exist.
> If you run SELECT * FROM sys.database_files you see the file mencioned in
> one row, but i don′t know if i can modify this system table.
> Rui
> "John Bell" wrote:
>|||Hi,
My understanding of your issue is that:
Your database had two log files. You manually deleted one of the log files
in SQL Server Management Studio. However when you viewed the database
properties again, you found that the log file was still there.
If I have misunderstood, please let me know.
I performed a test in SQL Server 2005 Enterprise Edition SP1, but
unfortunately I couldn't reproduce your issue. Everything works fine at my
side. I created a database called TMLogDB which has two log files:
TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
manually removed the TMLogDB_log1. Then I clicked the Properties of the
database and found that the log file had been removed.
For further research, I recommend that:
1. Check if this issue occurs only on this database. Please create a test
dabase and see if the same issue exists on it;
2. Completely backup your database, then try using sp_detach_db to detach
the database and sp_attach_db to attach the database with the current log
file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
Server Books Online.
3. Install SQL Server 2005 SP1 if it was not installed on your computer.
4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Charles
tanks for the help. Detach and attach database has solve the problem.
Regards,
Rui Reis
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> Your database had two log files. You manually deleted one of the log files
> in SQL Server Management Studio. However when you viewed the database
> properties again, you found that the log file was still there.
> If I have misunderstood, please let me know.
> I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> unfortunately I couldn't reproduce your issue. Everything works fine at my
> side. I created a database called TMLogDB which has two log files:
> TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> manually removed the TMLogDB_log1. Then I clicked the Properties of the
> database and found that the log file had been removed.
> For further research, I recommend that:
> 1. Check if this issue occurs only on this database. Please create a test
> dabase and see if the same issue exists on it;
> 2. Completely backup your database, then try using sp_detach_db to detach
> the database and sp_attach_db to attach the database with the current log
> file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> Server Books Online.
> 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> 4. If this issue perists, please mail me (changliw@.microsoft.com) sql erro
r
> logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Sincerely yours,
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi,
Appreciate your update and response. I am glad to hear that the problem has
been fixed. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi
Can you let us know whether this is SP1 or not?
Thanks
John
"Tecnica" wrote:
[vbcol=seagreen]
> Hi Charles
> tanks for the help. Detach and attach database has solve the problem.
> Regards,
> Rui Reis
> "Charles Wang[MSFT]" wrote:
>|||Hi
no, it is not SP1 because i already intaled some time ago. It was detach and
atach that solve the problem.
Thanks again,
Rui Reis
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Can you let us know whether this is SP1 or not?
> Thanks
> John
> "Tecnica" wrote:
>|||Hi
I would strongly recommend that you upgrade to SP1 which does not seem to
have this issue.
John
"Tecnica" wrote:
[vbcol=seagreen]
> Hi
> no, it is not SP1 because i already intaled some time ago. It was detach a
nd
> atach that solve the problem.
> Thanks again,
> Rui Reis
> "John Bell" wrote:
>

Deleted Log file

Hi,
I have a database that use two log files. I deleted one of the logs going to
database/properties/files select the log file, and remove.
Everything goes well, but now when I go to database/properties/files I still
see the tow log files (the folder and file for one of the logs doesnâ't exist
anymore).
Any idea how to resolve this?
ThanksHi
You don't say which version of SQL Server you are using? Have you tried
sp_helpfile to see what that gives and the
ALTER DATABASE REMOVE FILE logical_file_name
command to see if it can be removed that way?
John
"Tecnica" wrote:
> Hi,
> I have a database that use two log files. I deleted one of the logs going to
> database/properties/files select the log file, and remove.
> Everything goes well, but now when I go to database/properties/files I still
> see the tow log files (the folder and file for one of the logs doesnâ't exist
> anymore).
> Any idea how to resolve this?
> Thanks
>|||Hi John,
in fact i didn´t mencioned that the SQL is version 2005.
The ALTER DATABASE REMOVE FILE logical_file_name doesn´t work because
phisicaly the file does not exist.
If you run SELECT * FROM sys.database_files you see the file mencioned in
one row, but i don´t know if i can modify this system table.
Rui
"John Bell" wrote:
> Hi
> You don't say which version of SQL Server you are using? Have you tried
> sp_helpfile to see what that gives and the
> ALTER DATABASE REMOVE FILE logical_file_name
> command to see if it can be removed that way?
> John
>
> "Tecnica" wrote:
> > Hi,
> > I have a database that use two log files. I deleted one of the logs going to
> > database/properties/files select the log file, and remove.
> > Everything goes well, but now when I go to database/properties/files I still
> > see the tow log files (the folder and file for one of the logs doesnâ't exist
> > anymore).
> > Any idea how to resolve this?
> >
> > Thanks
> >|||Hi
You should not need to modify system catalogs. Do you have the number/text
for the error for the ALTER DATABASE command. Are you using SP1?
John
"Tecnica" wrote:
> Hi John,
> in fact i didn´t mencioned that the SQL is version 2005.
> The ALTER DATABASE REMOVE FILE logical_file_name doesn´t work because
> phisicaly the file does not exist.
> If you run SELECT * FROM sys.database_files you see the file mencioned in
> one row, but i don´t know if i can modify this system table.
> Rui
> "John Bell" wrote:
> > Hi
> >
> > You don't say which version of SQL Server you are using? Have you tried
> > sp_helpfile to see what that gives and the
> > ALTER DATABASE REMOVE FILE logical_file_name
> > command to see if it can be removed that way?
> >
> > John
> >
> >
> > "Tecnica" wrote:
> >
> > > Hi,
> > > I have a database that use two log files. I deleted one of the logs going to
> > > database/properties/files select the log file, and remove.
> > > Everything goes well, but now when I go to database/properties/files I still
> > > see the tow log files (the folder and file for one of the logs doesnâ't exist
> > > anymore).
> > > Any idea how to resolve this?
> > >
> > > Thanks
> > >|||Hi,
My understanding of your issue is that:
Your database had two log files. You manually deleted one of the log files
in SQL Server Management Studio. However when you viewed the database
properties again, you found that the log file was still there.
If I have misunderstood, please let me know.
I performed a test in SQL Server 2005 Enterprise Edition SP1, but
unfortunately I couldn't reproduce your issue. Everything works fine at my
side. I created a database called TMLogDB which has two log files:
TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
manually removed the TMLogDB_log1. Then I clicked the Properties of the
database and found that the log file had been removed.
For further research, I recommend that:
1. Check if this issue occurs only on this database. Please create a test
dabase and see if the same issue exists on it;
2. Completely backup your database, then try using sp_detach_db to detach
the database and sp_attach_db to attach the database with the current log
file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
Server Books Online.
3. Install SQL Server 2005 SP1 if it was not installed on your computer.
4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Charles
tanks for the help. Detach and attach database has solve the problem.
Regards,
Rui Reis
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your database had two log files. You manually deleted one of the log files
> in SQL Server Management Studio. However when you viewed the database
> properties again, you found that the log file was still there.
> If I have misunderstood, please let me know.
> I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> unfortunately I couldn't reproduce your issue. Everything works fine at my
> side. I created a database called TMLogDB which has two log files:
> TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> manually removed the TMLogDB_log1. Then I clicked the Properties of the
> database and found that the log file had been removed.
> For further research, I recommend that:
> 1. Check if this issue occurs only on this database. Please create a test
> dabase and see if the same issue exists on it;
> 2. Completely backup your database, then try using sp_detach_db to detach
> the database and sp_attach_db to attach the database with the current log
> file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> Server Books Online.
> 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Sincerely yours,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hi,
Appreciate your update and response. I am glad to hear that the problem has
been fixed. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi
Can you let us know whether this is SP1 or not?
Thanks
John
"Tecnica" wrote:
> Hi Charles
> tanks for the help. Detach and attach database has solve the problem.
> Regards,
> Rui Reis
> "Charles Wang[MSFT]" wrote:
> > Hi,
> > My understanding of your issue is that:
> > Your database had two log files. You manually deleted one of the log files
> > in SQL Server Management Studio. However when you viewed the database
> > properties again, you found that the log file was still there.
> > If I have misunderstood, please let me know.
> >
> > I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> > unfortunately I couldn't reproduce your issue. Everything works fine at my
> > side. I created a database called TMLogDB which has two log files:
> > TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> > manually removed the TMLogDB_log1. Then I clicked the Properties of the
> > database and found that the log file had been removed.
> >
> > For further research, I recommend that:
> > 1. Check if this issue occurs only on this database. Please create a test
> > dabase and see if the same issue exists on it;
> > 2. Completely backup your database, then try using sp_detach_db to detach
> > the database and sp_attach_db to attach the database with the current log
> > file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> > Server Books Online.
> > 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> > 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> > logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> >
> > If you have any other questions or concerns, please feel free to let me
> > know. It's my pleasure to be of assistance.
> >
> > Sincerely yours,
> > Charles Wang
> > Microsoft Online Community Support
> >
> > ======================================================> > When responding to posts, please "Reply to Group" via
> > your newsreader so that others may learn and benefit
> > from this issue.
> > ======================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > ======================================================> >
> >
> >
> >|||Hi
no, it is not SP1 because i already intaled some time ago. It was detach and
atach that solve the problem.
Thanks again,
Rui Reis
"John Bell" wrote:
> Hi
> Can you let us know whether this is SP1 or not?
> Thanks
> John
> "Tecnica" wrote:
> > Hi Charles
> >
> > tanks for the help. Detach and attach database has solve the problem.
> >
> > Regards,
> > Rui Reis
> >
> > "Charles Wang[MSFT]" wrote:
> >
> > > Hi,
> > > My understanding of your issue is that:
> > > Your database had two log files. You manually deleted one of the log files
> > > in SQL Server Management Studio. However when you viewed the database
> > > properties again, you found that the log file was still there.
> > > If I have misunderstood, please let me know.
> > >
> > > I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> > > unfortunately I couldn't reproduce your issue. Everything works fine at my
> > > side. I created a database called TMLogDB which has two log files:
> > > TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> > > manually removed the TMLogDB_log1. Then I clicked the Properties of the
> > > database and found that the log file had been removed.
> > >
> > > For further research, I recommend that:
> > > 1. Check if this issue occurs only on this database. Please create a test
> > > dabase and see if the same issue exists on it;
> > > 2. Completely backup your database, then try using sp_detach_db to detach
> > > the database and sp_attach_db to attach the database with the current log
> > > file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> > > Server Books Online.
> > > 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> > > 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> > > logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> > >
> > > If you have any other questions or concerns, please feel free to let me
> > > know. It's my pleasure to be of assistance.
> > >
> > > Sincerely yours,
> > > Charles Wang
> > > Microsoft Online Community Support
> > >
> > > ======================================================> > > When responding to posts, please "Reply to Group" via
> > > your newsreader so that others may learn and benefit
> > > from this issue.
> > > ======================================================> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > ======================================================> > >
> > >
> > >
> > >|||Hi
I would strongly recommend that you upgrade to SP1 which does not seem to
have this issue.
John
"Tecnica" wrote:
> Hi
> no, it is not SP1 because i already intaled some time ago. It was detach and
> atach that solve the problem.
> Thanks again,
> Rui Reis
> "John Bell" wrote:
> > Hi
> >
> > Can you let us know whether this is SP1 or not?
> >
> > Thanks
> >
> > John
> >
> > "Tecnica" wrote:
> >
> > > Hi Charles
> > >
> > > tanks for the help. Detach and attach database has solve the problem.
> > >
> > > Regards,
> > > Rui Reis
> > >
> > > "Charles Wang[MSFT]" wrote:
> > >
> > > > Hi,
> > > > My understanding of your issue is that:
> > > > Your database had two log files. You manually deleted one of the log files
> > > > in SQL Server Management Studio. However when you viewed the database
> > > > properties again, you found that the log file was still there.
> > > > If I have misunderstood, please let me know.
> > > >
> > > > I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> > > > unfortunately I couldn't reproduce your issue. Everything works fine at my
> > > > side. I created a database called TMLogDB which has two log files:
> > > > TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> > > > manually removed the TMLogDB_log1. Then I clicked the Properties of the
> > > > database and found that the log file had been removed.
> > > >
> > > > For further research, I recommend that:
> > > > 1. Check if this issue occurs only on this database. Please create a test
> > > > dabase and see if the same issue exists on it;
> > > > 2. Completely backup your database, then try using sp_detach_db to detach
> > > > the database and sp_attach_db to attach the database with the current log
> > > > file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> > > > Server Books Online.
> > > > 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> > > > 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> > > > logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> > > >
> > > > If you have any other questions or concerns, please feel free to let me
> > > > know. It's my pleasure to be of assistance.
> > > >
> > > > Sincerely yours,
> > > > Charles Wang
> > > > Microsoft Online Community Support
> > > >
> > > > ======================================================> > > > When responding to posts, please "Reply to Group" via
> > > > your newsreader so that others may learn and benefit
> > > > from this issue.
> > > > ======================================================> > > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > > ======================================================> > > >
> > > >
> > > >
> > > >|||Hi
I didnâ't explain well. I have already upgrade to SP1 some time ago (I donâ't
remember when).
Thanks
"John Bell" wrote:
> Hi
> I would strongly recommend that you upgrade to SP1 which does not seem to
> have this issue.
> John
> "Tecnica" wrote:
> > Hi
> >
> > no, it is not SP1 because i already intaled some time ago. It was detach and
> > atach that solve the problem.
> >
> > Thanks again,
> > Rui Reis
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Can you let us know whether this is SP1 or not?
> > >
> > > Thanks
> > >
> > > John
> > >
> > > "Tecnica" wrote:
> > >
> > > > Hi Charles
> > > >
> > > > tanks for the help. Detach and attach database has solve the problem.
> > > >
> > > > Regards,
> > > > Rui Reis
> > > >
> > > > "Charles Wang[MSFT]" wrote:
> > > >
> > > > > Hi,
> > > > > My understanding of your issue is that:
> > > > > Your database had two log files. You manually deleted one of the log files
> > > > > in SQL Server Management Studio. However when you viewed the database
> > > > > properties again, you found that the log file was still there.
> > > > > If I have misunderstood, please let me know.
> > > > >
> > > > > I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> > > > > unfortunately I couldn't reproduce your issue. Everything works fine at my
> > > > > side. I created a database called TMLogDB which has two log files:
> > > > > TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> > > > > manually removed the TMLogDB_log1. Then I clicked the Properties of the
> > > > > database and found that the log file had been removed.
> > > > >
> > > > > For further research, I recommend that:
> > > > > 1. Check if this issue occurs only on this database. Please create a test
> > > > > dabase and see if the same issue exists on it;
> > > > > 2. Completely backup your database, then try using sp_detach_db to detach
> > > > > the database and sp_attach_db to attach the database with the current log
> > > > > file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> > > > > Server Books Online.
> > > > > 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> > > > > 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> > > > > logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> > > > >
> > > > > If you have any other questions or concerns, please feel free to let me
> > > > > know. It's my pleasure to be of assistance.
> > > > >
> > > > > Sincerely yours,
> > > > > Charles Wang
> > > > > Microsoft Online Community Support
> > > > >
> > > > > ======================================================> > > > > When responding to posts, please "Reply to Group" via
> > > > > your newsreader so that others may learn and benefit
> > > > > from this issue.
> > > > > ======================================================> > > > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > > > ======================================================> > > > >
> > > > >
> > > > >
> > > > >

Deleted Log file

Hi,
I have a database that use two log files. I deleted one of the logs going to
database/properties/files select the log file, and remove.
Everything goes well, but now when I go to database/properties/files I still
see the tow log files (the folder and file for one of the logs doesn’t exist
anymore).
Any idea how to resolve this?
Thanks
Hi
You don't say which version of SQL Server you are using? Have you tried
sp_helpfile to see what that gives and the
ALTER DATABASE REMOVE FILE logical_file_name
command to see if it can be removed that way?
John
"Tecnica" wrote:

> Hi,
> I have a database that use two log files. I deleted one of the logs going to
> database/properties/files select the log file, and remove.
> Everything goes well, but now when I go to database/properties/files I still
> see the tow log files (the folder and file for one of the logs doesn’t exist
> anymore).
> Any idea how to resolve this?
> Thanks
>
|||Hi John,
in fact i didn′t mencioned that the SQL is version 2005.
The ALTER DATABASE REMOVE FILE logical_file_name doesn′t work because
phisicaly the file does not exist.
If you run SELECT * FROM sys.database_files you see the file mencioned in
one row, but i don′t know if i can modify this system table.
Rui
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't say which version of SQL Server you are using? Have you tried
> sp_helpfile to see what that gives and the
> ALTER DATABASE REMOVE FILE logical_file_name
> command to see if it can be removed that way?
> John
>
> "Tecnica" wrote:
|||Hi
You should not need to modify system catalogs. Do you have the number/text
for the error for the ALTER DATABASE command. Are you using SP1?
John
"Tecnica" wrote:
[vbcol=seagreen]
> Hi John,
> in fact i didn′t mencioned that the SQL is version 2005.
> The ALTER DATABASE REMOVE FILE logical_file_name doesn′t work because
> phisicaly the file does not exist.
> If you run SELECT * FROM sys.database_files you see the file mencioned in
> one row, but i don′t know if i can modify this system table.
> Rui
> "John Bell" wrote:
|||Hi,
My understanding of your issue is that:
Your database had two log files. You manually deleted one of the log files
in SQL Server Management Studio. However when you viewed the database
properties again, you found that the log file was still there.
If I have misunderstood, please let me know.
I performed a test in SQL Server 2005 Enterprise Edition SP1, but
unfortunately I couldn't reproduce your issue. Everything works fine at my
side. I created a database called TMLogDB which has two log files:
TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
manually removed the TMLogDB_log1. Then I clicked the Properties of the
database and found that the log file had been removed.
For further research, I recommend that:
1. Check if this issue occurs only on this database. Please create a test
dabase and see if the same issue exists on it;
2. Completely backup your database, then try using sp_detach_db to detach
the database and sp_attach_db to attach the database with the current log
file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
Server Books Online.
3. Install SQL Server 2005 SP1 if it was not installed on your computer.
4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Charles
tanks for the help. Detach and attach database has solve the problem.
Regards,
Rui Reis
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> Your database had two log files. You manually deleted one of the log files
> in SQL Server Management Studio. However when you viewed the database
> properties again, you found that the log file was still there.
> If I have misunderstood, please let me know.
> I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> unfortunately I couldn't reproduce your issue. Everything works fine at my
> side. I created a database called TMLogDB which has two log files:
> TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> manually removed the TMLogDB_log1. Then I clicked the Properties of the
> database and found that the log file had been removed.
> For further research, I recommend that:
> 1. Check if this issue occurs only on this database. Please create a test
> dabase and see if the same issue exists on it;
> 2. Completely backup your database, then try using sp_detach_db to detach
> the database and sp_attach_db to attach the database with the current log
> file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> Server Books Online.
> 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Sincerely yours,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi,
Appreciate your update and response. I am glad to hear that the problem has
been fixed. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi
Can you let us know whether this is SP1 or not?
Thanks
John
"Tecnica" wrote:
[vbcol=seagreen]
> Hi Charles
> tanks for the help. Detach and attach database has solve the problem.
> Regards,
> Rui Reis
> "Charles Wang[MSFT]" wrote:
|||Hi
no, it is not SP1 because i already intaled some time ago. It was detach and
atach that solve the problem.
Thanks again,
Rui Reis
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Can you let us know whether this is SP1 or not?
> Thanks
> John
> "Tecnica" wrote:
|||Hi
I would strongly recommend that you upgrade to SP1 which does not seem to
have this issue.
John
"Tecnica" wrote:
[vbcol=seagreen]
> Hi
> no, it is not SP1 because i already intaled some time ago. It was detach and
> atach that solve the problem.
> Thanks again,
> Rui Reis
> "John Bell" wrote:

delete with IN clause

delete from t1 where (c1, c2,c3) in (select c1,c2,c3 from t2 where c1=1).

The above query works well in oracle... whats its equavalent in SQL Server?

Thanks

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

Delete where 2 fields do not match

I need to be able to delete records from 1 table that do not match 2 fields
on a 2nd table. I was trying to get the SELECT to work first to find the
records that are not full matches. Below is my code and would appreciate
any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
was not sure. Thanks.
SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
FROM dbo.ClientWorkerStatus, ClientWorkerLink
WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
The problem with this is it gives me every record that does not match on
WorkerID. What I want is only those records that are on the
ClientWorkerStatus table that do not have exact match between ClientID and
WorkerID.David,
Can you post the DDL, sample data (INSERTs) and the desired resultset?
HTH
Jerry
"David" <dlchase@.lifetimeinc.com> wrote in message
news:OYqCKc2yFHA.3180@.TK2MSFTNGP14.phx.gbl...
>I need to be able to delete records from 1 table that do not match 2 fields
>on a 2nd table. I was trying to get the SELECT to work first to find the
>records that are not full matches. Below is my code and would appreciate
>any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
>was not sure. Thanks.
> SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
> FROM dbo.ClientWorkerStatus, ClientWorkerLink
> WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
> dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
> The problem with this is it gives me every record that does not match on
> WorkerID. What I want is only those records that are on the
> ClientWorkerStatus table that do not have exact match between ClientID and
> WorkerID.
>|||Try this,
SELECT ClientID, WorkerID
FROM ClientWorkerStatus C
WHERE (ClientID IN (SELECT ClientID
FROM ClientWorkerLink
WHERE (ClientID = C.ClientID) AND (WorkerID <>
C.WorkerID)))
"David" wrote:

> I need to be able to delete records from 1 table that do not match 2 field
s
> on a 2nd table. I was trying to get the SELECT to work first to find the
> records that are not full matches. Below is my code and would appreciate
> any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
> was not sure. Thanks.
> SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
> FROM dbo.ClientWorkerStatus, ClientWorkerLink
> WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
> dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
> The problem with this is it gives me every record that does not match on
> WorkerID. What I want is only those records that are on the
> ClientWorkerStatus table that do not have exact match between ClientID and
> WorkerID.
>
>|||Try,
delete dbo.ClientWorkerStatus
where not exists (
select *
from dbo.ClientWorkerLink as a
where a.ClientID = dbo.ClientWorkerStatus.ClientID
and a.WorkerID = dbo.ClientWorkerStatus.WorkerID
)
AMB
"David" wrote:

> I need to be able to delete records from 1 table that do not match 2 field
s
> on a 2nd table. I was trying to get the SELECT to work first to find the
> records that are not full matches. Below is my code and would appreciate
> any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
> was not sure. Thanks.
> SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
> FROM dbo.ClientWorkerStatus, ClientWorkerLink
> WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
> dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
> The problem with this is it gives me every record that does not match on
> WorkerID. What I want is only those records that are on the
> ClientWorkerStatus table that do not have exact match between ClientID and
> WorkerID.
>
>|||Got it! Thanks.
*** Sent via Developersdex http://www.examnotes.net ***

Delete via linked server

I am trying to delete records in a table pointing via linked server to Sybas
e
IQ
The login used in Linked server has all rights to do this
I can select using this linked server
I am gettng this msg, pls help
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"dbo"."all_metric"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation genera
ted
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [Sybase][ODBC Driver][Ada
ptive Server
IQ]Option value out of range: Update operation attempted on a read-only
cursor]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].Look like you're using Msdasql as the provider to your sybase and the remote
user for your linkedserver connection does not have permission to delete.
Also, one cannot do update on a readonly cursor.
If you're certain that you have permission to do update/delete, you can try
passthrough query. See Openquery(), OpenRowset(), or OpenDataSource() in
book online for syntax.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:586713B3-8E88-41D2-ABD6-7F6DC29680E8@.microsoft.com...
> I am trying to delete records in a table pointing via linked server to
Sybase
> IQ
> The login used in Linked server has all rights to do this
> I can select using this linked server
> I am gettng this msg, pls help
> Server: Msg 7345, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' could not delete from table
'"dbo"."all_metric"'.
> User did not have sufficient permission to delete the row.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
generated
> errors. Check each OLE DB status value, if available. No work was done.]
> [OLE/DB provider returned message: [Sybase][ODBC Driver][A
daptive Server
> IQ]Option value out of range: Update operation attempted on a read-only
> cursor]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRow
s
> returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
>|||Remote LOgin i am using does have the permissions
Is there any way to make it work using Linked Server
Why is it a readonly cursor. ? is there any way i could change the ODBC
properties to make it work
"oj" wrote:

> Look like you're using Msdasql as the provider to your sybase and the remo
te
> user for your linkedserver connection does not have permission to delete.
> Also, one cannot do update on a readonly cursor.
> If you're certain that you have permission to do update/delete, you can tr
y
> passthrough query. See Openquery(), OpenRowset(), or OpenDataSource() in
> book online for syntax.
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:586713B3-8E88-41D2-ABD6-7F6DC29680E8@.microsoft.com...
> Sybase
> '"dbo"."all_metric"'.
> generated
>
>|||The driver is the connector/translator between the two servers. A certain
provider/driver can only expose a readonly recordset.
You want to make sure you're running the latest driver on both boxes. You
can download latest mdac here:
http://microsoft.com/data
Also, see if you can use OLEDB provider instead of MSDASQL.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:A811897B-4C20-40DA-9F59-CE88B6B0247B@.microsoft.com...[vbcol=seagreen]
> Remote LOgin i am using does have the permissions
> Is there any way to make it work using Linked Server
> Why is it a readonly cursor. ? is there any way i could change the ODBC
> properties to make it work
>
> "oj" wrote:
>
remote[vbcol=seagreen]
delete.[vbcol=seagreen]
try[vbcol=seagreen]
done.][vbcol=seagreen]
Server[vbcol=seagreen]
read-only[vbcol=seagreen]
IRowsetChange::DeleteRows[vbcol=seagreen
]|||Perhaps the table doesn't have a primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"oj" <nospam_ojngo@.home.com> wrote in message news:OFaznY6jEHA.3624@.TK2MSFTNGP10.phx.gbl...[
vbcol=seagreen]
> The driver is the connector/translator between the two servers. A certain
> provider/driver can only expose a readonly recordset.
> You want to make sure you're running the latest driver on both boxes. You
> can download latest mdac here:
> http://microsoft.com/data
> Also, see if you can use OLEDB provider instead of MSDASQL.
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:A811897B-4C20-40DA-9F59-CE88B6B0247B@.microsoft.com...
> remote
> delete.
> try
> done.]
> Server
> read-only
> IRowsetChange::DeleteRows
>[/vbcol]|||Good point, Tibor. A similar case can be observed between Access <-> Sqlserv
er.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4czv7%23jEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Perhaps the table doesn't have a primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
news:OFaznY6jEHA.3624@.TK2MSFTNGP10.phx.gbl...
>

Delete via linked server

I am trying to delete records in a table pointing via linked server to Sybase
IQ
The login used in Linked server has all rights to do this
I can select using this linked server
I am gettng this msg, pls help
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"dbo"."all_metric"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [Sybase][ODBC Driver][Adaptive Server
IQ]Option value out of range: Update operation attempted on a read-only
cursor]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
Look like you're using Msdasql as the provider to your sybase and the remote
user for your linkedserver connection does not have permission to delete.
Also, one cannot do update on a readonly cursor.
If you're certain that you have permission to do update/delete, you can try
passthrough query. See Openquery(), OpenRowset(), or OpenDataSource() in
book online for syntax.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:586713B3-8E88-41D2-ABD6-7F6DC29680E8@.microsoft.com...
> I am trying to delete records in a table pointing via linked server to
Sybase
> IQ
> The login used in Linked server has all rights to do this
> I can select using this linked server
> I am gettng this msg, pls help
> Server: Msg 7345, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' could not delete from table
'"dbo"."all_metric"'.
> User did not have sufficient permission to delete the row.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
generated
> errors. Check each OLE DB status value, if available. No work was done.]
> [OLE/DB provider returned message: [Sybase][ODBC Driver][Adaptive Server
> IQ]Option value out of range: Update operation attempted on a read-only
> cursor]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
> returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
>
|||Remote LOgin i am using does have the permissions
Is there any way to make it work using Linked Server
Why is it a readonly cursor. ? is there any way i could change the ODBC
properties to make it work
"oj" wrote:

> Look like you're using Msdasql as the provider to your sybase and the remote
> user for your linkedserver connection does not have permission to delete.
> Also, one cannot do update on a readonly cursor.
> If you're certain that you have permission to do update/delete, you can try
> passthrough query. See Openquery(), OpenRowset(), or OpenDataSource() in
> book online for syntax.
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:586713B3-8E88-41D2-ABD6-7F6DC29680E8@.microsoft.com...
> Sybase
> '"dbo"."all_metric"'.
> generated
>
>
|||The driver is the connector/translator between the two servers. A certain
provider/driver can only expose a readonly recordset.
You want to make sure you're running the latest driver on both boxes. You
can download latest mdac here:
http://microsoft.com/data
Also, see if you can use OLEDB provider instead of MSDASQL.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:A811897B-4C20-40DA-9F59-CE88B6B0247B@.microsoft.com...[vbcol=seagreen]
> Remote LOgin i am using does have the permissions
> Is there any way to make it work using Linked Server
> Why is it a readonly cursor. ? is there any way i could change the ODBC
> properties to make it work
>
> "oj" wrote:
remote[vbcol=seagreen]
delete.[vbcol=seagreen]
try[vbcol=seagreen]
done.][vbcol=seagreen]
Server[vbcol=seagreen]
read-only[vbcol=seagreen]
IRowsetChange::DeleteRows[vbcol=seagreen]
|||Perhaps the table doesn't have a primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"oj" <nospam_ojngo@.home.com> wrote in message news:OFaznY6jEHA.3624@.TK2MSFTNGP10.phx.gbl...
> The driver is the connector/translator between the two servers. A certain
> provider/driver can only expose a readonly recordset.
> You want to make sure you're running the latest driver on both boxes. You
> can download latest mdac here:
> http://microsoft.com/data
> Also, see if you can use OLEDB provider instead of MSDASQL.
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:A811897B-4C20-40DA-9F59-CE88B6B0247B@.microsoft.com...
> remote
> delete.
> try
> done.]
> Server
> read-only
> IRowsetChange::DeleteRows
>
|||Good point, Tibor. A similar case can be observed between Access <-> Sqlserver.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4czv7%23jEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Perhaps the table doesn't have a primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
news:OFaznY6jEHA.3624@.TK2MSFTNGP10.phx.gbl...
>

delete text character

hi,

for example ;

select price from table

query results:195dollar

I want to see query result only numeric:195

how can I do?

You could try this:

select convert(int, price) as price from table

That would round the value to the nearest integer value.

|||

Allen White wrote:

You could try this:

select convert(int, price) as price from table

That would round the value to the nearest integer value.

Don't working :(

delete table1 from openxml

Hi,
I know how to select using openxml.
Could you please let me have a sample code on how to delete records from a table based on what is inside an xml.
Thanks

Something like:
delete from table1
where
OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)

There are a couple of ways to approach this. One of the easiest ways (again easy but not necessarily the highest performance) is to use a JOIN.

Example :

delete from table1 FROM table1 JOIN
SELECT contractMonth, [date], mid, bizdays

OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)) example1 on table1."commonkey" = example1."commonkey"

|||

You can use OPENXML in FROM clause so do below:

delete from table1

where exists (

select *

from OPENXML(...)

with (...) as x

where x.contractMonth = table1.contractMonth

and x.[date] = table.[date]

...

)

delete table1 from openxml

Hi,
I know how to select using openxml.
Could you please let me have a sample code on how to delete records from a table based on what is inside an xml.
Thanks

Something like:
delete from table1
where
OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)

There are a couple of ways to approach this. One of the easiest ways (again easy but not necessarily the highest performance) is to use a JOIN.

Example :

delete from table1 FROM table1 JOIN
SELECT contractMonth, [date], mid, bizdays

OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)) example1 on table1."commonkey" = example1."commonkey"

|||

You can use OPENXML in FROM clause so do below:

delete from table1

where exists (

select *

from OPENXML(...)

with (...) as x

where x.contractMonth = table1.contractMonth

and x.[date] = table.[date]

...

)

Delete syntax

Can one do a 'Delete from Select' SQL Statement?
SELECT distinct Vh_make_id FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN
(select distinct Vh_make_id from marc1) AND vh_make_id != '' -- 154 Rows
I want to delete all rows from the vehicle table that have a matching
Vh_make_id in the marc1 table but also have a Vh_make_id not equal to blank?
I am as to what exactly will occur with the following delete
statement:
DELETE FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (SELECT distinct
Vh_make_id FROM marc1) AND vh_make_id != ''> DELETE FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (SELECT distinct
> Vh_make_id FROM marc1) AND vh_make_id !=
You will delete every record where the vh_make_id in vht_lu_vehicle is empty
(not null, which is probably what you want) that has a vh_make_id in the
marc1 table.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
****************************************
*******
Think Outside the Box!
****************************************
*******
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:2ED21B32-3DF4-42B4-B32C-5568BBDBDDC4@.microsoft.com...
> Can one do a 'Delete from Select' SQL Statement?
> SELECT distinct Vh_make_id FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN
> (select distinct Vh_make_id from marc1) AND vh_make_id != '' -- 154 Rows
> I want to delete all rows from the vehicle table that have a matching
> Vh_make_id in the marc1 table but also have a Vh_make_id not equal to
> blank?
> I am as to what exactly will occur with the following delete
> statement:
> DELETE FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (SELECT distinct
> Vh_make_id FROM marc1) AND vh_make_id != ''
>

delete sql with in clause

delete from t1 where (c1, c2,c3) in (select c1,c2,c3 from t2).

The above query works well in oracle... whats its equavalent in SQL Server?

Thanks

That should be something like this here:

delete t1
from t1
Inner join T2
on t1.c1 = t2.c1
AND t1.c2 = t2.c2
AND t1.c3 = t2.c3

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens

delete rows using except ?

Found out that except is very fast, but is it possible to delete rows using except ?

Like:

delete accountnumber, central
from table1
except
select accountnumber, central
from table 2

Jam:

The DELETE FROM ... WHERE NOT EXISTS is likely to be faster than a DELETE FROM ... FROM ... EXCEPT; something such as:

delete from table1
from table1 a
where not exists
( select 0 /* a dummy */ from table2 b
where a.accountNumber = b.accountNumber
and a.central = b.central
)

Dave

|||

Hmmmm. This query seems to have the same amount of logical IO as the previous query:

delete from table1
from table1 a
inner join
( select accountNumber,
central
from table1
except
select accountNumber,
central
from table2
) b
on a.accountNumber = b.accountNumber
and a.central = b.central

I'll do a little more digging. At the moment, this query also looks viable.

Dave

|||

Sorry about that, I was simultaneously dealing with an implementation problem. Really, I cannot go any farther with my mock-up without knowing specifics about your actual implementation because your implementation will have a big impact on the execution plan. Yeah, I tested out with the same amount of logical IOs with both queries but this really is more qualitative than anything and doesn't prove too much.

I can say little more than the qualitative viability of the EXCEPT query; however, I don't consider this a simpler query than the NOT EXISTS query. Can you give more information about your two tables -- especially index information?

Dave

DELETE query syntax using subqueries

I have the following SELECT query, the results of which I would delete
from the table they're pulled from:

SELECT A.* FROM SalesOrderPartPrices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartPrices B
WHERE
(A.SalesOrderNo = B.SalesOrderNo) AND
(A.PartNo = B.PartNo) AND
(A.UnitPrice = B.UnitPrice) AND
(A.RowID > B.RowID)
)

However, when I try to run the following query, I get an error ("Msg
170, Level 15, State 1, Line 1: Incorrect syntax near 'A'."):

DELETE FROM SalesOrderPartPrices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartPrices B
WHERE
(A.SalesOrderNo = B.SalesOrderNo) AND
(A.PartNo = B.PartNo) AND
(A.UnitPrice = B.UnitPrice) AND
(A.RowID > B.RowID)
)

What am I doing wrong, and how do I resolve the issue? Any help is
greatly appreciated...... thanks in advance!

-=Tek Boy=-You don't understand the SQL model for tables -- base, derived, temp,
views, etc. A correlation name acts as if it creates a new table with
the new name and the old data. That working table then disappears at
the end of the statement. If you want to change a base table, you
have to use a base table name:

DELETE FROM SalesOrderPartPrices
WHERE EXISTS(
SELECT *
FROM SalesOrderPartPrices AS S1
WHERE SalesOrderPartPrices.SalesOrderNo = S1.SalesOrderNo
AND SalesOrderPartPrices.PartNo = S1.PartNo
AND SalesOrderPartPrices.UnitPrice = S1.UnitPrice
AND SalesOrderPartPrices.RowID > S1.RowID):

Is rowid actually a PHYSICAL location in the PHYSICAL table, a la
Oracle? Are you trying to remove dups because your table was created
without keys? If so, I hope you get to run over the moron that did
this in the parking lot. You are going to have to re-do all the
reports run against this table for at least the last year because of
him. Then you have to introduce a real key, etc.

From the way you wrote your code, I would guess that SQL is not your
main language and that you are an old mainframer. You were using too
many parens and putting AND at the end of the line like we did with
punch cards.

While that kind of formatting does not mess up the compiler, it will
increase the time to maintain code by 8-10% because of the way people
read from left to right in English.|||Thanks for your reply, Joe. You are correct in your observation that
SQL is not my primarily language -- I develop mainly using ASP, HTML,
Javascript (and eventually C#/.NET). As such, my understanding of the
foundations of SQL is still rather limited -- however, I do have a
copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
that situation will improve over time. As far as being an "old
mainframer", I've only read about mainframes in books and such. :)
My hands-on experience has been limited to Access, SQL Server and
(briefly) Oracle 8i.

My intention here was to clean up a bunch of data exported from a
MANMAN database, for subsequent use by a SQL Server 2000-drive web
application; no live data was harmed in the processing of these
scripts. And the only reason I added the RowID (identity) column is
because all of the other SQL Server methods I've read about for
removing duplicate rows requires some type of temp table. The most
elegant method I managed to find in the newsgroups was written for
Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
KEY constraints initially because of the duplicates. After I cleaned
everything up, I deleted the RowID column and created a PRIMARY KEY
constraint on (SalesOrder, PartNo).

Since you mention code formatting -- is there a method you DO suggest?
I haven't found a clearly-documented method of writing scripts that I
find easy to read AND doesn't take up inordinate amount of real estate
on my monitor. Always looking for a better way to do
things.........

-=Tek Boy=-|||Speaking of formatting, if anybody from MS is listening, I would LOVE
to be able to set the width of the tab in Query Analyzer as is
possible in the VS IDE.

On 11 Feb 2004 13:32:22 -0800, vejitaasp@.hotmail.com (Saiyan Vejita)
wrote:

>Thanks for your reply, Joe. You are correct in your observation that
>SQL is not my primarily language -- I develop mainly using ASP, HTML,
>Javascript (and eventually C#/.NET). As such, my understanding of the
>foundations of SQL is still rather limited -- however, I do have a
>copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
>that situation will improve over time. As far as being an "old
>mainframer", I've only read about mainframes in books and such. :)
>My hands-on experience has been limited to Access, SQL Server and
>(briefly) Oracle 8i.
>My intention here was to clean up a bunch of data exported from a
>MANMAN database, for subsequent use by a SQL Server 2000-drive web
>application; no live data was harmed in the processing of these
>scripts. And the only reason I added the RowID (identity) column is
>because all of the other SQL Server methods I've read about for
>removing duplicate rows requires some type of temp table. The most
>elegant method I managed to find in the newsgroups was written for
>Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
>KEY constraints initially because of the duplicates. After I cleaned
>everything up, I deleted the RowID column and created a PRIMARY KEY
>constraint on (SalesOrder, PartNo).
>Since you mention code formatting -- is there a method you DO suggest?
> I haven't found a clearly-documented method of writing scripts that I
>find easy to read AND doesn't take up inordinate amount of real estate
>on my monitor. Always looking for a better way to do
>things.........
>
>-=Tek Boy=-|||>> My intention here was to clean up a bunch of data exported from a
MANMAN database, .. <<

Okay, this is not **real** data, but only a scrubbing table! Have you
looked at any ETL tools for this job? They are geared for this kind
of thing and havea lot of extras that can be helpful. Otherwise, use
IDENTITY or a sequential file for the scrub work.

>> Since you mention code formatting -- is there a method you DO
suggest? <<

My rules have been picked up by my various publishers, and they are:

1) Uppercase keywords and put one per line
2) lowercase scalars, like column names, local variables, etc.
3) Capitalize schema objects like table names, view names, etc.
4) Indent along a gutter for each subquery or clause

And follow ISO-11179 Standard nbaming conventions.|||In Query Analyzer, open Tools | Options then on the Editor tab see Tab size
(in spaces). Is that what you meant?

"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:p0a03050ott4cf0claq65cfoi8b1v0oco7@.4ax.com...
> Speaking of formatting, if anybody from MS is listening, I would LOVE
> to be able to set the width of the tab in Query Analyzer as is
> possible in the VS IDE.
> On 11 Feb 2004 13:32:22 -0800, vejitaasp@.hotmail.com (Saiyan Vejita)
> wrote:
> >Thanks for your reply, Joe. You are correct in your observation that
> >SQL is not my primarily language -- I develop mainly using ASP, HTML,
> >Javascript (and eventually C#/.NET). As such, my understanding of the
> >foundations of SQL is still rather limited -- however, I do have a
> >copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
> >that situation will improve over time. As far as being an "old
> >mainframer", I've only read about mainframes in books and such. :)
> >My hands-on experience has been limited to Access, SQL Server and
> >(briefly) Oracle 8i.
> >My intention here was to clean up a bunch of data exported from a
> >MANMAN database, for subsequent use by a SQL Server 2000-drive web
> >application; no live data was harmed in the processing of these
> >scripts. And the only reason I added the RowID (identity) column is
> >because all of the other SQL Server methods I've read about for
> >removing duplicate rows requires some type of temp table. The most
> >elegant method I managed to find in the newsgroups was written for
> >Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
> >KEY constraints initially because of the duplicates. After I cleaned
> >everything up, I deleted the RowID column and created a PRIMARY KEY
> >constraint on (SalesOrder, PartNo).
> >Since you mention code formatting -- is there a method you DO suggest?
> > I haven't found a clearly-documented method of writing scripts that I
> >find easy to read AND doesn't take up inordinate amount of real estate
> >on my monitor. Always looking for a better way to do
> >things.........
> >-=Tek Boy=-|||Actually I misspoke, I didn't mean Query Analyzer, I meant the
workspace for writing a stored procedure over in Enterprise Manager.

On Fri, 07 May 2004 16:30:43 GMT, "tperovic" <tonyperovic@.yahoo.com>
wrote:

>In Query Analyzer, open Tools | Options then on the Editor tab see Tab size
>(in spaces). Is that what you meant?
>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:p0a03050ott4cf0claq65cfoi8b1v0oco7@.4ax.com...
>> Speaking of formatting, if anybody from MS is listening, I would LOVE
>> to be able to set the width of the tab in Query Analyzer as is
>> possible in the VS IDE.
>>
>> On 11 Feb 2004 13:32:22 -0800, vejitaasp@.hotmail.com (Saiyan Vejita)
>> wrote:
>>
>> >Thanks for your reply, Joe. You are correct in your observation that
>> >SQL is not my primarily language -- I develop mainly using ASP, HTML,
>> >Javascript (and eventually C#/.NET). As such, my understanding of the
>> >foundations of SQL is still rather limited -- however, I do have a
>> >copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
>> >that situation will improve over time. As far as being an "old
>> >mainframer", I've only read about mainframes in books and such. :)
>> >My hands-on experience has been limited to Access, SQL Server and
>> >(briefly) Oracle 8i.
>>> >My intention here was to clean up a bunch of data exported from a
>> >MANMAN database, for subsequent use by a SQL Server 2000-drive web
>> >application; no live data was harmed in the processing of these
>> >scripts. And the only reason I added the RowID (identity) column is
>> >because all of the other SQL Server methods I've read about for
>> >removing duplicate rows requires some type of temp table. The most
>> >elegant method I managed to find in the newsgroups was written for
>> >Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
>> >KEY constraints initially because of the duplicates. After I cleaned
>> >everything up, I deleted the RowID column and created a PRIMARY KEY
>> >constraint on (SalesOrder, PartNo).
>>> >Since you mention code formatting -- is there a method you DO suggest?
>> > I haven't found a clearly-documented method of writing scripts that I
>> >find easy to read AND doesn't take up inordinate amount of real estate
>> >on my monitor. Always looking for a better way to do
>> >things.........
>>>>> >-=Tek Boy=-
>