June 23, 2008 at 7:22 am
Here's a fun one that's perplexed me.
I have table, dump_results, which contains osql outputs from other sql servers running sp_readerrorlog outputs I've massaged. Ideally, each row is a dump of text starting with datetime, source, then message, all as one text field. Eventually I'll parse the three parts out. However, because of sp_readerror/osql quirks, I get rows back that do NOT start with a date, rather they are a continuation of the log message before. I can skip those rows but for a more accurate read, I'd rather keep them. What I'd like to do is combine the continued row (sometimes rows) into one row before sending to a final table, including the top four rows containing Microsoft SQL Server headers.
create table dump_results (rowid int identity(1,1), val varchar(1000))
...insert and massage data from osql into table...
results:
rowid val
-----------------------------------------
1 2008-06-20 09:14:38.17 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
2 Dec 17 2002 14:22:05
3 Copyright (c) 1988-2003 Microsoft Corporation
4 Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
5 2008-06-20 09:14:38.54 server Copyright (C) 1988-2002 Microsoft Corporation.
6 2008-06-20 09:14:38.54 server All rights reserved.
7 2008-06-20 09:14:38.54 server Server Process ID is 1064.
...
20 2008-06-06 15:36:35.96 backup Database backed up: Database: AllDatabases, creation date(time):
21 {'VDI_F03A71BC-D7BB-4D2D-BFD2-4B198CA6C578_0', 'VDI_F03A71BC-D7BB-4D2D-BFD2-4B198CA6C578_1'}
How do I combine rows 1-4 as one row as well as 20 to 21? Right now, I have a main loop cycling through all the rows, then generate a second inner loop but for some reason it takes a really long time, even with a very short table.
Thank you.
Gaby Abed
June 23, 2008 at 4:20 pm
--insert the results into an import table.
insert into import_dump_results (id, val)
....
-- insert valid date results into final
insert into dump_result (id, val)
select id, val
from import_dump_results
where isdate(left(val,22)) = 1
-- update val for long rows
declare @increment int
set @increment = 1
while @increment < 25
BEGIN
update dump_results
SET val = val +
FROM import_dump_results
where isdate(left(import_dump_results.val,22)) = 0
AND import_dump_results.id = dump_results.id + @increment
if @@rowcount > 0
set @increment = @incrment + 1
else
set @increment = 25
END
-- note: It may be more efficient to do a leftjoin (id is null) for the import_dump_results.id
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply