Deleting rows with OPENXML

Hi there,
I'm trying to delete some rows from a table using an xml document (in
memory) and OpenXML in SQL Server. I have the following code, but it will
only delete the first record. Any ideas?
CREATE PROCEDURE [dbo]. [CompanyBusinessUnit_DeleteCompanyBusine
ssUnit_Delete]
@.CompanyHeirarchy nvarchar(4000)
AS
SET NOCOUNT ON;
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.CompanyHeirarchy
DELETE FROM CompanyBusinessUnit
WHERE CompanyBusinessUnitID IN (Select CompanyBusinessUnitID
FROM OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2) WITH
(CompanyBusinessUnitID Integer))
EXEC sp_xml_removedocument @.hDoc
GO
Thanks,
WesTry,
DELETE
CompanyBusinessUnit
FROM
CompanyBusinessUnit
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:

> Hi there,
> I'm trying to delete some rows from a table using an xml document (in
> memory) and OpenXML in SQL Server. I have the following code, but it will
> only delete the first record. Any ideas?
> CREATE PROCEDURE [dbo]. [CompanyBusinessUnit_DeleteCompanyBusine
ssUnit_Delete]
> @.CompanyHeirarchy nvarchar(4000)
> AS
> SET NOCOUNT ON;
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.CompanyHeirarchy
> DELETE FROM CompanyBusinessUnit
> WHERE CompanyBusinessUnitID IN (Select CompanyBusinessUnitID
> FROM OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2) WITH
> (CompanyBusinessUnitID Integer))
> EXEC sp_xml_removedocument @.hDoc
> GO
>
> Thanks,
> Wes|||Hi there,
Thanks for answering my post so quickly. Unfortunately your solution did
the same thing as the ones I've tried. It simply deleted the first row
matched from the XML. I have posted my XML below, maybe there is something
wrong with it.
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
Thanks,
Wes
"Alejandro Mesa" wrote:
> Try,
> DELETE
> CompanyBusinessUnit
> FROM
> CompanyBusinessUnit
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
>
> "Wes" wrote:
>|||Try,
DELETE
a
FROM
CompanyBusinessUnit as a
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:
> Hi there,
> Thanks for answering my post so quickly. Unfortunately your solution did
> the same thing as the ones I've tried. It simply deleted the first row
> matched from the XML. I have posted my XML below, maybe there is somethin
g
> wrong with it.
> <NewDataSet>
> <DeletedNodes>
> <CompanyBusinessUnitID>17</CompanyBusinessUnitID>
> <CompanyBusinessUnitID>18</CompanyBusinessUnitID>
> </DeletedNodes>
> </NewDataSet>
> Thanks,
> Wes
> "Alejandro Mesa" wrote:
>|||I thought the problem was with the DELETE statement, but no, it was the
"SELECT ... FROM OPENXML ..." that was selecting just the first row. Can you
try changing this select by:
Select
cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
where
nodetype = 3
AMB
"Alejandro Mesa" wrote:
> Try,
> DELETE
> a
> FROM
> CompanyBusinessUnit as a
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
> "Wes" wrote:
>|||Your firts post will also work if you change the layout of the xml document
to:
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
</DeletedNodes>
<DeletedNodes>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
AMB
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Excellent! Thank you very much as this one works perfectly. Now I'll have
to analyze the code and see what I was doing differently.
Thanks for your help.
Wes
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>

Deleting from SQL table using two arguments

I am trying to delete from a SQL table using two arguments, I currently use the following on the aspx page:

<ItemTemplate>
<asp:LinkButton ID="cmdDeleteJob" runat="server" CommandName="CancelJob" Text="Delete" CssClass="CommandButton" CommandArgument='<%# Bind("Type_of_service") %>' OnClientClick="return confirm('Are you sure you want to delete this item?');"></asp:LinkButton>
</ItemTemplate>

and then I use the following on the aspx.vb page:

If e.CommandName = "CancelJob" Then

Dim typeService As Integer = CType(e.CommandArgument, Integer)
Dim typeDesc As String = ""

Dim wsDelete As New page1
wsDelete.deleteAgencySvcs(typeService)

gvTech.DataBind()

I need to be able to pick up two CommandArguments.

Is this Template in a GridView? if so, is the second argument the same for all rows? if yes, then store that arugment in the DataKeyNames property of the grid. If not, then you can try using a pipe or literal separator for your command argument and then parse it out in the handler by using split().

Hope that helps,

--D

|||

Hi dannyo1984,

Pass Two Areguments like this

<ItemTemplate>

<asp:LinkButtonID="lnkCreator"runat="server"

Text='<%# Eval ("fullname") %>'

CommandName="Select"

CommandArgument='<%# DataBinder.Eval(Container, "DataItem.lock", "{0}")+" "+ DataBinder.Eval(Container, "DataItem.ChangeRequest_No", "{0}")%>'OnCommand="lnkCreator_Command">

</asp:LinkButton>

</ItemTemplate>

in ASX.VB Page

ProtectedSub lnkCreator_Command(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.CommandEventArgs)

Dim strStringAsString = e.CommandArgument.ToString()

Dim resultArrayAsString() = strString.Split(" ")

EndSub

Regards,

Naveen

|||

How do I separate the two column values in the VB? I need both to be string values.

|||

On the ASPX page I used.

<asp:LinkButton ID="cmdDeleteJob" runat="server" CommandName="CancelJob" Text="Delete" CssClass="CommandButton" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "Type_of_Service") +"|"+ DataBinder.Eval(Container.DataItem, "Other_Service") %>' OnClientClick="return confirm('Are you sure you want to delete this item?');"></asp:LinkButton>

On the VB side I used this, to get the values.

Dim delimStr As String = "|"

Dim delimiter As Char() = delimStr.ToCharArray()

Dim arguments As String() = Convert.ToString(e.CommandArgument).Split(delimiter)

Dim typeservice As Integer = Convert.ToInt32(arguments(0))

Dim Value2 As String = Convert.ToString(arguments(1))

Dim otherService As String = Value2

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 and Updating from Gridview

Hi,

I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @.TransactionIDAND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid) "

