Are the posted questions getting worse?
-
Ok - this one is a stretch. Anybody have sp_who2 from sql 6.5?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
sp_who2 didn't exist in SQL 6.5
😉
It was sp_who. When they went to 7 or 2000, they kept sp_who and added sp_who2
-
CirquedeSQLeil (4/16/2010)
Ok - this one is a stretch. Anybody have sp_who2 from sql 6.5?Heck Jason even this old timer does NOT save stuff that old.... Yikes
-
Steve Jones - Editor (4/16/2010)
sp_who2 didn't exist in SQL 6.5😉
It was sp_who. When they went to 7 or 2000, they kept sp_who and added sp_who2
It was undocumented in 6.5 - found the references for it. It remained undocumented in 7 but was more widely known.
I am trying to install 6.5 to confirm - but it doesn't work on server 2003.:crazy:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
bitbucket-25253 (4/16/2010)
CirquedeSQLeil (4/16/2010)
Ok - this one is a stretch. Anybody have sp_who2 from sql 6.5?Heck Jason even this old timer does NOT save stuff that old.... Yikes
Haha - funny because I recently tossed all my MSDN stuff that old. I logged onto MSDN and found 6.5 there for download. Microsoft keeps everything around.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
I think you'll need NT 4 for this. It installed in W2K, but there were warnings, might be issues.
Where's the reference for it? I don't think it existed.
-
Steve Jones - Editor (4/16/2010)
I think you'll need NT 4 for this. It installed in W2K, but there were warnings, might be issues.Where's the reference for it? I don't think it existed.
I can't find anything official from Microsoft, but there is an article by Brad McGehee and then another site less reputable (in just that I don't know the person).
http://www.sql-server-performance.com/tips/gen_tips_p1.aspx
http://www.mssqlcity.com/Articles/SQL65/SQL65UndocSP.htm
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
CirquedeSQLeil (4/16/2010)
Wayne - I like your new avatar.Thanks. I was thinking about using this one (about 1/2 way down the page), but I figured that Steve would ask me to change it! :w00t:
Actually found it from someone else using it...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Chris Morris-439714 (4/16/2010)
jcrawf02 (4/16/2010)
Having a good day today. Working through a problem comparing two datasets and finding those that don't match from either. Thought about it, mocked up some data and tried it, then was going to post my solution and ask if it made sense, but found a thread where Jack suggested the very thing I came up with!must be larnin' sumthin! 😀
Can you imagine how tough your life would be if you didn't know Jack?
For those that don't know Jack[/url] (Caution: mild profanity)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Aha, got it to work (Server 2000).
Here is the SQL 6.5 version
if exists (select * from sysobjects where id = object_id('dbo.sp_who2') and sysstat & 0xf = 4)
drop procedure dbo.sp_who2
GO
CREATE PROCEDURE sp_who2 --1995/11/03 10:16
@loginame varchar(30) = NULL
as
set nocount on
declare
@retcode int
,@max_suid_spid int
,@int1 int
declare
@suidlow int
,@suidhigh int
,@spidlow int
,@spidhigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsuidlow varchar(11)
,@charsuidhigh varchar(11)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
--------
select
@retcode = 0 -- 0=good ,1=bad.
,@max_suid_spid = 32767
--------defaults
select
@suidlow = 0
,@suidhigh = @max_suid_spid
,@spidlow = 0
,@spidhigh = @max_suid_spid
--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
--------
select @int1 = suser_id(@loginame)
IF (@int1 IS NOT NULL) --Parm is a recognized login name.
begin
select @suidlow = suser_id(@loginame)
,@suidhigh = suser_id(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
--------
RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
SELECT
spid
,kpid
,status
,suid
,hostname
,program_name
,hostprocess
,cmd
,cpu
,physical_io
,memusage
,blocked
,waittype
,dbid
,uid
,gid
,login_time
,last_batch
,nt_domain
,nt_username
,net_address
,net_library
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
INTO #tb1_sysprocesses
from master..sysprocesses (nolock)
--------Screen out any rows?
IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)
and blocked = 0
--------Prepare to dynamically optimize column widths.
Select
@charsuidlow = convert(varchar,@suidlow)
,@charsuidhigh = convert(varchar,@suidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength( convert(varchar,suser_name(suid)))) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( convert(varchar,db_name(dbid)))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( convert(varchar,cpu))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( convert(varchar,physical_io))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( convert(varchar,cmd))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( convert(varchar,hostname))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( convert(varchar,program_name))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( convert(varchar,last_batch_char))) ,9)
)
from
#tb1_sysprocesses
where
suid >= @suidlow
and suid <= @suidhigh
and
spid >= @spidlow
and spid <= @spidhigh
--------Output the report.
EXECUTE(
'
SET nocount off
SELECT
SPID = convert(char(5),spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(suser_name(suid),1,' + @charMaxLenLoginName + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,DBName = substring(db_name(dbid),1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
where
suid >= ' + @charsuidlow + '
and suid <= ' + @charsuidhigh + '
and
spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '
-- (Seems always auto sorted.) order by spid_sort
SET nocount on
'
)
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
return @retcode
GO
GRANT EXECUTE ON dbo.sp_who2 TO public
GO
And a pic to bring back some memories.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
WayneS (4/16/2010)
CirquedeSQLeil (4/16/2010)
Wayne - I like your new avatar.Thanks. I was thinking about using this one (about 1/2 way down the page), but I figured that Steve would ask me to change it! :w00t:
Actually found it from someone else using it...
That other one is good too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Aha, got it to work (Server 2000).
Pray tell Jason, what is this seemling unquenchable thirst for SP_Who ....
whatcha going to do with it?
-
bitbucket-25253 (4/16/2010)
Aha, got it to work (Server 2000).
Pray tell Jason, what is this seemling unquenchable thirst for SP_Who ....
whatcha going to do with it?
Well, I am trying to document a history of it. I started getting curious when noticing that sql 2008 sp_who2 has a date in the file of
CREATE PROCEDURE sp_who2 --1995/11/03 10:16
That date has remained unchanged in all versions of who2, all the way back to 6.5. I thought it was a new proc in 2000 (skipped 7 and went to 2000 and never explored for a who2 proc). Then seeing that date, I was thinking there was no way it was that old - but it is.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Heh.... famous last words would be... [font="Arial Black"]AHHHHH CRAP![/font]
Just so you guys know... remember I said that I changed my cteTally code to use Gianluca's wonderful idea of using UNPIVOT instead of 10 SELECT 1 UNION ALL's??? Well, it turns out that it's quite machine dependent. I used it to do a million row split on a box at work and it came in at 4:18. Just to see how much faster it was, I also ran the old 10 SELECT code... the old code came in at 3:48!!!!!! The old code came in 30 seconds faster. I ran the test multiple times in disbelief.
I'm looking for the couple of posts I used the UNPIVOT code so I can post a correction. If you happen to run across the UNPIVOT code in your travels, send me the URL (please) so I can make a correction. Thanks, folks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ouch. I think that is the sort of thing that I was seeing. Machine dependency with all of those solutions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 14,431 through 14,445 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply