How can I check if it is currently executing?

Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
RobertRobert
There may be system stored procedures that return this info but surely the l
ogical workaround is just to use your own stored value. You are, after all,
working with an RDBMS!
CREATE TABLE long_running_sp_status ( exec_status bit not null )
GO
CREATE PROCEDURE long_running_sp
AS
IF EXISTS(SELECT 1 FROM long_running_sp_status WHERE exec_status = 1)
BEGIN /* already running */
RETURN
END
/* not running */
BEGIN TRANSACTION
UPDATE long_running_sp_status SET exec_status = 1
...
etc
UPDATE long_running_sp_status SET exec_status = 0
COMMIT TRANSACTION
GO
Regards,
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:ecAhfLeTGHA.474
0@.TK2MSFTNGP14.phx.gbl...
Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
Robert|||We are currently using a process similar to this. but the problem is...
If the procedure for any reason crashes or does not complete, the "Currently
Running" flag does not get set back to False
Any Ideas?.?.?.
Robert
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message news:%23EuBy
TeTGHA.5496@.TK2MSFTNGP11.phx.gbl...
Robert
There may be system stored procedures that return this info but surely the l
ogical workaround is just to use your own stored value. You are, after all,
working with an RDBMS!
CREATE TABLE long_running_sp_status ( exec_status bit not null )
GO
CREATE PROCEDURE long_running_sp
AS
IF EXISTS(SELECT 1 FROM long_running_sp_status WHERE exec_status = 1)
BEGIN /* already running */
RETURN
END
/* not running */
BEGIN TRANSACTION
UPDATE long_running_sp_status SET exec_status = 1
...
etc
UPDATE long_running_sp_status SET exec_status = 0
COMMIT TRANSACTION
GO
Regards,
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:ecAhfLeTGHA.474
0@.TK2MSFTNGP14.phx.gbl...
Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
Robert|||Hi
Why does it "crash"? It shouldn't crash... what exactly happens? Do you t
hink there is a design or implementation flaw you need to address?
If the SP can be set-up as a job to be run by Server Agent, and if it is onl
y ever going to be run by the agent on a specified schedule, you could use s
p_jobhelp (or whatever its called) to check if the *job* has stopped or is s
till running...
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uCaLYdeTGHA.434
0@.tk2msftngp13.phx.gbl...
We are currently using a process similar to this. but the problem is...
If the procedure for any reason crashes or does not complete, the "Currently
Running" flag does not get set back to False
Any Ideas?.?.?.
Robert
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message news:%23EuBy
TeTGHA.5496@.TK2MSFTNGP11.phx.gbl...
Robert
There may be system stored procedures that return this info but surely the l
ogical workaround is just to use your own stored value. You are, after all,
working with an RDBMS!
CREATE TABLE long_running_sp_status ( exec_status bit not null )
GO
CREATE PROCEDURE long_running_sp
AS
IF EXISTS(SELECT 1 FROM long_running_sp_status WHERE exec_status = 1)
BEGIN /* already running */
RETURN
END
/* not running */
BEGIN TRANSACTION
UPDATE long_running_sp_status SET exec_status = 1
...
etc
UPDATE long_running_sp_status SET exec_status = 0
COMMIT TRANSACTION
GO
Regards,
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:ecAhfLeTGHA.474
0@.TK2MSFTNGP14.phx.gbl...
Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
Robert|||check out sp_getapplock in BOL.|||rmg66 (rgwathney__xXx__primepro.com) writes:
> Here's a good one.
> Does any one out there know how to tell if a "particular" (by name)
> stored procedure is currently being executed?
One could inspect sysprocess.sql_handle for every process, but if the
procedure has called an inner procedure you would not see it.
Anyway, Scott had the right answer for you: use Application locks.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm sorry if I'm being dense. But I don't quite understand how this (sp_geta
pplock) is to be used.
Will it lock access to any db object (e.g. a particular stored procedure) un
til it is unlocked?
When I tested it. It didn't seeem to do what I expected...
I used it to lock a table object and then attempted to update the table. I d
id it inside a transaction.
I expected the table to reject the update but it didn't.
Am I missing something?
Can someone give me an example of how I could use this to prevent and stored
procedure to be called while it is currently executing?
Thanks, Robert
"Scott Morris" <bogus@.bogus.com> wrote in message news:ePqOKneTGHA.4264@.TK2MSFTNGP11.phx.gb
l...
> check out sp_getapplock in BOL.
>|||> Will it lock access to any db object (e.g. a particular stored procedure)
> until it is unlocked?
It doesn't really "lock" anything. It effectively sets a flag that can be
used as a gatekeeper. If not used correctly, it will not be useful.

> When I tested it. It didn't seeem to do what I expected...
> I used it to lock a table object and then attempted to update the table. I
> did it inside a transaction.
> I expected the table to reject the update but it didn't.
> Am I missing something?
Yes - the whole idea is to serialize processing. If you did not attempt to
execute the procedure simultaneously from multiple connections, then you
only tested the syntactical correctness of your usage (details of which you
did not post so we cannot determine if you used it correctly).

