How to determine if a SP is running?

I need to to modify a stored procedure so that it can
determine if it is already running. I could create a
table to add/opdate a status record, but I would prefer
to read a system table looking for the proc running on
a connection if possible.
If anyone has done something like this, I would really
like to hear about it.
tia,
BillYou can't do that directly, but you can use an application lock inside your
stored procedure. See sp_getapplock in Books Online for the details.
Jacco Schalkwijk
SQL Server MVP
<bill_sheets@.hotmail.com> wrote in message
news:1107442939.517642.53580@.g14g2000cwa.googlegroups.com...
>I need to to modify a stored procedure so that it can
> determine if it is already running. I could create a
> table to add/opdate a status record, but I would prefer
> to read a system table looking for the proc running on
> a connection if possible.
> If anyone has done something like this, I would really
> like to hear about it.
> tia,
> Bill
>

How to detect suspect status out of 100 servers in network.

1) Suppose 100 servers...if one goes in suspect db..how can we check which d
atabase is suspect..
all 100 servers are scattered around the world...and we need to immediate th
e server went to suspect database...
Hows that possible...
2)log shipping..
can stand by server in non recovery mode..be used to handle and transactions
whether it is Read only or write and read...
Can we use tempdb in standby server for normal creation and writing in temp
tables..sanjaya
1)
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @.dbname AND status & 256 = 256) != 1
BEGIN
PRINT 'The database is not in suspect mode.'
RETURN (1)
END
2)
Please refer to BOL
"sanjaya" <anonymous@.discussions.microsoft.com> wrote in message
news:D017C0EC-7C4D-42AE-AFF3-AEF62355A787@.microsoft.com...

quote:

---

> 1) Suppose 100 servers...if one goes in suspect db..how can we check which

---

database is suspect..

quote:

---

> all 100 servers are scattered around the world...and we need to immediate

---

the server went to suspect database...

quote:

---

> Hows that possible...
> 2)log shipping..
> can stand by server in non recovery mode..be used to handle and

---

transactions whether it is Read only or write and read...

quote:

---

> Can we use tempdb in standby server for normal creation and writing in

---

temp tables..

quote:

---

>
>

---

|||I scan and check the status of all my databases by
actaully querying them -- i.e. trying to run a simple
query. Then, I check the error code to determine what
state the database is in.
You can't make any change to a database in the standby
mode.
Linchi

quote:

---

>--Original Message--
>1) Suppose 100 servers...if one goes in suspect db..how

---

can we check which database is suspect..

quote:

---

>all 100 servers are scattered around the world...and we

---

need to immediate the server went to suspect database...

quote:

---

>Hows that possible...
>2)log shipping..
> can stand by server in non recovery mode..be used to

---

handle and transactions whether it is Read only or write
and read...

quote:

---

>Can we use tempdb in standby server for normal creation

---

and writing in temp tables..

quote:

---

>
>
>.
>

---

How to detect suspect status out of 100 servers in network.

1) Suppose 100 servers...if one goes in suspect db..how can we check which database is suspect.
all 100 servers are scattered around the world...and we need to immediate the server went to suspect database..
Hows that possible..
2)log shipping.
can stand by server in non recovery mode..be used to handle and transactions whether it is Read only or write and read..
Can we use tempdb in standby server for normal creation and writing in temp tables.sanjaya
1)
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @.dbname AND status & 256 = 256) != 1
BEGIN
PRINT 'The database is not in suspect mode.'
RETURN (1)
END
2)
Please refer to BOL
"sanjaya" <anonymous@.discussions.microsoft.com> wrote in message
news:D017C0EC-7C4D-42AE-AFF3-AEF62355A787@.microsoft.com...
> 1) Suppose 100 servers...if one goes in suspect db..how can we check which
database is suspect..
> all 100 servers are scattered around the world...and we need to immediate
the server went to suspect database...
> Hows that possible...
> 2)log shipping..
> can stand by server in non recovery mode..be used to handle and
transactions whether it is Read only or write and read...
> Can we use tempdb in standby server for normal creation and writing in
temp tables..
>
>|||I scan and check the status of all my databases by
actaully querying them -- i.e. trying to run a simple
query. Then, I check the error code to determine what
state the database is in.
You can't make any change to a database in the standby
mode.
Linchi
>--Original Message--
>1) Suppose 100 servers...if one goes in suspect db..how
can we check which database is suspect..
>all 100 servers are scattered around the world...and we
need to immediate the server went to suspect database...
>Hows that possible...
>2)log shipping..
> can stand by server in non recovery mode..be used to
handle and transactions whether it is Read only or write
and read...
>Can we use tempdb in standby server for normal creation
and writing in temp tables..
>
>
>.
>