It gives the error that Quantityavailable is not a SET type?

Thanks if you can suggest a remedy!

Jon

jbear123:

UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid

That is not valid syntax for an UPDATE statement.

Try this

UPDATE itemsSET Quantityavailable = Quantityavailable + 1, numtaken = numtaken - 1WHERE ItemId = @.ItemId

|||

Thanks!

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 a column DEFAULT....

Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)Giacomo (no_spam@.grazie.it) writes:

Quote:

Originally Posted by

P.S.
We're using SQL Sever 2000 (version 8.00.194)

Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.

No, it will not address your problem at hand, and being at work now I don't
have the time to post the exact code. You will need to read the system
tables to get the name of the default and then use dynamic SQL to run
ALTER TABLE DROP CONSTRAINT. Best practice is to always name your defaults,
so that you don't run into this problem.

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

You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.

Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

EXEC sp_rename 'PlanningDettaglio.OreStraordinario', '__old', 'COLUMN'

ALTER TABLE PlanningDettaglio
ADD OreStraordinario DECIMAL(18,2) NULL -- or whatever the original
definition

UPDATE PlanningDettaglio
SET OreStraordinario = __old

ALTER TABLE PlanningDettaglio DROP COLUMN __old

COMMIT TRANSACTION

HTH,
Gert-Jan

Giacomo wrote:

Quote:

Originally Posted by

>
Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:
>
ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO
>
ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO
>
ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO
>
it works, but only locally.
I've tried with:
>
ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT
>
Err.: Incorrect syntax near the keyword 'DEFAULT'.
>
Can someone help me please?
>
Thanks in advance,
Giacomo
>
P.S.
We're using SQL Sever 2000 (version 8.00.194)

|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

Giacomo (no_spam@.grazie.it) writes:

Quote:

Originally Posted by

>P.S.
>We're using SQL Sever 2000 (version 8.00.194)

>
Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.
>
No, it will not address your problem at hand, and being at work now I
don't have the time to post the exact code. You will need to read the
system tables to get the name of the default and then use dynamic SQL to
run ALTER TABLE DROP CONSTRAINT. Best practice is to always name your
defaults, so that you don't run into this problem.

So this is how you do it:

DECLARE @.default sysname
SELECT @.default = object_name(cdefault)
FROM syscolumns
WHERE id = object_id('yourtable')
AND name = 'yourcolumn'
EXEC ('ALTER TABLE yourtable DROP CONSTRAINT ' + @.default)

--
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|||Thank you very much,
it works perfectly! :)

Giacomo|||Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
<constraint name >The conflict occured in database <databasename>,table
tablename,column <columnname>

Kindly give solution of this problem.
sonika.

Gert-Jan Strik wrote:

Quote:

Originally Posted by

Giacomo,
>
You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.
>
Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:
>
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
>
EXEC sp_rename 'PlanningDettaglio.OreStraordinario', '__old', 'COLUMN'
>
ALTER TABLE PlanningDettaglio
ADD OreStraordinario DECIMAL(18,2) NULL -- or whatever the original
definition
>
UPDATE PlanningDettaglio
SET OreStraordinario = __old
>
ALTER TABLE PlanningDettaglio DROP COLUMN __old
>
COMMIT TRANSACTION
>
HTH,
Gert-Jan
>
>
Giacomo wrote:

Quote:

Originally Posted by

Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)

|||sonu (sonika4oct@.gmail.com) writes:

Quote:

Originally Posted by

Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
><constraint name >The conflict occured in database <databasename>,table
tablename,column <columnname>
>
Kindly give solution of this problem.

I'm sorry, but you will have to try again. To start with, your problem
appears to be completely different from what Giacomo had, so please start
a new thread.

As for your problem, you run a DELETE statement and a constraint fires.
I assume that this is a foreign key constraint. Usually foreign keys
are a good thing, as it prevents from you from doing bad things like
deleting a product which are listed on open order items. Then you talk
about diagrams, but it's impossible to understand what you are talking
about. Are you talking about database diagrams in an SQL tool, or is one
of your tables called Diagrams? Please post again, and make an effor to
explain your problem clearly. It helps if you includ the definition of
the involved tables, and the SQL you are having problem with.

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

Delete-operation performance problem

Hi!

I'm experiencing the following experience problem with my SQL Server 2000.

Explanation a' la example;

1.
I insert data using my SP; EXEC dbo.up_DataInsert
This is fine, SQL Profiler duration only a few ms.

2.
I try to select the data.
select * from dbo.tblData where DataNumber = 283279
This is fine, SQL Profiler duration only a few ms.

3.
delete from dbo.tblData where DataNumber = 283279
This is NOT fine, SQL Profiler duration up to 50 seconds!!!

I have got a unique index with ignore duplicate key and the table has about
180.000 records.

What could be wrong here??

/Magnus"Magnus sterberg" <magnus.osterberg@.abo.fi> wrote in message
news:d9bgug$rcr$1@.plaza.suomi.net...
> Hi!
> I'm experiencing the following experience problem with my SQL Server 2000.
> Explanation a' la example;
> 1.
> I insert data using my SP; EXEC dbo.up_DataInsert
> This is fine, SQL Profiler duration only a few ms.
> 2.
> I try to select the data.
> select * from dbo.tblData where DataNumber = 283279
> This is fine, SQL Profiler duration only a few ms.
> 3.
> delete from dbo.tblData where DataNumber = 283279
> This is NOT fine, SQL Profiler duration up to 50 seconds!!!
> I have got a unique index with ignore duplicate key and the table has
> about 180.000 records.
> What could be wrong here??
> /Magnus

No idea - have you checked the query plan in Profiler to see where the time
is going? And are there any other factors, such as a DELETE trigger on the
table? If you can post the table DDL (including keys and indexes) and also
details of the query plan (SET SHOWPLAN_TEXT/ALL), someone may be able to
suggest something.

