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...
> 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...
> 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/inf...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
>
>

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

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...
> 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...
> 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 da
tabase with same file
structure as you want to have for the one you want to attach, stop SQL serve
r, copy the files you
want to attach over file files for the db you just created and start SQL ser
ver. Now, the db is
corrupt to some extent, the question is whether the database will be availab
le 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/in..._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
>
>

Part of string

Need help..
I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?
Thanxtry this

select substring('last,first',1,charindex('first,last'))

i hav'nt checked it.. I think u may have to alter it a little|||Assuming there is always a comma

declare @.name char(30)
set @.name = 'GATES, BILL'
select left(@.name,charindex(',',@.name)-1)

returns

GATES|||Isn't this your lucky day? I just adapted and updated this function yesterday off of some old Access Basic code I had.

Call it like this: "Select dbo.FormatName([YourNameString], 'L')" and it should give you what you want. It handles names in multiple formats. If you find a name string it won't handle, let me know so I can update it.

create function FormatName(@.NameString varchar(100), @.NameFormat varchar(20))
returns varchar(100) as
begin
--blindman, 11/04
--FormatName decodes a NameString into its component parts and returns it in a requested format.
--@.NameString is the raw value to be parsed.
--@.NameFormat is a string that defines the output format. Each letter in the string represents
--a component of the name in the order that it is to be returned.
-- [H] = Full honorific
-- [h] = Abbreviated honorific
-- [F] = First name
-- [f] = First initial
-- [M] = Middle name
-- [m] = Middle initial
-- [L] = Last name
-- [l] = Last initial
-- [S] = Full suffix
-- [s] = Abbreviated suffix
-- [.] = Period
-- [,] = Comma
-- [ ] = Space

--Test variables
-- declare @.NameString varchar(50)
-- declare @.NameFormat varchar(20)
-- set @.NameFormat = 'L, h. F m. s.'
-- set @.NameString = 'Father Gregory Robert Von Finzer Jr'

Declare @.Honorific varchar(20)
Declare @.FirstName varchar(20)
Declare @.MiddleName varchar(30)
Declare @.LastName varchar(30)
Declare @.Suffix varchar(20)
Declare @.TempString varchar(100)
Declare @.IgnorePeriod char(1)

--Prepare the string
--Make sure each period is followed by a space character.
set @.NameString = rtrim(ltrim(replace(@.NameString, '.', '. ')))
--Eliminate double-spaces.
while charindex(' ', @.NameString) > 0 set @.NameString = replace(@.NameString, ' ', ' ')
--Eliminate periods
while charindex('.', @.NameString) > 0 set @.NameString = replace(@.NameString, '.', '')

--If the lastname is listed first, strip it off.
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString)))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString, len(@.TempString)+2)))
if right(@.TempString, 1) = ',' set @.LastName = left(@.TempString, len(@.TempString)-1)
if len(@.LastName) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))

--Get rid of any remaining commas
while charindex(',', @.NameString) > 0 set @.NameString = replace(@.NameString, ',', '')

--Get Honorific and strip it out of the string
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
if @.TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'MAJ', 'Major', 'PVT', 'Private', 'FATHER', 'SISTER') set @.Honorific = @.TempString
if len(@.Honorific) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))

--Get Suffix and strip it out of the string
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @.Suffix = @.TempString
if len(@.Suffix) > 0 set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))

if @.LastName is null
begin
--Get LastName and strip it out of the string
set @.LastName = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.LastName)))
--Check to see if the last name has two parts
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
begin
set @.LastName = @.TempString + ' ' + @.LastName
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))
end
end

--Get FirstName and strip it out of the string
set @.FirstName = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.FirstName)))

--Anything remaining is MiddleName
set @.MiddleName = @.NameString

--Create the output string
set @.TempString = ''
while len(@.NameFormat) > 0
begin
if @.IgnorePeriod = 'F' or left(@.NameFormat, 1) <> '.'
begin
set @.IgnorePeriod = 'F'
set @.TempString = @.TempString +
case ascii(left(@.NameFormat, 1))
when '72' then case @.Honorific
when 'Dr' then 'Doctor'
when 'Rev' then 'Reverend'
when 'Hon' then 'Honorable'
when 'Maj' then 'Major'
when 'Pvt' then 'Private'
else isnull(@.Honorific, '')
end
when '70' then isnull(@.FirstName, '')
when '77' then isnull(@.MiddleName, '')
when '76' then isnull(@.LastName, '')
when '83' then case @.Suffix
when 'Jr' then 'Junior'
when 'Sr' then 'Senior'
when 'Esq' then 'Esquire'
else isnull(@.Suffix, '')
end
when '104' then case @.Honorific
when 'Doctor' then 'Dr'
when 'Reverend' then 'Rev'
when 'Honorable' then 'Hon'
when 'Major' then 'Maj'
when 'Private' then 'Pvt'
else isnull(@.Honorific, '')
end
when '102' then isnull(left(@.FirstName, 1), '')
when '109' then isnull(left(@.MiddleName, 1), '')
when '108' then isnull(left(@.LastName, 1), '')
when '115' then case @.Suffix
when 'Junior' then 'Jr'
when 'Senior' then 'Sr'
when 'Esquire' then 'Esq'
else isnull(@.Suffix, '')
end
when '46' then case right(@.TempString, 1)
when ' ' then ''
else '.'
end
when '44' then case right(@.TempString, 1)
when ' ' then ''
else ','
end
when '32' then case right(@.TempString, 1)
when ' ' then ''
else ' '
end
else ''
end
if ((ascii(left(@.NameFormat, 1)) = 72 and @.Honorific in ('FATHER', 'SISTER'))
or (ascii(left(@.NameFormat, 1)) = 115 and @.Suffix in ('II', 'III')))
set @.IgnorePeriod = 'T'
end
set @.NameFormat = right(@.NameFormat, len(@.NameFormat) - 1)
end

-- select ltrim(rtrim(@.TempString))

Return @.TempString
end