How to define global var?

How to I create a global variable for several SPs to share? For example, I
might have two status vars, such as statusred = 3 and statusgreen = 1.
Thanks,
BrettInsert the value(s) in a table and have each of your stored procs select the
value from the table?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Brett" <no@.spam.com> wrote in message
news:%23YLgthPIFHA.1176@.TK2MSFTNGP12.phx.gbl...
> How to I create a global variable for several SPs to share? For example,
I
> might have two status vars, such as statusred = 3 and statusgreen = 1.
> Thanks,
> Brett
>|||That's one way but isn't that inefficient?
How does SQL Server use the @.@.ERROR global var for example?
Thanks,
Brett
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uj8mZsPIFHA.3076@.TK2MSFTNGP10.phx.gbl...
> Insert the value(s) in a table and have each of your stored procs select
> the
> value from the table?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Brett" <no@.spam.com> wrote in message
> news:%23YLgthPIFHA.1176@.TK2MSFTNGP12.phx.gbl...
> I
>|||Do the two SPs have anything in common? i.e., do they run in support of one
another? or in rsponse to the same trigger? or are they totally indodependan
t
except for their use of this same value?
If they're functionally related, consider creating a single SP That aclls
both of them, and have that SP pass the value to both SPs that need it.
If they're not, it sounds like what you have is (one of potentially many)
application configuration settings. These can be stored and propogated to
wherever they are needed in a variety of ways, including externally in XML
files, or the Registry, or internally in a separate Database Table that has
name value pairs (Setting, value).
Don;t worry about efficiency ( I Think you meant performance) because SQL is
optimized for this. If the value is used often, it will be cached and kept
in memory anyway.
"Brett" wrote:

> How to I create a global variable for several SPs to share? For example,
I
> might have two status vars, such as statusred = 3 and statusgreen = 1.
> Thanks,
> Brett
>
>|||"Brett" <no@.spam.com> wrote in message
news:O9k4M4PIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> That's one way but isn't that inefficient?
> How does SQL Server use the @.@.ERROR global var for example?
No; SQL Server will keep the value in memory if it's accessed often --
the in-memory cache frees data based on usage, so keep using it and it
stays.
As for @.@.ERROR, that's a function, not a global variable. It's just
named similarly to a variable.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||From BOL ...@.@.ERROR is cleared and reset on each statement executed...
Where did you get the idea that this is a global variable?
Why don't you just pass the 2 status's as parameters from 1 SP to the other?
"Brett" <no@.spam.com> wrote in message
news:O9k4M4PIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> That's one way but isn't that inefficient?
> How does SQL Server use the @.@.ERROR global var for example?
> Thanks,
> Brett
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uj8mZsPIFHA.3076@.TK2MSFTNGP10.phx.gbl...
>|||Think about this for a moment. It is a relational database with the primary
goal of storing data in a table. The whole purpose is optimal data
handling. For a few values that you will be dealing with they will likely
be stored in memory throughout the process anyhow.
Just create a permanent table that your procs use and they can share data on
multiple connections. You will have to figure out how to handle garbage
collection when the programs stop and/or when the start however.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Brett" <no@.spam.com> wrote in message
news:O9k4M4PIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> That's one way but isn't that inefficient?
> How does SQL Server use the @.@.ERROR global var for example?
> Thanks,
> Brett
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uj8mZsPIFHA.3076@.TK2MSFTNGP10.phx.gbl...
>