Simon|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> I'm experiencing the following experience problem with my SQL Server 2000.
> Explanation a' la example;
> 1.
> I insert data using my SP; EXEC dbo.up_DataInsert
> This is fine, SQL Profiler duration only a few ms.
> 2.
> I try to select the data.
> select * from dbo.tblData where DataNumber = 283279
> This is fine, SQL Profiler duration only a few ms.
> 3.
> delete from dbo.tblData where DataNumber = 283279
> This is NOT fine, SQL Profiler duration up to 50 seconds!!!
> I have got a unique index with ignore duplicate key and the table has
> about 180.000 records.

Possible causes:

1) Blocking.

2) Autogrow.

3) There is a trigger on the table.

4) There is a FK constraint from another, big table, and the FK
column in that table is not indexed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Deleteing specific rows from a table which have similar values

I have the following table and I'd like to delete rows which have the same
values in the fields MergeFromURN and MergeToURN
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
3 100 300
4 700 800
5 700 800
After my query I'd like my table to look like the following: -
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
4 700 800
I've no idea how to carry out this delete query as whenever I try I also
seem to delete both rows which are the same. Can someone help me to do this.
Thanks for any help anyone can give me.Hi Stephan,
this should work. Just replace 'myTable' with your table name.
Depending on the amount of data, you might want to create some indexes on
the temp-table.
Micha.
SELECT tab1.RecNo, tab1.MergeFromURN, tab1.MergeToURN
INTO #temp
FROM myTable tab1
JOIN myTable tab2 ON (tab1.RecNo <> tab2.RecNo AND tab1.MergeFromURN =
tab2.MergeFromUrn AND tab1.MergeToUrn = tab2.MergeToUrn)
DELETE
FROM myTable
WHERE RecNo IN (SELECT RecNo
FROM #temp)
AND RecNo NOT IN (SELECT MIN(RecNo)
FROM #temp
GROUP BY MergeFromURN, MergeToURN)
DROP TABLE #temp
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:76146231-179D-497C-ADA3-AE707F279984@.microsoft.com...
>I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do
> this.
> Thanks for any help anyone can give me.|||Hi Stephen,
May this statement solve your Problem
CREATE TABLE TABLENAME(RecNo INT, MergeFromURN INT ,MergeToURN
INT)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 1,500, 600)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 2,100, 300)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 3,100, 300 )
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 4,700, 800)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 5,700, 800)
SELECT * FROM TABLENAME
DELETE FROM TABLENAME WHERE RECNO NOT IN (SELECT MIN(RECNO) FROM
TABLENAME GROUP BY MergeFromURN,MergeToURN)
SELECT * FROM TABLENAME
DROP TABLE TABLENAME
If this statement does solve your purpose let me know.
Please post DDL,DML statements so that others can test their queries.
With warm regards
Jatinder Singh
Stephen wrote:
> I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do thi
s.
> Thanks for any help anyone can give me.

Deleted row information cannot be accessed through the row

Hi,
I got following exception when accessing database using release version of
my C# application:
System.Data.DeletedRowInaccessibleException: Deleted row information cannot
be accessed through the row.
at System.Data.DataRow.GetDefaultRecord()
at System.Data.DataRow.get_Item(String columnName)
at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
Database\TwsUser.cs:line 185
at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
e) in C:\ZCAP\TWS\Tws.cs:line 2068
at System.Windows.Forms.Control.OnLayout(LayoutEventA rgs levent)
at System.Windows.Forms.ScrollableControl.OnLayout(La youtEventArgs
levent)
at System.Windows.Forms.Control.PerformLayout(LayoutE ventArgs args)
at System.Windows.Forms.Control.PerformLayout()
at ZCAP.ZAPP.Tws.InitializeComponent() in
C:\ZCAP\TWS\Tws.designer.cs:line 4025
at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
In following 2 situations, there is no exception:
(1) Running under debug version
(2) In visual studio, if I select "Release", then click "Debug" -> "Start
Debugging"
Your help is highly appreciated!
Hank
"Hang" <hyuan@.zcap.net> wrote in message
news:#LcxwJBaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I got following exception when accessing database using release version of
> my C# application:
> System.Data.DeletedRowInaccessibleException: Deleted row information
> cannot be accessed through the row.
> at System.Data.DataRow.GetDefaultRecord()
> at System.Data.DataRow.get_Item(String columnName)
> at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
> Database\TwsUser.cs:line 185
> at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
> e) in C:\ZCAP\TWS\Tws.cs:line 2068
> at System.Windows.Forms.Control.OnLayout(LayoutEventA rgs levent)
> at System.Windows.Forms.ScrollableControl.OnLayout(La youtEventArgs
> levent)
> at System.Windows.Forms.Control.PerformLayout(LayoutE ventArgs args)
> at System.Windows.Forms.Control.PerformLayout()
> at ZCAP.ZAPP.Tws.InitializeComponent() in
> C:\ZCAP\TWS\Tws.designer.cs:line 4025
> at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
> at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
> In following 2 situations, there is no exception:
> (1) Running under debug version
> (2) In visual studio, if I select "Release", then click "Debug" -> "Start
> Debugging"
>
This is an ADO.NET question, not a SQL Server question. Try posting in the
ADO.NET groups.
David

Deleted row information cannot be accessed through the row

Hi,
I got following exception when accessing database using release version of
my C# application:
System.Data.DeletedRowInaccessibleException: Deleted row information cannot
be accessed through the row.
at System.Data.DataRow.GetDefaultRecord()
at System.Data.DataRow.get_Item(String columnName)
at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
Database\TwsUser.cs:line 185
at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
e) in C:\ZCAP\TWS\Tws.cs:line 2068
at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
levent)
at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
at System.Windows.Forms.Control.PerformLayout()
at ZCAP.ZAPP.Tws.InitializeComponent() in
C:\ZCAP\TWS\Tws.designer.cs:line 4025
at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
In following 2 situations, there is no exception:
(1) Running under debug version
(2) In visual studio, if I select "Release", then click "Debug" -> "Start
Debugging"
Your help is highly appreciated!
Hank"Hang" <hyuan@.zcap.net> wrote in message
news:#LcxwJBaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I got following exception when accessing database using release version of
> my C# application:
> System.Data.DeletedRowInaccessibleException: Deleted row information
> cannot be accessed through the row.
> at System.Data.DataRow.GetDefaultRecord()
> at System.Data.DataRow.get_Item(String columnName)
> at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
> Database\TwsUser.cs:line 185
> at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
> e) in C:\ZCAP\TWS\Tws.cs:line 2068
> at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
> at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
> levent)
> at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
> at System.Windows.Forms.Control.PerformLayout()
> at ZCAP.ZAPP.Tws.InitializeComponent() in
> C:\ZCAP\TWS\Tws.designer.cs:line 4025
> at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
> at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
> In following 2 situations, there is no exception:
> (1) Running under debug version
> (2) In visual studio, if I select "Release", then click "Debug" -> "Start
> Debugging"
>
This is an ADO.NET question, not a SQL Server question. Try posting in the
ADO.NET groups.
David

