|
What's wrong with this?
Begin Tran
delete from Rough1
Rollback
Pits fall into Chuck Norris.
|
|
|
|
|
Nothing wrong with it.
Actually it is said tht Delete can be rolled back but truncate cannot be.
//This T-SQL will be rolled back for Delete command
Begin Tran
delete from Rough1
Rollback
//This T-SQL will also be rolled back for Truncate command
Begin Tran
Truncate Rough1
Rollback
So where is the difference between Delete and Truncate command in respect of Rollback
|
|
|
|
|
Within an explicit transaction either can be rolled back. You cannot roll back from the transaction logs for a truncate (outside an explict transaction) as the changes are not inserted into the transaction logs. A delete logs each record deleted to the transaction log so it can be recovered. That is the difference.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Sir,
Can u give me an example.........
Begin Transaction Logs
Truncate Table Rough1
Rollback
Is this T-SQL given abouve ok otherwise plz let me know so tht Truncate cannot be rolled back
|
|
|
|
|
Just do not put the transaction around the truncate
Truncate Table Rough1
Bob
Ashfield Consultants Ltd
|
|
|
|
|
ok Thnx but still i m confused if i dont put the transaction around the truncate then it wont be Rolledback as i want
But if i also dont put the transaction around the delte then it wont be rolled back too.
So how can we say that a Delete can be rolledback but a truncate cannot be......
|
|
|
|
|
.NET- India wrote: So how can we say that a Delete can be rolled back but a truncate cannot be......
You seem to be missing the point, or at least not understanding what others have said.
Both delete and truncate can be rolled back within an explicit transaction, since the physical change does not happen until the transaction (explicit or auto commit) completes. In the case of a committed transaction (explicit or auto commit) only delete can be recovered from the transaction log by restoring the log with backup. It is incorrect to say that delete can be rolled back, but truncated cannot, the difference is not one of ability to rollback, but of recoverability from the transaction log after commit.
|
|
|
|
|
Thanks for trying to explain it better, but I think we are onto a loser here 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Dear Friends,
i m working on an VC++ application and using oracle as a backend. Through my application i m writing some data to Oracle database. After certain number of iterations i m getting Commit error (8197). Can anybody help me with this? What might be the reason for the same.
|
|
|
|
|
It could be deadlocks (row locks by another app).
Try figuring which data it fails on.
|
|
|
|
|
Hi Arsene,
Actually using one application i m putting data in a table and another application is accessing that data to display on the screen.
Can this lead to deadlock situation??/
When i m reading tht data from another appplication i m trying to do some operation as Distinct, SUM and Count...
Can this lead to deadlock situation??/
It will be very helpful if u can provide with some information...
Thanks in Advance
|
|
|
|
|
How would I go about enumerating constraints on a table, logging them, and then dropping them with a view to recreating them again later? I assume something in the Microsoft.SqlServer.Management.Smo namespace would be of use, but a perfunctory Google wasn't too helpful, and I have a 14h00 deadline, and it's now 11h20.
Pits fall into Chuck Norris.
|
|
|
|
|
I don't know about constraints but we have done the following with indexes.
In Enterprise Manage create an index, BEFORE saving the change you can get SQL to script the proposed changes, save to a file or clip. You can then work over the code to meet your requirements. Just like excel macros 
And yeah I guess this is way to late for the deadline, but it is Sunday afternoon here now and....
[edit] script icon is next to the primary key icon [edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I appreciate if some one could help me with using InfoMessage Event of ADODB.Connection object in VB6 and SQL server.
If I pass a single SQL Print command to the ADO object, it will fire the Event and works fine, but a print command among other commands doesn't work. Am I missing something??
here is the code
Thanks
Arash
__________________________________________________________________________________________
Private WithEvents cn As ADODB.Connection
Private Sub cn_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
MsgBox pError.Description
End Sub
Private Sub Comm_UPD_Rec_Click()
SQLAdd = " DECLARE @Key int " & _
"SET @Key=(select (max(task_id)+1) from task) " & _
"insert into task (task_id,...)values (@Key ,...) " & _
" PRINT @Key"
Dim cmd As New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = SQLAdd
Set cmd.ActiveConnection = cn
out = cmd.Execute
End Sub
__________________________________________________________________________________________
|
|
|
|
|
Might I suggest you move from SQL strings to a stored procedure based structure, you are lamost there with your string.
A couple of things (I'm not up on VB6 or ADODB any more) so
I don't think Print will return anything @key.
You are using a sub and out is not declared (use a function and return the output of cmd.execute)
executing a sql string using your structure is just weird (and wrong for security reasons). most people just create a string like "Insert Table (fields)Values("+ varvalue + ")" and exc the string (This is still wrong but simpler)
Take your SQL and paste it into Query analyer/Enterprise manager and see if it runs, I dout it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The SQL is a part of a larger SQL statement that the client program generates and it works fine, I mean it does what it's supposed to do and with a lot of parameters and loops that i have it's much easier to create it on the client program rather than on the server.
I was expecting to catch the event when SQL executes PRINT or RAISERROR but it doesn't. If you pass a simple SQL command like
PRINT 5
The event fires and program shows the message box,
But when you have
Declare @key int
SET @key=5
PRINT @key
Nothing happens. Strange?
Thanks anyway
Arash
|
|
|
|
|
I found the Answer
the SQL command shall be enclose in a Begin...End and SET NOCOUNT ON shall be used.
so the SQL would be like
SQLAdd = " Begin SET NOCOUNT ON; DECLARE @Key int " & _
"SET @Key=(select (max(task_id)+1) from task) " & _
"insert into task (task_id,...)values (@Key ,...) " & _
" PRINT @Key; End"
I also tryed placing Select @Key at the end of the SQL command, but without Begin...End and SET NOCOUNT ON
rs.open SQLAdd will not actually open the recordset and any attempts to read the rs will result in Error Object Closed. But if you place Select @Key after the End, rs will contain the @key.
Arash
|
|
|
|
|
Anyone care to comment on the differences between these? A preliminary Google yields very little.
|
|
|
|
|
I beleive the views are now the "preferred" method of getting at the schema, M$ is big brothering you again,  typical dumbing down of the database. With the schemas you do not have to really understand the underlying tables and structures - newbies also never learn that there is an underlying environment available to them.
I seem to recall they made noises about hiding the underlying tables but nothing ever came of it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Both of them are views, so besides decrying what is elsewhere considered good practice, because the bad M$ does it, you haven't really contributed to this thread.
Pits fall into Chuck Norris.
|
|
|
|
|
Interesting, I always thought the sys.objects, sys.columns etc were tables.
I think having to rat through the sys objects (I had trouble writing that) is one of the best way of learning how the SQL is put together. When everything is layed out on a platter(view) life gets too simple. This is fine for users but a developer should have to get down and dirty in SQL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You're thinking of sysobjects and syscolumns, no full stop. I seriously doubt this is a dumbing down exercise, as navigating sys.columns is not much easier than navigating syscolumns, but MS advise uysing the abstraction layer to preserve compatibility if they move away from or change the underlying syscolumns table. I take their advice as a hint that they are planning on changing things, and when I write robust code that must access schema information, I will choose the more reliable views over the less reliable tables.
Pits fall into Chuck Norris.
|
|
|
|
|
From Wikipedia:
"In relational databases, the Information Schema is an ANSI standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information which some databases make available through non-standard commands, such as the SHOW command of MySQL and the DESCRIBE command of Oracle."
sys.columns is a version that predates (actually comes from Sybase, from whom Microsoft bought SQL Server) the ANSI standard. So, all ANSI compliant relational databases will have the INFORMATION_SCHEMA viesw as defined in the standard, but only SQL Server (and possibly still Sybase on Unix) will have sys.columns.
|
|
|
|
|
Enlightenment at last! Thanks Rob. ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
Pits fall into Chuck Norris.
|
|
|
|
|
hi
Friends
I am working on a asp.net application
in one of the aspx page i am having a combobx
in which i want to display a concatenated string ..
that means i want to concatenate four fields from the database and display it in the combo box
Can i get those values in a datareader so that i can assign it..
Please suggest how to solve this problem
Example
combobox value is (a1,a2,a3,a4),(a5,a6,a7,a8),(a9.a10.a11.a12) where all the fields(a1,a2,a3,a4.......) are coming from the database
If You win You need not Explain............
But If You Loose You Should not be there to Explain......
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
