partial transaction is not working

use northwind
BEGIN TRANSACTION namechange
UPDATE dbo.Employees
SET Title = 'team leader' where FirstName = 'Bishoy'
UPDATE dbo.Employees
SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
SAVE TRANSACTION namesaved
UPDATE dbo.Employees
SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
SELECT *
FROM dbo.Employees WHERE FirstName = 'Bishoy'
ROLLBACK TRANSACTION namesaved
COMMIT TRANSACTION namechange"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
Nope, works perfectly.
Add another
SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
At the end. The BirthDate change is rolled back, but the Title change is
commited.
David|||Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
>> use northwind
>> BEGIN TRANSACTION namechange
>> UPDATE dbo.Employees
>> SET Title = 'team leader' where FirstName = 'Bishoy'
>> UPDATE dbo.Employees
>> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
>> SAVE TRANSACTION namesaved
>> UPDATE dbo.Employees
>> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
>> SELECT *
>> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>>
>> ROLLBACK TRANSACTION namesaved
>> COMMIT TRANSACTION namechange
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||Yes , you are right , thank you.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
>> use northwind
>> BEGIN TRANSACTION namechange
>> UPDATE dbo.Employees
>> SET Title = 'team leader' where FirstName = 'Bishoy'
>> UPDATE dbo.Employees
>> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
>> SAVE TRANSACTION namesaved
>> UPDATE dbo.Employees
>> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
>> SELECT *
>> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>>
>> ROLLBACK TRANSACTION namesaved
>> COMMIT TRANSACTION namechange
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||You could try save the files you want to attach on a safe place, create a database with same file
structure as you want to have for the one you want to attach, stop SQL server, copy the files you
want to attach over file files for the db you just created and start SQL server. Now, the db is
corrupt to some extent, the question is whether the database will be available with problems in it
or suspect when you start SQL server. MS Support is probably the best route in any case...
Some thoughts:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
>