I have update a table field and I like to know how do you "undo" the update? I set the field_length from 6 to 7 and like to undo this.Here is my command:
table = template_fileds
column= field_length.
filed name= field_name.
update template_fields set field_length='7' where field_name='assesscode9'
Hi
Can somebody advice me on this:
I am trying to update a row, which is being pulled from another table.
i.e., I have two tables T1 and T2
I am getting a row from T2 and inserting it in T1
then I am updating that row in T1
What i want is if in case these is an error while updating this row I want to remove the row from T1
T1 and T2 are same.
declare @err1 int
--BEGIN TRANSACTION
INSERT INTO dbo.T1 SELECT * FROM dbo.T2 WHERE UpdateFlag = 'Y' AND ID = '12345'
BEGIN TRY
UPDATE dbo.T1 set ReportDate= '9910-12-2006'
SET @Err1 = @@error
END TRY
BEGIN CATCH
insert into errorlog values (error_message())
END CATCH
--if @err1<>0
--begin
--Rollback transaction
--end
This work fine but I am not able to roll back the transaction
When I activate the codes fro roll back, it not performing the Logging operation in CATCH also terminating
REQUIREMENT
1) Get the record in T1 from T2
2) Update the record in T1
3) IF step:3 good commit it, else remove the only the row which was pulled from T2. It must do this also log in dbo.ErrorLog the errored system message error_message()
Thanks a lot in advance
I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table
if the update is successful or not. What i'm looking for is:
1) Is there a way to identify what records were updated.
2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.
I'm on sql server 2008.
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
Hi all,
I have a problem about a query to update a table
UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288}
where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View 1 Replies View RelatedIn Access, you have a combo with column designations for example me.combofield.column(x) and you can update another field with those column(x) values.
How do you do it in MS SQL?
I didn't mean "from a 'combo' field'" in SQL(!) I just want to reproduce the equivalent of an Access combo box.
Hi. I'm not such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that?
What I mean is something like:
---------------------------------------------------
Update database.dbo.table set
if(@newvalue !=null)
afield = @newvalue;
-------------------------------------------------
Really need your help on this. Thanks.
Hello all,
I know that Oracle uses undo table spaces or rollback segments as its undo mechanism. what does Microsoft SQL Server use as its undo mechanism?
Krz
I'm confused how the undo works when you restore WITH STANDBY
Suppose I restore WITH STANDBY
FULL
DIFF
LOG A
LOG B
LOG C
How do I undo LOG C? Do I restore LOG B again?
Hello !
I have a big problem. I need to go back un changes that had been since the a specific date (beginning of november in my case).
Thanks a lot for any help on how to do this !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
Is there anyway in SQL Server to rollback an SQL statement which wasalready executed. I know there is a transaction log but what itcontains and how it works is still a mystery to me.Assuming I delete all records from a table - can I somehow undo this?
View 5 Replies View Relatedif we kill sp, let's say, on the third statement, will the first two
be rolled back (undo) ?
How come Undo button does not work? Thanks.
View 2 Replies View RelatedMuhammad Qasim writes "i hav query that i was just taking the backup of the database n by mistake i just clicked restore as the wizards are similar so i just restored the database to 10days old backup n now i have no latest backup so how can i just undo this restore if u hav any idea of how to overcome this plz reply as early possible lookin for replies"
View 4 Replies View Relatedhai to every one,
i am pavan working as oracle dba. in oracle we have undo table space.
when we issue rollback oracle gets old image from undo tablespace.
in sql server where it gets from old image.
who performs instance crash recovery(which back ground is responsible for it)
in sql server we have logwriter, lazy writers(back ground processes)
what are the other processess.
and some doubts are
how insert query works
how update query works,
how select query works.
means i issue update statement. command successfully executed. late issue rollback, from where sql server gets old imege of the data
please send me any archetecture book /pdf/online link
thanks
kumar
pavan
Associate Oracle DBA
Hello,Somebody accidently deleted a record with cascade deletesand took out about 80 records.The database has been used heavily since then.How do I put back the data?I have spent hours researching BOL and all I can findis how to restore the database to a point in time.But that will lose all the work done after the mistake.(We do a full backup every week and log backup every day)Is there a standard way to do this?Morgan
View 2 Replies View RelatedHi ,
I was working in the SSIS designer and when I found that the DataFlow task I deleted was indeed something I needed, I tried to Undo the operation but Alas the option is not enabled :(
Is there a way we can undo the operations?
I often run queries in Query analyzer. Today I accidentaly deleted some data from a table. Is their a way to undo the last SQL command executed?I have heard of the rollback command, but I don't know if that is applicable or not?
View 6 Replies View RelatedI have full Recovery mode.
I just accidently deleted a handful of records out of a table about 10 minutes ago. My last backup was last night.
Can I go back 10 minutes ago for a specific table? If so how... DO I need to backup now, then use the trans-logs to recover up to a specific point?
Thanks!!
USE [master]
RESTORE DATABASE [EmployeeRecovered] FROM DISK = N'D:BackupsNormalEmployeeFULL.BAK' WITH FILE = 1,
MOVE N'Employee' TO N'D:MSSQLDATAEmployeeRecovered1.mdf',
MOVE N'Employee_log' TO N'D:MSSQLLOGEmployeeRecovered1_log.ldf',
NOUNLOAD, STATS = 5, NORECOVERY
[Code] ....
Now I realze the recovery point is somewhere in TranBackup2.trn and need to go back to the state where only TranBackup1.trn is applied. How can i do this ?
currently i m developing a web application with using sql server 2005
and i was testing yesterday a sql update query with sql server management studio.
in my update query i forgot to put where condition and now all the rows of table are updated. is there any solution to undo this and retrieve all rows back?
Regards Selena
Hi,
I am in a process of designing a web application (ASP.NET) which is mostly data driven. The application needs to provide an undo functionality to users for eg. Undo an Add, Update or Delete that has been already committed to the database.
We are using SQL Server 2005 at back end. What I need to know is how will I enable this feature at database level. I am sure we might have to play with triggers but that too might not help in achieving the UNDO functionality. If anyone can shed light on how to achieve this, it will be quite helpful. Thanks
1. A few months ago, I received a request to deny delete to a group (Windows AD login) against 3 databases. So I issued a DENY DELETE to the relevant Windows login.
2. Now, someone is having trouble updating data. The issue seems to be that a trigger fires during the update. In the trigger the code tries to delete from a table in the previously delete-denied databases. So the update fails.
-- In retrospect, perhaps I should have used REVOKE - not sure
-- Also, I can't seem to find a listing of the DENY permissions - or rather, how to list what permissions are denied. I guess maybe that is a hard thing to do if the result of a DENY is to leave certain permission columns NULL (vs adding 'DENY'). But it would be useful for me to see what the current permission state is.
-- Finally, I tried to undo the DENY without success. Here is my attempt.
a. I tried GRANT DELETE... - no luck
b. Then I saw an article explaining to use REVOKE, so I tried 'REVOKE DELETE' followed by 'GRANT DELETE' - still no luck.
Hello,
I have a field in a table that I have to update to the number of the week.
Information i use is from another field, which is text in the format like :
01022005
02142005
07082005
11222005
...
12022005
mmddyyyy.
How to write an update to update empty field with a number of the week based on the value from another field.
Thank you very much.
Trying to run this SQL script
update Promotions
set PromotionDiscountData = '<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-11T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-12T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule">
[code]...
but getting this error
Msg 402, Level 16, State 1, Line 1
The data types xml and varchar are incompatible in the equal to operator.Basically within each cell I am trying to change the StartDate only
if a db field "mynum" of type int contains for example the number 543.Is there a way I can update this field without first selecting the value?so for example set the the current value to +1
in sql server I want to update the "UpdatedDate" field automatically when I change ANY other field in that specific record.it has to be set to the current server time.
View 2 Replies View RelatedHi,
I am trying to update a field in a temptable with the count of items in another table. To illustrate, CustomerID=23 and I want the number of occurences in the temp table. Here's the code which DOESN'T work:
INSERT INTO TempTable
(
CustomerID,
FirstName,
LastName,
DateAdded,
AlbumPicture,
LayoutCount
)
SELECT
Albums.CustomerID,
Customers.FirstName,
Customers.LastName,
DateAdded,
AlbumPicture,
COUNT(*) FROM Layouts WHERE Layout.customerID = Albums.CustomerID
FROM
Albums JOIN
Customers on (Albums.CustomerID=Customers.CustomerID)
Please take a look at the COUNT line. Here I want to count the occurences of a specific customerid in another table and put in into th LayoutCount field.
SQL server reports "Incorrect syntax near the keyword 'FROM'". Any ideas how to achieve this?
Many thanks!!
Eric
Is it possible to update the filed used in the inner join
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.name
Hello,
I am updating three fields (defined as decimal(18,4) NULL) in
a 1.7 million record table to 0.
This table is also very wide (meaning lot of columns). A simple
query like this -
Update Table1
Set [Decimal Field1] = 0,
[Decimal Field2] = 0,
[Decimal Field3] = 0
is taking forever (1+ hours) to run.
Either I may be doing something stupid here or I am missing
something vital.
Thanks for any help.
Hi,
I'm trying to use the Update command to change a field value. I only know the field name at run-time, how do you write this command. This is what I have done which just sets the variable @cFieldName to the value and not the field within the table.
UPDATE [Atable] SET @cFieldName = @aValue WHERE ([Id] = @Id_1)
Thanks