Deleted row information cannot be accessed through the row

Hi,
I got following exception when accessing database using release version of
my C# application:
System.Data.DeletedRowInaccessibleException: Deleted row information cannot
be accessed through the row.
at System.Data.DataRow.GetDefaultRecord()
at System.Data.DataRow.get_Item(String columnName)
at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
Database\TwsUser.cs:line 185
at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
e) in C:\ZCAP\TWS\Tws.cs:line 2068
at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
levent)
at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
at System.Windows.Forms.Control.PerformLayout()
at ZCAP.ZAPP.Tws.InitializeComponent() in
C:\ZCAP\TWS\Tws.designer.cs:line 4025
at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
In following 2 situations, there is no exception:
(1) Running under debug version
(2) In visual studio, if I select "Release", then click "Debug" -> "Start
Debugging"
Your help is highly appreciated!
Hank"Hang" <hyuan@.zcap.net> wrote in message
news:#LcxwJBaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I got following exception when accessing database using release version of
> my C# application:
> System.Data.DeletedRowInaccessibleException: Deleted row information
> cannot be accessed through the row.
> at System.Data.DataRow.GetDefaultRecord()
> at System.Data.DataRow.get_Item(String columnName)
> at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
> Database\TwsUser.cs:line 185
> at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
> e) in C:\ZCAP\TWS\Tws.cs:line 2068
> at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
> at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
> levent)
> at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
> at System.Windows.Forms.Control.PerformLayout()
> at ZCAP.ZAPP.Tws.InitializeComponent() in
> C:\ZCAP\TWS\Tws.designer.cs:line 4025
> at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
> at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
> In following 2 situations, there is no exception:
> (1) Running under debug version
> (2) In visual studio, if I select "Release", then click "Debug" -> "Start
> Debugging"
>
This is an ADO.NET question, not a SQL Server question. Try posting in the
ADO.NET groups.
David

deleted object for trigger

Is 'deleted' object available for a table with Identity field? I try the
scripts as following. It gets me the error "Invalid object name 'deleted'".
How can I bypass it? Thanks.
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
GO
INSERT dbo.myTable_History SELECT * FROM deleted
GO
SET IDENTITY_INSERT dbo.myTable_History OFF
GOGO terminates batches in Query Analyzer. So your trigger does nothing
more than SET IDENTITY_INSERT ON for the table. Remove the GOs and it
should work...
That said, why does your table have an IDENTITY column if you're just
bypassing it from the trigger anyway?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:83E83E75-16E6-4473-B3AB-BEA946A60B08@.microsoft.com...
> Is 'deleted' object available for a table with Identity field? I try the
> scripts as following. It gets me the error "Invalid object name
'deleted'".
> How can I bypass it? Thanks.
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> GO
> INSERT dbo.myTable_History SELECT * FROM deleted
> GO
> SET IDENTITY_INSERT dbo.myTable_History OFF
> GO
>|||The GO keyword should be only at the end of the trigger, not after each
statement.
To use SET IDENTITY_INSERT you must specify the columns (it doesn't
work with *)
Razvan|||1.
I removed all GO statement. Now the trigger is:
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
INSERT dbo.myTable_History SELECT * FROM deleted
SET IDENTITY_INSERT dbo.myTable_History OFF
It still doesn't work though. The error shows:
Error 8101: An explicit value for the identity column in ... can only be
specified when a columne list is used and IDENTITY_INSERT is ON.
Do I miss anything?
2.
You raised a good quesiton. The reason I have IDENTITY field on the history
tables is just because they are created in the SQL script by
SELECT * INTO MyTable1_History FROM MyTable1
SELECT * INTO MyTable2_History FROM MyTable2
............
Since there is no short cut to change table's IDENTITY field to be plain int
field, we keep the IDENTITY field in the history table.
"Adam Machanic" wrote:

> GO terminates batches in Query Analyzer. So your trigger does nothing
> more than SET IDENTITY_INSERT ON for the table. Remove the GOs and it
> should work...
> That said, why does your table have an IDENTITY column if you're just
> bypassing it from the trigger anyway?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:83E83E75-16E6-4473-B3AB-BEA946A60B08@.microsoft.com...
> 'deleted'".
>
>|||Thanks for your reply. What do you mean 'To use SET IDENTITY_INSERT you must
specify the columns'. Shouldn't I SET IDENTITY_INSERT ON to the table?
"Razvan Socol" wrote:

> The GO keyword should be only at the end of the trigger, not after each
> statement.
> To use SET IDENTITY_INSERT you must specify the columns (it doesn't
> work with *)
> Razvan
>|||A) Use a column list
B) Stop being lazy and create your tables using Data Definition Language.
Why would you take a shortcut that doesn't save much time and is going to
make your database worse?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:BBCE9EF9-1D63-4947-9B38-3A2F96195A3C@.microsoft.com...
> 1.
> I removed all GO statement. Now the trigger is:
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History SELECT * FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> It still doesn't work though. The error shows:
> Error 8101: An explicit value for the identity column in ... can only be
> specified when a columne list is used and IDENTITY_INSERT is ON.
> Do I miss anything?
> 2.
> You raised a good quesiton. The reason I have IDENTITY field on the
history
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
> ............
> Since there is no short cut to change table's IDENTITY field to be plain
int
> field, we keep the IDENTITY field in the history table.
>
> "Adam Machanic" wrote:
>
nothing
just
the|||You have to write the column list.
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
INSERT dbo.myTable_History (col1, ..., coln)
SELECT col1,..., coln FROM deleted
SET IDENTITY_INSERT dbo.myTable_History OFF
go