> Can someone give me an example of how I could use this to prevent and
> stored procedure to be called while it is currently executing?
BOL has some important information. In addition, you can search the
newsgroups (as well as the internet) for additional information and
examples. Note that it will not PREVENT a "... stored procedure to be
called..."; it can be used to coordinate simultaneous executions of the
procedure in whatever manner you desire.|||sp_getapplock doesn't lock any data inside the database, it is just there fo
r *you* to use to define
a lock using SQL Servers lock semantics and release the lock at will.
ALTER PROC p AS
BEGIN TRAN
EXEC sp_getapplock @.Resource = 'myLock',
@.LockMode = 'Exclusive'
RAISERROR('Before', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:20'
EXEC sp_releaseapplock @.Resource = 'myLock'
COMMIT
RAISERROR('After', 10, 1) WITH NOWAIT
GO
EXEC p
--Now, within 20 seconds, execute this from another connection
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:uTWo0qoTGHA.2276@.tk2msftngp13.phx.gbl...
> I'm sorry if I'm being dense. But I don't quite understand how this (sp_ge
tapplock) is to be used.
> Will it lock access to any db object (e.g. a particular stored procedure)
until it is unlocked?
> When I tested it. It didn't seeem to do what I expected...
> I used it to lock a table object and then attempted to update the table. I
did it inside a
> transaction.
> I expected the table to reject the update but it didn't.
> Am I missing something?
> Can someone give me an example of how I could use this to prevent and stor
ed procedure to be
> called while it is currently executing?
> Thanks, Robert
>
>
> "Scott Morris" <bogus@.bogus.com> wrote in message news:ePqOKneTGHA.4264@.TK
2MSFTNGP11.phx.gbl...
>|||Thanks All
and thanks Tibor for that the example of how it works...
Robert
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:eL
TjZKpTGHA.196@.TK2MSFTNGP10.phx.gbl...
> sp_getapplock doesn't lock any data inside the database, it is just there
for *you* to use to define a lock using SQL Servers lock
> semantics and release the lock at will.
> ALTER PROC p AS
> BEGIN TRAN
> EXEC sp_getapplock @.Resource = 'myLock',
> @.LockMode = 'Exclusive'
> RAISERROR('Before', 10, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:20'
> EXEC sp_releaseapplock @.Resource = 'myLock'
> COMMIT
> RAISERROR('After', 10, 1) WITH NOWAIT
> GO
> EXEC p
> --Now, within 20 seconds, execute this from another connection
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uTWo0qoTGHA.2
276@.tk2msftngp13.phx.gbl...
>

How can I be convinced by what the model says in particular for Time series?

Hi, all here,

I want to hear from you for your experiences on how can we be convinced by what the model predicts particularly for Time Series algorithm? As we are not able to see the model accuracy chart for that, in this case, how can use the model results? E.g. we wanna know the possible sales amount of next month for a particular store in order to buy in the goods, in this case, how can we make the most of the prediction by the model? To shop ower, well, if the result is too far away from the usual sales amount, then it is unbelievable, thus, in this case, what else can we try? Keep training the models until its results sounds reasonable? Or what else can we try?Thanks in advance for your advices and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Manually, you could train your model with only some part of your data (say, without the last month) and see how the predictions compare with the actual values for the last month.

The algorithm allows you to specify a parameter, HISTORIC_MODEL_COUNT.
In the series viewer, you can choose to display historic predictions and compare them with the real data

This should give you an idea of how good the predictions are. Also, in the series viewer, you can choose to display the error associated with the forecast values, which should give an idea about the confidence of the prediction|||

Hi, Bogdan,

Thanks a lot for your very kind advices and help.

With best regards,

Yours sincerely,

How can I be convinced by what the model says in particular for Time series?

Hi, all here,

I want to hear from you for your experiences on how can we be convinced by what the model predicts particularly for Time Series algorithm? As we are not able to see the model accuracy chart for that, in this case, how can use the model results? E.g. we wanna know the possible sales amount of next month for a particular store in order to buy in the goods, in this case, how can we make the most of the prediction by the model? To shop ower, well, if the result is too far away from the usual sales amount, then it is unbelievable, thus, in this case, what else can we try? Keep training the models until its results sounds reasonable? Or what else can we try?Thanks in advance for your advices and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Manually, you could train your model with only some part of your data (say, without the last month) and see how the predictions compare with the actual values for the last month.

The algorithm allows you to specify a parameter, HISTORIC_MODEL_COUNT.
In the series viewer, you can choose to display historic predictions and compare them with the real data

This should give you an idea of how good the predictions are. Also, in the series viewer, you can choose to display the error associated with the forecast values, which should give an idea about the confidence of the prediction|||

Hi, Bogdan,

Thanks a lot for your very kind advices and help.

With best regards,

Yours sincerely,

sql

How building a report referenced by a map?

I need to create a report with a map that allows me to selece a particular
set of data when I click on a map element.
For example, I have an employee table containing the data related the country.
I want to build a map respect this data. This it is easy.
Then when I click on a particular value of country, the report must load the
data of employee table selecting this value.
Is it possible to do it? Do I must write embedded code?
How can I solve this issue?
Many thanksCheck this out:
http://dspace.dial.pipex.com/town/drive/xxg48/CustomParameters.html
José.
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:60EF9D9F-C19C-47BD-9745-2BF5DCB694A8@.microsoft.com...
>I need to create a report with a map that allows me to selece a particular
> set of data when I click on a map element.
> For example, I have an employee table containing the data related the
> country.
> I want to build a map respect this data. This it is easy.
> Then when I click on a particular value of country, the report must load
> the
> data of employee table selecting this value.
> Is it possible to do it? Do I must write embedded code?
> How can I solve this issue?
> Many thanks

How am I protecting my Database from freign people at my network

as well particular tables in my database should be acceible only for users supply special U/N & PWD within my program

(Windows application - VB.NET)

thanks for referring

You better to have a security policy for your ENV. SQL Server follows two level security architecture. One at server level (LOGIN) and the other at Databaes level (User). You can restrict the permission on objects. you can give access to table(i would always prefer to create view or sp to access table instead of giving direct select permission on table) to individual user or role.

http://msdn2.microsoft.com/en-us/library/aa905172(SQL.80).aspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0522.mspx

Madhu