How to revert the commited query in MS Sql Server?

Accedentally i have updated the specific field by update query of the table and to preserve the previous data .....so please help me in this regard

How to revert the commited query in MS Sql Server?

ThanX in advance!!!!!!!!

If you've commited the transaction then your only real option is to restore your database from backup. Depending on the recovery model of the database and your backup strategy it may be possible to recover to the point just before you made the update. However, changes after this point will be lost.

Of course, if the data is fairly static, its possible you could restore your database as a new database and then manually correct the action by updating the data from the restore database.

Check out overview of Restore and Backup recovery in Books Online.

HTH!

|||

Thanx. Richbrownesq

i tried with point in time recovery with the RESTORE command but it show the following error..

Msg 4338, Level 16, State 1, Line 2

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

pleas help me in this regard.

|||

The error looks like its saying that your STOPAT time is incorrect. Make sure that you are applying your transaction logs in the correct order and that the date/time specified is accurate.

If you're still having issues, please post an example of the syntax you are using to restore your database and logs.

How to revert the commited query in MS Sql Server?

Accedentally i have updated the specific field by update query of the table and to preserve the previous data .....so please help me in this regard

How to revert the commited query in MS Sql Server?

ThanX in advance!!!!!!!!

If you've commited the transaction then your only real option is to restore your database from backup. Depending on the recovery model of the database and your backup strategy it may be possible to recover to the point just before you made the update. However, changes after this point will be lost.

Of course, if the data is fairly static, its possible you could restore your database as a new database and then manually correct the action by updating the data from the restore database.

Check out overview of Restore and Backup recovery in Books Online.

HTH!

|||

Thanx. Richbrownesq

i tried with point in time recovery with the RESTORE command but it show the following error..

Msg 4338, Level 16, State 1, Line 2

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

pleas help me in this regard.

|||

The error looks like its saying that your STOPAT time is incorrect. Make sure that you are applying your transaction logs in the correct order and that the date/time specified is accurate.

If you're still having issues, please post an example of the syntax you are using to restore your database and logs.

How to return specific Row in sql server

Hi,
Suppose I have 100 rows in my table, I want to retrieve say row no 5(I just want any thing on row no 5), I don't want to use identity column. Is there any other way by which I can retrieve specified row.
Thanks,
Sajidin relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/|||Originally posted by r937
in relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/

Thanks for answering my question.

Say suppose I have Name column and i want the 5th record on name column.

Thanks
Sajid|||Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW|||Originally posted by GWilliy
Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW

Yes I need the same.|||select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy|||Originally posted by r937
select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy

Thanks, It works.

If suppose i want to retrieve all columns, then do I need to specific each column.|||U could actualy do a sraight select into a cursor & then go straight to the Row

DECLARE @.a VarChar(20),@.b,@.c

DECLARE myCursor CURSOR SCROLL
FOR
SELECT a,b,c FROM myTable
OPEN myCursor
FETCH ABSOLUTE 5 INTO @.a,@.b,@.c
BEGIN
PRINT @.a
PRINT @.b
PRINT @.c
END
CLOSE myCursor
DEALLOCATE myCursor

example Code to give U an Idea - There are many options available when declaring Cursors etc.

May be more suitable for whatever Sajidrep is Doing

GW

How to return current hour of week?

One week have 168 hours.
How do you create SQl statement that return hour value for specific timestamp??
eg.
If week starts on system from Monday.
Monday 01:00 is hour 1
Tuesday 01:00 is hour 25
Sunday 23:00 is hour 167
etc.
Ideas??This should be a good start:

DECLARE @.startDate SMALLDATETIME
DECLARE @.endDate SMALLDATETIME

--Set Test value for Thursday Feb 22nd
SET @.endDate = ('02/22/2004 15:21:20')
--Set the start date to first day of the week
SET @.startDate = DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)
--Remove the time component of the start date
SET @.startDate = CONVERT(VARCHAR(10), @.startDate, 101)
--Count the number of hours from start of week (Mon) to @.endDate
SELECT DATEDIFF(Hour, @.startDate, @.endDate)

This could be converted to run as part of a select where @.endDate is provided by a column with Date data. If you use this verbatim you will want to test boundary conditions to assure they meet your requirements and I'm sure it could be optimized.

The key is in the setting of @.startDate [DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)].
Evaluation:
DATEPART(WeekDay, @.EndDate) = Day Code for Thursday (4)
(4) - 1 = 3*
-(3) = -3
DATEADD(Day, (-3), @.endDate) = Sets Date To Monday (*determined by the 1 subtracted from the original day code)|||One more try...

(24 * DatePart("dw",'2004-03-8 1:00:00')-1) + {fn Hour('2004-03-8 1:00:00')}

Is it correct?|||You need another set of parenthesis for proper order of operations:

declare @.TimeStamp datetime
set @.TimeStamp = getdate()
select (24 * (DatePart(dw,@.TimeStamp)-1)) + {fn Hour(@.TimeStamp)}|||set datefirst 1
declare @.timestamp datetime
set @.timestamp = getdate()
select (datepart(dd,@.timestamp)-1)*24+datepart(hh,@.timestamp)|||Just an observation that may not affect you, but there aren't always 168 hours in the week. If you observe Daylight Savings Time (http://webexhibits.org/daylightsaving/) then one week has 167 and one has 169 each year. If you need to keep Sidereal (http://tycho.usno.navy.mil/sidereal.html) time, then a different week each year can have a smidgeon more or less than 168.

These don't affect everyone, but they are the basis behind my always using date functions instead of "roll your own" functions like these. I'm not exactly sure how I'd handle this case, but I just wanted to raise the issue before folks go merrily trooping off with a solution that might not always work for them.

-PatP|||declare @.dt datetime
set @.dt = '20040309 1:00'
select DATEDIFF(hh,DATEADD(d,-(DATEPART(w,@.dt)+@.@.DATEFIRST-2)%7,LEFT(@.dt,13)),@.dt)

Hans.|||Just like skinning a cat, there is more than one way to do this and whatever you choose, the result is not very attractive.

how to retrieve data from sql then put it into a label or textbox

i wanna know is there a way to retrieve data from the sql database and then
instead of putting it in the datagrid, can i put a specific field of data to a textbox?

i mean just a data (for example, a username where the password match the username) in a textbox.

thanks

sherlynyes you can use a datareader/dataadapter etc to fill a dataset and then into a textbox or pretty much any control you want..

hth