> You raised a good quesiton. The reason I have IDENTITY field on the histor
y
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
select col2, ..., coln
into t
from table1
alter table t
add col1 int not nul
go
AMB
"Sean" wrote:
> 1.
> I removed all GO statement. Now the trigger is:
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History SELECT * FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> It still doesn't work though. The error shows:
> Error 8101: An explicit value for the identity column in ... can only be
> specified when a columne list is used and IDENTITY_INSERT is ON.
> Do I miss anything?
> 2.
> You raised a good quesiton. The reason I have IDENTITY field on the histor
y
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
> ............
> Since there is no short cut to change table's IDENTITY field to be plain i
nt
> field, we keep the IDENTITY field in the history table.
>
> "Adam Machanic" wrote:
>|||Sean
To use INSERT to put rows in a table with INDENTITY_INSERT ON, you must
explicitly list all the columns in the table. Please read about the
variations of the INSERT command in the Books Online.
It would be something like this:
INSERT dbo.myTable_History (name_of_column1, name_of_column_2, ...)
SELECT * FROM deleted
One of the columns names needs to be the name of the identity column, in the
right position.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:3A42CE27-B412-424B-9A12-35882AB63F4F@.microsoft.com...
> Thanks for your reply. What do you mean 'To use SET IDENTITY_INSERT you
> must
> specify the columns'. Shouldn't I SET IDENTITY_INSERT ON to the table?
>
> "Razvan Socol" wrote:
>|||Thanks for the reply. I got it.
"Alejandro Mesa" wrote:
> You have to write the column list.
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History (col1, ..., coln)
> SELECT col1,..., coln FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> go
>
> select col2, ..., coln
> into t
> from table1
> alter table t
> add col1 int not nul
> go
>
> AMB
> "Sean" wrote:
>|||Thanks, Kalen.
"Kalen Delaney" wrote:

> Sean
> To use INSERT to put rows in a table with INDENTITY_INSERT ON, you must
> explicitly list all the columns in the table. Please read about the
> variations of the INSERT command in the Books Online.
> It would be something like this:
> INSERT dbo.myTable_History (name_of_column1, name_of_column_2, ...)
> SELECT * FROM deleted
> One of the columns names needs to be the name of the identity column, in t
he
> right position.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:3A42CE27-B412-424B-9A12-35882AB63F4F@.microsoft.com...
>
>

DELETE where syntax ... need help :)

I have a table with the following columns,

NAME, TYPE, TAG

And there may be 'duplicates' on name and type.

How can I delete them??

I want to delete all with duplicate NAME and TYPEActually I want to delete all rows which is duplicate on NAME and
TYPE.

Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B

If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.

This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.

The result should give me the tables (occurences) that is missing in
one of the databases. The TAG tells me which.|||Actually I want to delete all rows which is duplicate on NAME and

Quote:

Originally Posted by

TYPE.

You can remove the TAG criteria from the original statement I posted so that
all of the rows with duplicate NAME and TYPE values are removed:

CREATE TABLE dbo.PMTOOLS
(
[NAME] varchar(30) NOT NULL,
[TYPE] varchar(30) NOT NULL,
[TAG] varchar(30) NOT NULL
)
GO

INSERT INTO dbo.PMTOOLS
SELECT 'TEST1', '12', 'A'
UNION ALL SELECT 'TEST1', '12', 'B'
UNION ALL SELECT 'TEST2', '12', 'A'
UNION ALL SELECT 'TEST4', '14', 'B'
GO

DELETE dbo.PMTOOLS
FROM dbo.PMTOOLS
JOIN (
SELECT NAME, TYPE
FROM dbo.PMTOOLS
GROUP BY NAME, TYPE
HAVING COUNT(*) 1
) AS dups
ON
dups.NAME = PMTOOLS.NAME AND
dups.TYPE = PMTOOLS.TYPE

--
Hope this helps.

Dan Guzman
SQL Server MVP

"cobolman" <olafbrungot@.hotmail.comwrote in message
news:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.
>
Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B
>
If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.
>
This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.
>
The result should give me the tables (occurences) that is missing in
one of the databases. The TAG tells me which.
>
>
>

|||On Jun 28, 4:50 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.

>
You can remove the TAG criteria from the original statement I posted so that
all of the rows with duplicate NAME and TYPE values are removed:
>
CREATE TABLE dbo.PMTOOLS
(
[NAME] varchar(30) NOT NULL,
[TYPE] varchar(30) NOT NULL,
[TAG] varchar(30) NOT NULL
)
GO
>
INSERT INTO dbo.PMTOOLS
SELECT 'TEST1', '12', 'A'
UNION ALL SELECT 'TEST1', '12', 'B'
UNION ALL SELECT 'TEST2', '12', 'A'
UNION ALL SELECT 'TEST4', '14', 'B'
GO
>
DELETE dbo.PMTOOLS
FROM dbo.PMTOOLS
JOIN (
SELECT NAME, TYPE
FROM dbo.PMTOOLS
GROUP BY NAME, TYPE
HAVING COUNT(*) 1
) AS dups
ON
dups.NAME = PMTOOLS.NAME AND
dups.TYPE = PMTOOLS.TYPE
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"cobolman" <olafbrun...@.hotmail.comwrote in message
>
news:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...
>

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.

>

Quote:

Originally Posted by

Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B

>

Quote:

Originally Posted by

If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.

>

Quote:

Originally Posted by

This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.

>

Quote:

Originally Posted by

The result should give me the tables (occurence) that is missing in
one of the databases. The TAG tells me which.

cobolman,

I may be reading more into this than I should, but I am assuming you
want to keep one row for each set of dups. Dan's script will remove
all occurrences of the dup rows.

Do you have a sequential unique ID, or timestamp type of column on the
table? Let us know the details (schema) if you do and I'll post a
solution for you.

-- Bill|||Bill,

