Friday, March 30, 2012
Help on Updategram
"Ambiguous delete, unique identifier required
Transaction aborted"
I am trying to delete several rows where a column equals
a certain value like "7". THe errors occurs because
SQLXML appears to only allow from a single row to be
deleted at a time. Here is what the Profiler shows:
SET XACT_ABORT ON
BEGIN TRAN
DECLARE @.eip INT, @.r__ int, @.e__ int
SET @.eip = 0
DELETE tblNote WHERE ( pkAccountID=6 ) ; SELECT @.e__ =
@.@.ERROR, @.r__ = @.@.ROWCOUNT
IF (@.e__ != 0 OR @.r__ != 1) SET @.eip = 1
IF (@.r__ > 1) RAISERROR ( N'SQLOLEDB Error Description:
Ambiguous delete, unique identifier required Transaction
aborted ', 16, 1)
ELSE IF (@.r__ < 1) RAISERROR ( N'SQLOLEDB Error
Description: Empty delete, no deletable rows found
Transaction aborted ', 16, 1)
IF (@.eip != 0) ROLLBACK ELSE COMMIT
SET XACT_ABORT OFF
WHY?!?!?!?!?!?!
Regards,
Ron
To delete multiple rows in an Updategram, you have to specify each row to be
deleted individually in the "before" element - there's no equivalent of a
wildcard delete.
From SQLXML 3.0 BOL:
"If an element that is specified in the updategram either matches more than
one row in the table or does not match any table row, the updategram returns
an error and cancels the entire <sync> block. Only one record at a time can
be deleted by an element in the updategram."
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Ron Antinori" <rantinori@.bluestoneinfo.com> wrote in message
news:264601c48e1c$9a727f80$a301280a@.phx.gbl...
I am recieveing this error.
"Ambiguous delete, unique identifier required
Transaction aborted"
I am trying to delete several rows where a column equals
a certain value like "7". THe errors occurs because
SQLXML appears to only allow from a single row to be
deleted at a time. Here is what the Profiler shows:
SET XACT_ABORT ON
BEGIN TRAN
DECLARE @.eip INT, @.r__ int, @.e__ int
SET @.eip = 0
DELETE tblNote WHERE ( pkAccountID=6 ) ; SELECT @.e__ =
@.@.ERROR, @.r__ = @.@.ROWCOUNT
IF (@.e__ != 0 OR @.r__ != 1) SET @.eip = 1
IF (@.r__ > 1) RAISERROR ( N'SQLOLEDB Error Description:
Ambiguous delete, unique identifier required Transaction
aborted ', 16, 1)
ELSE IF (@.r__ < 1) RAISERROR ( N'SQLOLEDB Error
Description: Empty delete, no deletable rows found
Transaction aborted ', 16, 1)
IF (@.eip != 0) ROLLBACK ELSE COMMIT
SET XACT_ABORT OFF
WHY?!?!?!?!?!?!
Regards,
Ron
Help On Trigger For Delete
The Trigger works fine when only one record is deleted but no when more than record is deleted; it only works for 1 and there is no error message.
For each row deleted I need to update a column in another table (PACCESOS_CAB).
This the trigger...
CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS
declare @.mdias as int
declare @.mFKFeria as int
declare @.mtipo as char(1)
declare @.mfkcontacto as varchar(7)
if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)
begin
select @.mfkferia=m.fkferia, @.mfkcontacto = m.fkcontacto, @.mtipo = m.tipo, @.mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo
update PACCESOS_CAB set diasvisita = @.mdias -1 where FKFeria= @.mFKFeria and FKContacto=@.mFKContacto and Tipo=@.mTipo
end
Thanks in advanced."Inside every large program is a small program screaming to get out."
Your trigger is MUCH too complicated. Best I can figure, this is all you need:
CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS
begin
update PACCESOS_CAB
set PACCESOS_CAB.diasvisita = PACCESOS_CAB.diasvisita -1
from PACCESOS_CAB
inner join deleted
on PACCESOS_CAB.FKFeria = deleted.FKFeria
and PACCESOS_CAB.fkcontacto = deleted.fkcontacto
and PACCESOS_CAB.Tipo = deleted.Tipo
end|||It worked fine, sinco I keep on not Knowing the problem with the first solution.
anyway, lot of thanks blindman.|||Problems with your first solution:
This phrase is completely unnecessary, as records between the two tables will be matched in the UPDATE query:
"if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)"
These variables are not required, because as you can see from the solution all the records can be updated simultaneously with a single UPDATE statement, so it is not necessary to store values in temporary variables:
"declare @.mdias as int
declare @.mFKFeria as int
declare @.mtipo as char(1)
declare @.mfkcontacto as varchar(7)"
This statement fails when more than one record is deleted, because your FROM clause will return more than one record, and the parameters can hold only single values:
"select @.mfkferia=m.fkferia, @.mfkcontacto = m.fkcontacto, @.mtipo = m.tipo, @.mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo"
Friday, March 23, 2012
help on find duplicates and delete procedure
my PERSON table is this below:
ID name fname ownerid id2
1 a b
2 c c
3 e f
4 a b 1 10
5 c c 2 11
I have this query below that returns records 1 and 4 and 2 and 5 since they have the same name and fname
select * from ( Select name ,fname, count(1) as cnt from PERSON group by
name,Fname ) where cnt > 1
ID name fname ownerid id2
1 a b
4 a b 1 10
2 c c
5 c c 2 11
With this result I need to delete the second record of each group but update the first records with the ownerid and id2 of the second record that would be deleted... I don't know how to proceed with this..
thanks
alexhow do you know which one is the "second" record?
and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?|||how do you know which one is the "second" record?
and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?
the second record is the one with ownerid and id2 well the reason behind is that the second record is inserted from another table and the id of the first one is the one i need due to relationships with other tables|||I see trouble ahead ;) If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?
Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).
Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.|||I see trouble ahead ;) If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?
Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).
Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.
yes thanks for your suggestion will try it then...|||this might work for u ...
[code]
delete from Table1 where ID in
(select ID from Table1, (Select name ,fname, count(*) as cnt from Table1 group by name,Fname having count(*) > 1) as xx
where Table1.name=xx.name and Table1.fname=xx.fname and Table1.ownerid is not null and Table1.id2 is not null)
[code]
Help on Emails & scheduled Jobs
The name of the Server was changed which in turn gave me the following
error when I tried to delete the jobs
Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server.
The job was not saved.
Knowing that it has to do with the Originator server in the sysjobs
table; I hacked the table ( now I think not a good idea ) and deleted
all the jobs that I wanted to delete and performed the same task on
sysjobservers.
Now although I have accomplished the deletion of the jobs.
But I still get emails ( job failure notifications) for the deleted
jobs exactly at the time they were scheduled.
This is driving me crazy as I do not how to stop these emails .
Any help is appreciated.
Thank youcheck the sysjobsteps and the sysjobschedules tables. Since you have
deleted the entries from the sysjobs table, you will not be able to
tell by job_id.
But if you look into the command and step name (if you have given a
meaningful name when you create the step, that will be easier to spot
out the email notification steps) from the SYSJOBSTEPS table and the
name (if you have given a meaningful name when you schedule the job)
from the SYSJOBSCHEDULES table.
Obviousbly it is not advisable to modify the system tables directly but
since we have a bad start already, so ... find the records (email
notification step and schedule) and delete it from the tables (backup
the the table first).
Mel|||Hi
Thank you for the info.
I did see job steps and job schedules of the deleted jobs in the table.
So I wenty ahead and deleted them .
Now I have the data for only the job ( that I want ).
Hopefully this will stop emails being sent out (notifcation) for
deleted jobs.
Thank you again|||Hi All,
Removing the records from sysjobschedules and sysjobsteps did not help.
I am still receiving emails.
Please advice|||You mentioned the name of a server was changed, was it the target
server or the master server where the job was stored?
I believe that you have deleted the jobs on the MSX server (mentioned
in your post). It may be the target server not yet received the new
set of instruction (do nothing - jobs have deleted). Run the stored
procedure as below to enforce it to happen (on the master job server):
USE msdb
EXEC sp_resync_targetserver 'target server name'
sp_resync_targetserver deletes the current set of instructions for the
target server and posts a new set for the target server to download.
The new set consists of an instruction to delete all multiserver jobs,
followed by an insert for each job currently targeted at the server.
Mel|||If the above doesn't help.
Run this to see what is available for the target servers to download
from the master.
sp_help_downloadlist
To force a target server to poll the master server.
If you make changes to multiserver job definitions outside of SQL
Server Enterprise Manager (which it is in this case), you must post the
changes to the download list so that target servers can download the
updated job again. To ensure that target servers have the most current
job definitions, post an INSERT instruction after you update the
multiserver job:
EXECUTE sp_post_msx_operation 'INSERT', 'JOB', '<job id>'
Check BOL for more details for the above stored procedures.
Mel
Help on Emails & scheduled Jobs
The name of the Server was changed which in turn gave me the following
error when I tried to delete the jobs
Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server.
The job was not saved.
Knowing that it has to do with the Originator server in the sysjobs
table; I hacked the table ( now I think not a good idea ) and deleted
all the jobs that I wanted to delete and performed the same task on
sysjobservers.
Now although I have accomplished the deletion of the jobs.
But I still get emails ( job failure notifications) for the deleted
jobs exactly at the time they were scheduled.
This is driving me crazy as I do not how to stop these emails .
Any help is appreciated.
Thank youcheck the sysjobsteps and the sysjobschedules tables. Since you have
deleted the entries from the sysjobs table, you will not be able to
tell by job_id.
But if you look into the command and step name (if you have given a
meaningful name when you create the step, that will be easier to spot
out the email notification steps) from the SYSJOBSTEPS table and the
name (if you have given a meaningful name when you schedule the job)
from the SYSJOBSCHEDULES table.
Obviousbly it is not advisable to modify the system tables directly but
since we have a bad start already, so ... find the records (email
notification step and schedule) and delete it from the tables (backup
the the table first).
Mel|||Hi
Thank you for the info.
I did see job steps and job schedules of the deleted jobs in the table.
So I wenty ahead and deleted them .
Now I have the data for only the job ( that I want ).
Hopefully this will stop emails being sent out (notifcation) for
deleted jobs.
Thank you again|||Hi All,
Removing the records from sysjobschedules and sysjobsteps did not help.
I am still receiving emails.
Please advice|||You mentioned the name of a server was changed, was it the target
server or the master server where the job was stored?
I believe that you have deleted the jobs on the MSX server (mentioned
in your post). It may be the target server not yet received the new
set of instruction (do nothing - jobs have deleted). Run the stored
procedure as below to enforce it to happen (on the master job server):
USE msdb
EXEC sp_resync_targetserver 'target server name'
sp_resync_targetserver deletes the current set of instructions for the
target server and posts a new set for the target server to download.
The new set consists of an instruction to delete all multiserver jobs,
followed by an insert for each job currently targeted at the server.
Mel|||If the above doesn't help.
Run this to see what is available for the target servers to download
from the master.
sp_help_downloadlist
To force a target server to poll the master server.
If you make changes to multiserver job definitions outside of SQL
Server Enterprise Manager (which it is in this case), you must post the
changes to the download list so that target servers can download the
updated job again. To ensure that target servers have the most current
job definitions, post an INSERT instruction after you update the
multiserver job:
EXECUTE sp_post_msx_operation 'INSERT', 'JOB', '<job id>'
Check BOL for more details for the above stored procedures.
Mel
Help on delete statement
I have two tables.
Table1:
MySymbol, BloombergSymbol
A, A_Bloomberg
B, B_Bloomberg
Table2:
MySymbol, Open, High, Low, Close
A,...
A,...
B,...
B,...
I want to perform one task--Delete all the records in table2 whose MySymbol matches one given BloombergSymbol in table1.
3x.which dbms is this?|||Try this query:
Delete Table2.* from Table1 LEFT JOIN Table2 ON Table1.MySymbol = Table2.MySymbol|||Alternate syntax :
DELETE FROM table2 WHERE MySymbol IN (SELECT MySymbol FROM table1)
n.b. I have based this on MySQL syntax which may or may not work in your DB.|||sql server
which dbms is this?|||this one works.
thank you guys.
Alternate syntax :
DELETE FROM table2 WHERE MySymbol IN (SELECT MySymbol FROM table1)
n.b. I have based this on MySQL syntax which may or may not work in your DB.|||For SQL Server use:
DELETE table2
FROM table2
INNER JOIN table1 ontable2.MySymbol = table1.MySymbol
Monday, March 12, 2012
help needed to drop a table!!!
hi,
can any one tell me how to drop a table...i tried to delete using the command :
drop table sample;
but it displays an error stating that,
Cannot drop the table 'dbo.sample', because it does not exist or you do not have permission.
pls post the reply asap
-Sweety
To verify that the table does indeed exists, try:
SET ROWCOUNT 1
SELECT * FROM MyTable
SET ROWCOUNT 0
Then if you receive data, and the statement DROP TABLE MyTable again fails, check your permissions. It is possible that your login does not have permission to remove a table.
|||Make sure you're using the correct db.
USE MyTest
GO
DROP TABLE Test
Adamus
|||check whether the table exists in the db at all... if exists , see who owns it.... give fully qualified name.
in SQL 2005
select Name from Sys.Objects where name='tablename'
Drop table databasename.schema.Tablename
SQL 2000
select Name from SysObjects where name='tablename'
Drop Table databasename.owner.Tablename
Madhu
|||
May be that table actually does not exist
|||sweet_salt wrote:
hi,
can any one tell me how to drop a table...i tried to delete using the command :
drop table sample;
but it displays an error stating that,
Cannot drop the table 'dbo.sample', because it does not exist or you do not have permission.
pls post the reply asap
-Sweety
Check that the account that you are using to run this query has the appropriate permission to do so.
Check the user properties in the security node.
Friday, March 9, 2012
Help needed for Sql2005 Transaction Logs
Hi,
I have run a lot of insert/ delete, update queries on a database in sql 2005 for a couple of months
Is there a way to track when and what are sql transactions that are have been executed?
Thanks in advance for your time and help.
whitze
You can monitor the activity in your database by running sql profiler........but this can be performed when you want to identify the bottlenecks in your db or in your server.........but it is not advisable to run it always....if you have the trace which is captured when those DML's were performed you can track it .......else i dont think its possible..........im not too sure about it......|||If you have a complete trail of the Transaction Logs, you could use one of the several third party log tools to accomplish your task.
If you do not have a complete trail of Transaction Logs, then most likely, you will not be able get that information.
Friday, February 24, 2012
help me ples
i Am trying to add delete and update some data in sql database in web developer but there is some problem no error in my cod ............ can anyone tell me the right meathead of add update and delete data from database pless
my code
Partial Class _Default
Inherits System.Web.UI.Page
Protected sqlConnection1 As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
Protected cmd As New System.Data.SqlClient.SqlCommand
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cID, cName, cEmail As String
cID = TextBox1.Text
cName = TextBox2.Text
cEmail = TextBox3.Text
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "INSERT Customers (ID, Name, Email) VALUES (" & cID & ",'" & cName & "', '" & cEmail & "')"
cmd.Connection = sqlConnection1
sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()
End Sub
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim cName, cEmail As String
Dim cID As Integer
cID = Val(TextBox1.Text)
cName = TextBox2.Text
cEmail = TextBox3.Text
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "UPDATE Customers SET Email = '" & cEmail & "' WHERE ID = " & cID & ""
cmd.Connection = sqlConnection1
sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()
End Sub
Private Function PrepareStr(ByVal strValue As String) As String
If strValue.Trim() = "" Then
Return "NULL"
Else
Return "'" & strValue.Trim() & "'"
End If
End Function
Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim cName, cEmail As String
Dim cID As Integer
cID = Val(TextBox1.Text)
cName = TextBox2.Text
cEmail = TextBox3.Text
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "Delete Customers WHERE ID = " & cID & ""
cmd.Connection = sqlConnection1
sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()
End Sub
End Class
First, does your code execute? If you put a breakpoint in your button1_click event, does it get hit? If you single step through the function does it execute without errors? What happens if you execute the SQL that you've created in Query Analyzer?