Hi
When using sybase EA server I can utilise the following code to inform me of
what a particular Spid is executing
select s.stmtnum, s.linenum, o.name
from sysobjects o, master..sysprocesses s
where s.id = o.id
and s.spid = 77
is there anything I can use in sqlserver to give me the same kind of
details? ie, what line of a SP is running?
however, in sql server the columns linenum1) DBCC INPUTBUFFER(spid)
2) DECLARE @.Handle BINARY(30)
SELECT @.Handle = sql_handle
FROM master..sysprocesses
WHERE spid = @.@.spid
SELECT * FROM ::fn_get_sql(@.Handle)
For more details please refer to BOL
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
> Hi
> When using sybase EA server I can utilise the following code to inform me
> of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:
> Hi
> When using sybase EA server I can utilise the following code to inform me
of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||Thanks for that Uri, every day is a lesson eh!
However, is there any way to drill down to a more granular level so that I
can identify what line of the proc it is running?
The problem we are encountering is that on occasions a couple of large
procedures grind to a standstill and we are trying to identify where they ar
e
slow.
Any more assistance would be gratefully received.
Gavin
"Uri Dimant" wrote:
> 1) DBCC INPUTBUFFER(spid)
> 2) DECLARE @.Handle BINARY(30)
> SELECT @.Handle = sql_handle
> FROM master..sysprocesses
> WHERE spid = @.@.spid
> SELECT * FROM ::fn_get_sql(@.Handle)
> For more details please refer to BOL
>
> "almightygav" <almightygav@.discussions.microsoft.com> wrote in message
> news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
>
>|||I've got to hand it to you Vadivel, that was good!
I'd like to think I could have written it if the help files were updated
inline with the Service Packs but hey ho, top job!
When do the help files get updated to show changes made by the Service
Packs? The two columns added to sysprocesses don't seem to be documented
anywhere?
Thank you very much, perfect!
"Vadivel" wrote:
> You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> http://thinkingms.com/vadivel
> "almightygav" wrote:
>|||> When do the help files get updated to show changes made by the Service
> Packs?
You need to download the update for Books Online. BOL update is not in the s
ervice pack files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:FF370F16-5824-4E66-909F-2FFF2A1FB160@.microsoft.com...
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||Thanks almighty! I am glad i was of some help to you.
Btw as Tibor said, BOL updates are not part of Service pack files.
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||On Thu, 10 Nov 2005 05:22:04 -0800, "almightygav"
<almightygav@.discussions.microsoft.com> wrote:
>Thanks for that Uri, every day is a lesson eh!
>However, is there any way to drill down to a more granular level so that I
>can identify what line of the proc it is running?
>The problem we are encountering is that on occasions a couple of large
>procedures grind to a standstill and we are trying to identify where they a
re
>slow.
>Any more assistance would be gratefully received.
You can get that level of detail by running the profiler.
There's probably some voodoo way to get it via queries, but I don't
know it.
Well, maybe I do, "set statistics profile on" and then run your fat SP
and see if that helps, if that's not too much information!
J.