I do want to remove all occurences of the dup rows.
The result set should only hold the ones that did not have any dups.

Thanks to both (Dan and Bill) :)|||I guess I need help on another one as well, ...

I'd like to do a select to find all the foreign keys of a given table,
and the foreign_key columns.. (Sybase).

This SQL gives me what I want :

Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
b.foreign_column_id, b.primary_column_id, c.column_id
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
where a.foreign_table_id= XXX

But, .. what I'd really like is to instead of the column_id's and
table_id's have the actual name. I can get this from systable and
syscolumn, but I'm not sure how to write the sql|||Hmm...could this be it?

Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
c.table_name, b.foreign_column_id,
(select column_name from sys.syscolumn where table_id =
a.foreign_table_id AND column_id = b.foreign_column_id),
b.primary_column_id,
(select column_name from sys.syscolumn where table_id =
a.primary_table_id AND column_id = b.primary_column_id)
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
JOIN SYS.SYSTABLE c ON
a.primary_table_id = c.table_id
where a.foreign_table_id=XXX|||cobolman (olafbrungot@.hotmail.com) writes:

Quote:

Originally Posted by

I guess I need help on another one as well, ...
>
I'd like to do a select to find all the foreign keys of a given table,
and the foreign_key columns.. (Sybase).

You are probably better off asking in comp.databases.sybase. It does not
seem from your queries that neither Sybase use their old system
tables anymore.

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

Delete using a linked server

Hi,

I am trying to delete records in a file on the AS400 using a linked server
and I am getting the following error message:

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"catalog"."schema"."table"'. There was a recoverable, provider-specific error, such as an RPC failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

The linked server is configured as follows:
"server type": other data source
"provider name": Microsoft OLE DB provider for ODBC drivers
"data source" references a system DSN name that uses the driver
"Client Access ODBC driver(32 bit)"
linked server options: "Data access" and "Use remote collation" boxes checked
provider options: "Dynamic parameters", "Nested queries", "Allow in process",
"Non transacted updates" boxes checked

The file on the AS400 is journaled and has a unique key.

The Select and Insert statements work like a charm but I can not for the life of me get the Delete or Update statements to work.

I have been struggling with this problem for over a month, so any help whatsoever would be tremendously appreciated.

Thanks!On the properties page for the linked server, are the RPC and RPC OUT check boxes checked?

That said, I never tried manipulating data on a linked server; only selects. Also, there is a different driver for AS/400s, I' racking my brain for the name, but it's not provided by IBM.

Regards,

hmscott|||Thanks for the reply.

I tried checking the RPC and RPC out boxes and I get the same error message.

As for the other driver, you're probably thinking of OLE DB provider for DB2.
I've have also tried using this driver without success.

My hunch is that there is an ODBC provider property(such as DBPROPSET_PROVIDERROWSET) that's not configured to handle updates or deletes, but I don't know how to display this information.

Are you familiar with OLE DB providers for ODBC or can you direct me to a
resource?|||Can you post the delete and update statements.|||Here's the statement I'm using in query analyzer.

DELETE FROM AS400LINKEDSERVER.SYSTEMNAME.LIBRARYNAME.FILENAME

The four part names have been changed here for privacy.

I have also tried OPENQUERY. Again, same error message.

Thanks.

DELETE transaction with SNAPSHOT isolation level - conflicts another table

Hi,

we are executing the following query in a stored procedure using snapshot isolation level:

DELETE FROM tBackgroundProcessProgressReport
FROM tBackgroundProcessProgressReport LEFT OUTER JOIN
tBackgroundProcess ON
tBackgroundProcess.BackgroundProcessProgressReportID =tBackgroundProcessProgressReport.BackgroundProcessProgressReportID LEFTOUTER JOIN
tBackgroundProcessProgressReportItem ON
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID =tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportID
WHERE (tBackgroundProcess.BackgroundProcessID IS NULL) AND
(tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportItemID IS NULL)

The query should delete records from tBackgroundProcessProgressReport which are not connected with the other two tables.
However, for some reasone we get the following exception:

System.Data.SqlClient.SqlException:Snapshot isolation transaction aborted due to update conflict. Youcannot use snapshot isolation to access table 'dbo.tBackgroundProcess'directly or indirectly in database 'RHSS_PRD_PT_Engine' to update,delete, or insert the row that has been modified or deleted by anothertransaction. Retry the transaction or change the isolation level forthe update/delete statement.

The exception specifies that we arenot allowed to update/delete/insert records in tBackgroundProcess, butthe query indeed deletes records from tBackgroundProcessProgressReport,not from the table in the exception.
Is the exception raised because of the join?

Has someone encountered this issue before?

Thanks,

Yani

Hi,

it looks like this forum is not the best place to ask, since it's dedicated to asp.net

So anybody with idea where i could ask for a solution for my problem?

Thanks in advance!

Delete this post

hello all,

I have the following script that does not work in windows XP SP2

//Script

sub UpLoad (UploadFileName)

dim b1

b1=&H4000000

set objStream =CreateObject("ADODB.Stream")

set objRecord =CreateObject("ADODB.Record")

set cnn =CreateObject("ADODB.Connection")

cnn.Open "provider=MSDAIPP.DSO;data source= http://192.168.0.100"

objRecord.Open UploadFileName,cnn, 3,b1

objStream.Type = 1 'adTypeBinary

objStream.Open objRecord,3,4 /////// Error line //////

objStream.LoadFromFile "c:\"+UploadFileName

objStream.Flush

objStream.Close

objRecord.Close

Set objStream = Nothing

Set objRecord = Nothing

cnn.close

set cnn = nothing

end sub

Error Message is "No such interface supported"

Any idea whoud be appreciated highly

Would this post be served better at a different forum?|||

Hi admin,

this post is not in a good forum so please delete it for me.

thanks, mahsina

Delete statement trouble

I have an Oracle 8i database with a table called GM_INV_CST. This table has the following fields:

STORE_CD NOT NULL VARCHAR2(4)
SKU_NUM NOT NULL VARCHAR2(12)
PLUS_MINUS NOT NULL VARCHAR2(1)
RCV_DT NOT NULL DATE
UNIT_CST NOT NULL NUMBER(10,2)
QTY NOT NULL NUMBER(6)
CST_TOT NOT NULL NUMBER(13,2)

