How to determine if stor proc parameter is output or input

I know that you can retrieve whether a parameter is for output buy way
of the "isoutparam" field, but is there anything that tells you whether
a parameter is input/output?
thanks(jw56578@.gmail.com) writes:
> I know that you can retrieve whether a parameter is for output buy way
> of the "isoutparam" field, but is there anything that tells you whether
> a parameter is input/output?

The only true output-only value is the return value. All parameters are
input/output. This is T-SQL, not Ada. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I am using a .net Parameter builder, to build paramter objects based
on system information. When it sees a parameter used for input and
output, it "isoutparam" indicates that it is an output, so its
"Direction" attribute is assigned a value of output. But if i want to
use it as an input, it doesn't work.|||(jw56578@.gmail.com) writes:
> I am using a .net Parameter builder, to build paramter objects based
> on system information. When it sees a parameter used for input and
> output, it "isoutparam" indicates that it is an output, so its
> "Direction" attribute is assigned a value of output. But if i want to
> use it as an input, it doesn't work.

That builder seems to have a bug. :-)

The only "parameter" that should have Direction.IsOutput is the return
value.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

how to determine if a stored proc is running

I need to determine whether a stored procedure is executiing and perform
various steps dependent on the state of the procedure in question (i.e.,
determined from sysprocesses.status). I know I can get the listing of
active commands sysprocesses.cmd (from the master table, sysprocesses),
but this doesn't give me the _stored procedure_ name, as it appears in
Enterprise Manager when you click on the ProcessID and it gives the
"Last TSQL Batch..." Where exactly is this information is this stored?
Somewhere, I assume, in MDDB?
TIA!
gms--Greg M. Silverman wrote:
> I need to determine whether a stored procedure is executiing and
> perform various steps dependent on the state of the procedure in
> question (i.e., determined from sysprocesses.status). I know I can get
> the listing of active commands sysprocesses.cmd (from the master
> table, sysprocesses), but this doesn't give me the _stored procedure_
> name, as it appears in Enterprise Manager when you click on the
> ProcessID and it gives the "Last TSQL Batch..." Where exactly is this
> information is this stored? Somewhere, I assume, in MDDB?
> TIA!
> gms--
>
okay, looks like DBCC INPUTBUFFER (pid) gives me what I need.
gms--

How to determine EXEC permission to an extended stored procedure?

The following proc indicates whether you have EXEC permission to a proc -
however it fails for extended procs. I'd be grateful for a fix!
A good test is @.SPNM = 'xp_sprintf'. Note that the proc is getting a valid
object id for the extended procs.
PROCEDURE procHasExecutePermission
( @.SPNM sysname,
@.HAS bit OUTPUT
) AS
BEGIN
SET NOCOUNT ON
DECLARE @.OID int
SET @.OID = OBJECT_ID(@.SPNM)
IF @.OID IS NULL
IF SUBSTRING(@.SPNM, 1, 3) = 'sp_' OR SUBSTRING(@.SPNM, 1, 3) = 'xp_'
SET @.OID = OBJECT_ID('master..' + @.SPNM)
IF @.OID IS NULL
SET @.HAS = 0
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
END
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc.It doesn't fail (only) for extended procs, it fails for the objects
that begin with 'sp_' or 'xp_', because you are getting the OBJECT_ID
for the object from the master database, but the PERMISSIONS function
accepts only object id-s for objects from the current database. In some
cases, it may look like it's working for some objects from master, but
that's only because the same object id is allocated in the current
database for another object.
If you need to check for objects that may be in master, I would use
another procedure like this:
USE master
GO
CREATE PROCEDURE procHasExecutePermission1
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
USE YourDatabase
GO
CREATE PROCEDURE procHasExecutePermission2
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
CREATE PROCEDURE procHasExecutePermission3
(@.SPNM sysname, @.HAS int OUTPUT) AS
IF LEFT(@.SPNM,3)='sp_'
EXEC master..procHasExecutePermission1 @.SPNM, @.HAS OUTPUT
IF @.HAS IS NOT NULL RETURN
EXEC procHasExecutePermission2 @.SPNM, @.HAS OUTPUT
I have used sysobjects to check the object type, so if you pass a table
name it will return NULL instead of 0.
You may want to improve these procedures using PARSENAME if you need to
allow procedure names prefixed with the database name. If the database
name may also be other than the current database or the master
database, then it gets complicated... there may be a solution by
calling the PERMISSIONS function from Dynamic SQL.
Razvan

How to determine compiled proc size?

How do I determine the amount of server memory a stored procedure (or any
other object) takes up?http://www.sql-server-performance.com/rd_data_cache.asp gives some
information regarding querying the syscacheobjects system table and what
each row in the table means. You should be able to use that to derive
the number of pages of memory used with many kinds of server objects.
Good luck,
Tony Sebion
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:

> How do I determine the amount of server memory a stored procedure (or any
> other object) takes up?|||There's also an undocumented DBCC command: DBCC MEMOBJLIST but it requires
starting the service with the T3654 flag and it doesn't show what's in AWE
memory.
"Tony Sebion" wrote:

> http://www.sql-server-performance.com/rd_data_cache.asp gives some
> information regarding querying the syscacheobjects system table and what
> each row in the table means. You should be able to use that to derive
> the number of pages of memory used with many kinds of server objects.
> Good luck,
> Tony Sebion
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:
>
>

How to detect a stored proc is running from TSQL?

I launch a stored proc from a job that is scheduled several times a day. I want the proc to detect if it is still running (from a previous process) and exit out if it is. I want to be absolutely sure it is or is not running.

Thanks!

I don't know of a straight forward way to check if a stored prcocedure is running but I have a couple of workarounds. Before that I just want to highlight if the only source that is running the stored procedure is the job you're talking about, then if it is still running and it is time for the next job, the next instance won't run so you wouldn't have the case that 2 job instances are running in the same time. Regardless here are the workarounds:

Include in your database a table that you can use to check the status of the stored procedure. For example a table called SystemStatus that includes a Key column and a Status column. Add a row for this procedure with initial value in the status false (for not running). Make the first statement in the procedure a check on the value of the status field for this row. If it true then another instance is running so end the procedure. If it is false then update it to true and let the final statement in the procedure update it back to false|||

I can't able to understand "I want the proc to detect if it is still running (from a previous process) and exit out if it is"

You mean, to find the current SP is already running on your Server (or job) before executing it.

If yes, you need not worry about it. Bcs if the current job is executing then the SQL server never initate the next scheduled job, it will wait to complete the current job, then it will find the next possible schedule to execute.

if you 2 or more parllel jobs with same SP (not really required) then you need to follow some log based events.

|||

Sami,

Thank you for your quick and detailed response. Actually I have used this method before. Unfortunately, this transaction is what I wanted to avoid here because it is a long-running proc that runs many other procs so I wanted to avoid holding blocking other users just to prevent my proc from running simultaneously. Thanks again!

Mr. P.

|||

You could use application locks in SQL Server. Something like below should work. This can be simplified a bit in SQL Server 2005.

Code Snippet

exec @.rc = sp_getapplock 'Only_this_SP', 'Exclusive', default, 0

if @.rc = -1

begin

-- SP is already running:

end

-- Release lock at end of SP:

exec @.rc = sp_releaseapplock 'Only_this_SP'