-- table of names with no spaces
declare @Names table ([Name] varchar(100));

-- we need a table of numbers
declare @Ints table (i int identity, [dummy] int);

-- here's some data
insert into @Names
select 'JeffSmith' union
select 'RobVolk' union
select 'BillyJoel' union
select 'MaryJohnson' union
select 'PetePeters'

-- populate our table of numbers with 1-25
insert into @ints(dummy)
select 0 from 
@Names N1
cross join @Names N2

-- this will give us the position of the 2nd capital letter
-- in each name:

select [Name], Min(i) as Position
FROM @names
cross join @ints
WHERE
i > 1 AND
ASCII(SUBSTRING([Name],i,1)) between 65 and 90
group by [Name]

-- So, our final query is:
SELECT Left(Name,Position-1) + ' ' + SUBSTRING(Name,Position, Len(Name)-Position + 1)
FROM
(
select [Name], Min(i) as Position
FROM @names
cross join @ints
WHERE
i > 1 AND
ASCII(SUBSTRING([Name],i,1)) between 65 and 90
group by [Name]
) A

Select Name, LEFT(Name, LEN(Name) - PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', REVERSE(Name))) 
+ ' ' + RIGHT(Name, PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', REVERSE(Name))) AS SplitName
FROM Names