I'm using the following statement to identify duplicate entries of based on SKU_NUM, STORE_CD, and PLUS_MINUS, then identify the entries with zero (0) quantities in QTY and CST_TOT:

select base.cnt,base.sku_num,base.store_cd,gm_inv_cst.plu s_minus,
gm_inv_cst.rcv_dt,gm_inv_cst.unit_cst,gm_inv_cst.q ty,gm_inv_cst.cst_tot
from gm_inv_cst,
(select count(*)as cnt,sku_num,store_cd,plus_minus
from gm_inv_cst
group by sku_num,store_cd,plus_minus
having count(*)>1) base
where gm_inv_cst.cst_tot = 0
and gm_inv_cst.qty = 0
and gm_inv_cst.sku_num=base.sku_num
and gm_inv_cst.store_cd=base.store_cd
and base.plus_minus = gm_inv_cst.plus_minus

...the problem is I can't seem to find the right syntax to create a delete statement for these records, anyone?My first observation is that you might have better luck posting an Oracle question in the Oracle (http://www.dbforums.com/f4) forum. The folks that hang out in the engine specific forums can often suggest specific features of a given engine that make the job a lot simpler than doing it the way that is required by the SQL standard.

The next observation is that you haven't specified the primary key column(s). This would help a lot to determine the simplest answer to your question.

-PatP|||If you already identified duplicate records, can't you use this query to delete them? Minor modification would then be

DELETE FROM gm_inv_cst
WHERE (plus_minus, rcv_dt, unit_cst, qty, cst_tot) IN
(SELECT g.plus_minus, g.rcv_dt, g.unit_cst, g.qty, g.cst_tot
FROM gm_inv_cst g,
(SELECT COUNT(*) AS cnt, sku_num, store_cd, plus_minus
FROM gm_inv_cst
GROUP BY sku_num, store_cd, plus_minus
HAVING COUNT(*) > 1
) base
WHERE g.cst_tot = 0
AND g.qty = 0
AND g.sku_num = base.sku_num
AND g.store_cd = base.store_cd
AND g.plus_minus = base.plus_minus
);|||I think your statement is close however it returns well over 9000 records where my original select statement only returns 463?|||Your original consolidates the duplicates, there have to be at least twice as many rows in the target table as in your result set, and quite possibly more than that!

-PatP|||It is your data, after all ... if this statement is close, enhance it a little bit and it'll be just fine :)

DELETE Statement

Hi folks,
I am running the following statement on SQL Server 2000 Database:
DELETE from tablea
WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
AND effective_date < CONVERT(varchar(10),GETDATE(),120)
tablea has effective_date defined as smalldatetime.
When i run the sql, i get the following error:
DELETE failed because the following SET options have incorrect settings:
'ARITHABORT'
The effective_date column is not a computed column. Any idea as to why the
statement should genrate the error.Can you post the table DDL, including indexes?. Is tablea an indexed view or
does tablea have an index on a computed column?

> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
effective_date >= CONVERT(char(8), dateadd(day, -3, GETDATE()), 112)
effective_date < CONVERT(char(8), GETDATE(), 112)
AMB
"Ram" wrote:

