March 5, 2008 at 12:39 pm
SQL:
select @@VERSION
SELECT SERVERPROPERTY('ProductLevel')
select 'something' as s, 1 as n into #s
union select 'somethingelse', 2
select distinct n into #ss from #s
delete from #s where s=(select top 1 s from #s where n=(select top 1 n from #ss) )
drop table #s drop table #ss
RESULTS:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
(1 row(s) affected)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SP4
(1 row(s) affected)
(2 row(s) affected)
(2 row(s) affected)
Server: Msg 21, Level 20, State 1, Line 6
Warning: Fatal error 625 occurred at Mar 5 2008 2:00PM
Connection Broken
...any thoughts? I can do this with any of the databases on this server and on several other servers. Is this just a bug that puts me SOL?
Thanks in advance!
March 5, 2008 at 2:31 pm
Looks like a severity 20 error, so there should be more information in the SQL error log and/or the windows event log.
Check them, see if there's anything useful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2008 at 6:21 pm
I tried it again on a slightly different server on which I have access to the Windows Event Log, and I got the following:
[font="Courier New"]Cannot retrieve row from page (1:1008) by RID because the slotid (0) is not valid.[/font]
I tried it a few times on that system; the page was always page (1: ). The slotID was always 0. I don't know how to get to the SQL errorlog. Since I can reproduce this on several servers, I wonder if anyone else out there can reproduce it? Maybe it is a SQL Server bug rather than a data corruption issue.
@@VERSION, ServerProperty('ProductLevel'):
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
(1 row(s) affected)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SP4
(1 row(s) affected)
March 6, 2008 at 8:19 am
Matt,
It does! Thank you so much!
-Nate
March 6, 2008 at 9:37 am
Nathan,
Check this fix on MSDN - it should solve your problem.
http://support.microsoft.com/kb/810026
Kind Regards,
Matt
March 6, 2008 at 10:27 am
In case anyone is interested, this is what I was trying to do:
Turn table a:
[font="Courier New"]subordinate boss
-------------------- --------------------
Dodsworth Buchanan
King Buchanan
Suyama Buchanan
Buchanan Fuller
Callahan Fuller
Davolio Fuller
Leverling Fuller
Peacock Fuller
[/font]
into table b:
[font="Courier New"]Subordinates Boss
-------------------------------------------- --------------------
Dodsworth,King,Suyama Buchanan
Buchanan,Callahan,Davolio,Leverling,Peacock Fuller[/font]
And I did it this way:
[font="Courier New"]---------------------------------------------------------
use Northwind
select e1.lastName as subordinate,
e2.lastName as boss
into #s
from employees e1 right outer join employees e2
on e2.employeeID=e1.reportsTo
where e1.lastName is not null
order by boss, subordinate
---------------------------------------------------------
select distinct boss into #ss from #s
---------------------------------------------------------
select cast('' as varchar(1000)) as subordinates, boss into #r2 from #ss -- subordinates should be buffer big enough to hold all subordinates
---------------------------------------------------------
while(select count(boss) from #ss) > 0
begin
--
update #r2
set subordinates=( select subordinates
from #r2
where boss=(select top 1 boss from #ss)
)+','+(
select top 1 subordinate
from #s
where boss=(select top 1 boss from #ss)
)
where boss=(select top 1 boss from #ss)
--
delete from #s
where subordinate=( select top 1 subordinate
from #s
where boss=(select top 1 boss from #ss)
) OPTION(HASH JOIN)
--
delete from #ss where boss not in (select distinct boss from #s)
end
---------------------------------------------------------
select substring(subordinates, 2, 1000) as Subordinates, boss as Boss from #r2
---------------------------------------------------------
drop table #s
drop table #ss
drop table #r2
---------------------------------------------------------
[/font]
March 6, 2008 at 12:53 pm
Okay, cool.
I am glad it worked.
Matt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply