partial transaction is not working  
Author Message
Vp3r





PostPosted: Sat Jun 25 11:11:24 CDT 2005 Top

SQL Server >> 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

SQL Server177  
 
 
David





PostPosted: Sat Jun 25 11:11:24 CDT 2005 Top

SQL Server >> 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
>

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


 
 
Andrew





PostPosted: Sat Jun 25 12:06:52 CDT 2005 Top

SQL Server >> partial transaction is not working Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.


--
Andrew J. Kelly SQL MVP




>


>> 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
>


 
 
Bishoy





PostPosted: Sat Jun 25 11:41:51 CDT 2005 Top

SQL Server >> partial transaction is not working Yes , you are right , thank you.




>


>> 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
>


 
 
Tibor





PostPosted: Sat Jun 25 13:17:08 CDT 2005 Top

SQL Server >> partial transaction is not working 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/




> 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
>
>
>
>