> Hi folks,
> I am running the following statement on SQL Server 2000 Database:
> DELETE from tablea
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
> tablea has effective_date defined as smalldatetime.
> When i run the sql, i get the following error:
> DELETE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> The effective_date column is not a computed column. Any idea as to why the
> statement should genrate the error.|||Is there a calculated column or an indexed view on the table you are
attempting to delete from?
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:4EF71394-62B6-49C0-8BF6-3FA849F22770@.microsoft.com...
> Hi folks,
> I am running the following statement on SQL Server 2000 Database:
> DELETE from tablea
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
> tablea has effective_date defined as smalldatetime.
> When i run the sql, i get the following error:
> DELETE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> The effective_date column is not a computed column. Any idea as to why the
> statement should genrate the error.|||Hi,
Here is the DDL:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tablea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tablea]
GO
CREATE TABLE [dbo].[tablea] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[iex_id] [int] NOT NULL ,
[mu_id] [int] NOT NULL ,
[effective_date] [smalldatetime] NOT NULL ,
[start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
Indexes:
IDX_mu_id mu_id
IDX_mu_id_agent_id mu_id, iex_id
PK_tblASAgentSchedule iex_id, mu_id, effective_date
Ram
"Alejandro Mesa" wrote:
> Can you post the table DDL, including indexes?. Is tablea an indexed view
or
> does tablea have an index on a computed column?
>
> effective_date >= CONVERT(char(8), dateadd(day, -3, GETDATE()), 112)
> effective_date < CONVERT(char(8), GETDATE(), 112)
>
> AMB
> "Ram" wrote:
>|||I can not reproduce the error.
CREATE TABLE [dbo].[tablea] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[iex_id] [int] NOT NULL ,
[mu_id] [int] NOT NULL ,
[effective_date] [smalldatetime] NOT NULL ,
[start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL,
constraint PK_tblASAgentSchedule primary key (iex_id, mu_id, effective_date)
) ON [PRIMARY]
GO
DELETE from tablea
WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
AND effective_date < CONVERT(varchar(10),GETDATE(),120)
go
drop table tablea
go
AMB
"Ram" wrote:
> Hi,
> Here is the DDL:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tablea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tablea]
> GO
> CREATE TABLE [dbo].[tablea] (
> [row_id] [int] IDENTITY (1, 1) NOT NULL ,
> [iex_id] [int] NOT NULL ,
> [mu_id] [int] NOT NULL ,
> [effective_date] [smalldatetime] NOT NULL ,
> [start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL
> ) ON [PRIMARY]
> GO
> Indexes:
> IDX_mu_id mu_id
> IDX_mu_id_agent_id mu_id, iex_id
> PK_tblASAgentSchedule iex_id, mu_id, effective_date
> Ram
> "Alejandro Mesa" wrote:
>|||The whole design looks wrong. A SQL programmer does not use IDENTITY
for keys, only for display in cursors. Times are not stored in CHAR(n),
but in temporal data types which they do not have to CONVERT() in weird
ways. Audit information is not stored in the tables, but externally
and best done with log tools.
Having something go into effect is a status change, so where is the
status code? You have two identifiers that are not in this table, so
where is the DRI to the base tables that define them? My guess is that
it should look like this:
CREATE TABLE FoobarHistory
(iex_id INTEGER NOT NULL
REFERENCES IEX(iex_id),
mu_id INTEGER NOT NULL
REFERENCES MU(mu_id),
status_code INTEGER NOT NULL, -- let 0 be effective
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means current
PRIMARY KEY (iex_id, mu_id));
Then you can write:
DELETE FROM Foobar
WHERE status = 0;|||Hi,
I dropped and recreated the index on the table.
The delete statement worked fine after that.
Thank you
"Andrew J. Kelly" wrote:

> Is there a calculated column or an indexed view on the table you are
> attempting to delete from?
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4EF71394-62B6-49C0-8BF6-3FA849F22770@.microsoft.com...
>
>

Delete replication

I defined replication in my SQL Server 2000. I have deleted everything except
a remote server. I try to delete this and I get the following error:
Microsoft SQL_DMO (ODBC SQLState:42000) Error 15190: There are still remote
logins for the server "Name of the server".
Where can I find these remote logins? I had a look at the remote server and
I can not see any login.
Thanks
This is probably the distributor_admin account. I would not touch this
account even if you don't require replication anymore.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Laura Pradre" <LauraPradre@.discussions.microsoft.com> wrote in message
news:0F20D434-1D9F-43C2-A53B-3710B6439C75@.microsoft.com...
>I defined replication in my SQL Server 2000. I have deleted everything
>except
> a remote server. I try to delete this and I get the following error:
> Microsoft SQL_DMO (ODBC SQLState:42000) Error 15190: There are still
> remote
> logins for the server "Name of the server".
> Where can I find these remote logins? I had a look at the remote server
> and
> I can not see any login.
> Thanks

Delete recordsets with same Date and Line

Hi All!

I need help with a Statement!
I am working with an Access2000 DB.
I have the following Problem.

ChNrLinieDatum Code 39 Stckzahl BHL1 BHL2 BMRH
582-064L2.1008.03.2005 02:30:00FCAA14821701
582-064L2.1008.03.2005 02:30:00FCAA14871701
582-114L2.1208.03.2005 01:00:00FAC827501240
582-114L2.1208.03.2005 01:00:00FAC827441240
582-114L2.1208.03.2005 01:00:00FAC827501240
582-094L2.707.03.2005 19:45:00FAE74323481
582-094L2.707.03.2005 19:45:00FAE74489481
582-094L2.707.03.2005 19:45:00FAE74489481
581-294L2.807.03.2005 18:20:00FA8V2658221
581-294L2.807.03.2005 18:20:00FA8V2652221
581-294L2.807.03.2005 18:20:00FA8V2658221
582-114L2.1207.03.2005 17:45:00FAAR20721236
As you can see I have a few recordsets that are double. The Thing is, there
is an ID that makes them different.

I need a Statement that deletes the surplus records where 'Datum' and
'Linie' are identical to another record. 1 record has to remain of course.
I thought of something like this.

DELETE FROM tbAuswert
WHERE EXISTS(
SELECT *
FROM tbAuswert
WHERE (Linie AND Datum)
IN (
SELECT Linie AND Datum AS Suchkrit
FROM tbAuswert
GROUP BY Suchkrit
HAVING ((Count(Suchkrit)>1)
)
)

But I get an error:
You wanted to execute a Query that did not have the following expression
'Not Linie = 0 And Not Datum = 0' in its Aggregatefunction

Perhaps you ccan help me.

Thanks
Julia

--
Message posted via http://www.sqlmonster.comJulia Hrtfelder via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I need help with a Statement!
> I am working with an Access2000 DB.
>...
> I need a Statement that deletes the surplus records where 'Datum' and
> 'Linie' are identical to another record. 1 record has to remain of course.
> I thought of something like this.
> DELETE FROM tbAuswert
> WHERE EXISTS(
> SELECT *
> FROM tbAuswert
> WHERE (Linie AND Datum)
> IN (
> SELECT Linie AND Datum AS Suchkrit
> FROM tbAuswert
> GROUP BY Suchkrit
> HAVING ((Count(Suchkrit)>1)
> )
> )
> But I get an error:
> You wanted to execute a Query that did not have the following expression
> 'Not Linie = 0 And Not Datum = 0' in its Aggregatefunction
> Perhaps you ccan help me.

If you don't have a primary that uniquely identifies each row, then
this will be very difficult, as SQL is designed to operate only from the
data in the tables.

It's possible that you can add a column that gives you a unique ID. Had
you been using SQL Server, I could have showed you how. However, since
you are using Access, you are better off posting your question to a
forum for Access. There are considerable differences between the SQL in
SQL Server and Access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 17 May 2005 12:09:04 GMT, Julia Hrtfelder via SQLMonster.com
wrote:

>Hi All!
>I need help with a Statement!
>I am working with an Access2000 DB.
>I have the following Problem.
(snip)
>As you can see I have a few recordsets that are double. The Thing is, there
>is an ID that makes them different.
>I need a Statement that deletes the surplus records where 'Datum' and
>'Linie' are identical to another record. 1 record has to remain of course.

Hi Julia,

The following will work for SQL Server. I'm not sure about Access though
(it deviates from the standard in other ways than SQL Server does <g>),
so test it first, and repost in an Access group if this doesn't work.

Note: I assume that the ID column is called "ID" and that you want to
retain the row with the lowest ID value.

DELETE FROM tbAuswert
WHERE EXISTS
(SELECT *
FROM tbAuswert AS a2
WHERE a2.Datum = tbAuswert.Datum
AND a2.Linie = tbAuswert.Linie
AND a2.ID > tbAuswert.ID)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||That one really worked out!

Thank you so much. You are great!

Julia

--
Message posted via http://www.sqlmonster.com