SQL Stored procedure

**Maligui** · Oct 19 '06, 09:35 PM

Re: SQL Stored procedure

A common practice in coding is to have long procedures run in the
background. I would run the procedure on another thread.

--
Thiele Enterprises - The Power Is In Your Hands Now!
"Darin" <darin_nospam@n ospameverwrote in message
news:uDi2ig88GH A.2268@TK2MSFTN GP05.phx.gbl...

How can I execute a stored procedure and NOT wait for it to finish
before my program continues. The SP takes about 15 minutes to run, so I
want to run it but not have the program locked while it is running.
>
I am running it via:
>
strCommand = New SqlCommand(in_s p, xconn)
strCommand.Comm andType = CommandType.Sto redProcedure
strCommand.Comm andTimeout = 3600 '60 minutes
strCommand.Exec uteNonQuery
>
Thanks
>
Darin
>
*** Sent via Developersdex http://www.developersdex.com ***

**Maligui** · Oct 19 '06, 09:35 PM

Re: SQL Stored procedure

Try this code.

Dim t As New System.Threadin g.Thread(Addres sOf RunStoredProced ure)

Sub Something()
t.Start()
End Sub

Private Sub RunStoredProced ure()
strCommand = New SqlCommand(in_s p, xconn)
strCommand.Comm andType = CommandType.Sto redProcedure
strCommand.Comm andTimeout = 3600 '60 minutes
strCommand.Exec uteNonQuery()
End Sub

--
Thiele Enterprises - The Power Is In Your Hands Now!
"Darin" <darin_nospam@n ospameverwrote in message
news:uDi2ig88GH A.2268@TK2MSFTN GP05.phx.gbl...

How can I execute a stored procedure and NOT wait for it to finish
before my program continues. The SP takes about 15 minutes to run, so I
want to run it but not have the program locked while it is running.
>
I am running it via:
>
strCommand = New SqlCommand(in_s p, xconn)
strCommand.Comm andType = CommandType.Sto redProcedure
strCommand.Comm andTimeout = 3600 '60 minutes
strCommand.Exec uteNonQuery
>
Thanks
>
Darin
>
*** Sent via Developersdex http://www.developersdex.com ***

**Darin** · Oct 20 '06, 01:45 AM

Re: SQL Stored procedure

FOllowing up on the threading code, how will the program know it is
finished?

Darin

*** Sent via Developersdex http://www.developersdex.com ***

gs · Oct 20 '06, 02:05 AM

Re: SQL Stored procedure

t.IsAlive?

so when your program absolutely need the result before executing some
functionality, you can check that first at that point.

have tried the sample code given by Maligui yet?

Thread.IsAlive Property
Gets a value indicating the execution status of the current thread.

reference sample
Public ReadOnly Property IsAlive As Boolean
Visual Basic (Usage)
Dim instance As Thread
Dim value As Boolean

value = instance.IsAliv e
"Darin" <darin_nospam@n ospameverwrote in message
news:e9IKDv%238 GHA.1256@TK2MSF TNGP04.phx.gbl. ..

FOllowing up on the threading code, how will the program know it is
finished?
>
Darin
>
*** Sent via Developersdex http://www.developersdex.com ***

**Ryan S. Thiele** · Oct 20 '06, 02:25 AM

Re: SQL Stored procedure

The thread will exit automatically. Or you can terminate it by using:

t.cancel

To reastart the thread, you have to make a new instance.

t = new thread

Also you can name the thread so you can keep track of it.

t = new thread
t.Name = "A Name"

the console will say:

Thread (A Name) as exitied with code 0 (0 might be in hex [0x000])

You can email me for some further help, been programming vb since 1996 :)
I'm sing VS2005 team at this point. I might go enterprise verison.

--
Thiele Enterprises - The Power Is In Your Hands Now!
"gs" <gs@dontMail.te luswrote in message
news:Oea5g5%238 GHA.4632@TK2MSF TNGP02.phx.gbl. ..

t.IsAlive?
>
so when your program absolutely need the result before executing some
functionality, you can check that first at that point.
>
have tried the sample code given by Maligui yet?
>
Thread.IsAlive Property
Gets a value indicating the execution status of the current thread.
>
>
reference sample
Public ReadOnly Property IsAlive As Boolean
Visual Basic (Usage)
Dim instance As Thread
Dim value As Boolean
>
value = instance.IsAliv e
"Darin" <darin_nospam@n ospameverwrote in message
news:e9IKDv%238 GHA.1256@TK2MSF TNGP04.phx.gbl. ..

>FOllowing up on the threading code, how will the program know it is
>finished?
>>
>Darin
>>
>*** Sent via Developersdex http://www.developersdex.com ***

>
>

**Stephany Young** · Oct 20 '06, 04:55 AM

Re: SQL Stored procedure

It would appear that everybody has missed the point here.

You really need to be checking out the :
SqlCommand.Begi nExecuteNonQuer y
and
SqlCommand.EndE xecuteNonQuery
methods, which are specifically designed for exactly the asynchronous
behavior you appear to be looking for.

"Darin" <darin_nospam@n ospameverwrote in message
news:uDi2ig88GH A.2268@TK2MSFTN GP05.phx.gbl...

How can I execute a stored procedure and NOT wait for it to finish
before my program continues. The SP takes about 15 minutes to run, so I
want to run it but not have the program locked while it is running.
>
I am running it via:
>
strCommand = New SqlCommand(in_s p, xconn)
strCommand.Comm andType = CommandType.Sto redProcedure
strCommand.Comm andTimeout = 3600 '60 minutes
strCommand.Exec uteNonQuery
>
Thanks
>
Darin
>
*** Sent via Developersdex http://www.developersdex.com ***

**Robinson** · Oct 20 '06, 11:15 AM

Re: SQL Stored procedure

"Stephany Young" <noone@localhos twrote in message
news:%230JWRXA9 GHA.5012@TK2MSF TNGP06.phx.gbl. ..

It would appear that everybody has missed the point here.
>
You really need to be checking out the :
SqlCommand.Begi nExecuteNonQuer y
and
SqlCommand.EndE xecuteNonQuery
methods, which are specifically designed for exactly the asynchronous
behavior you appear to be looking for.
>
>
"Darin" <darin_nospam@n ospameverwrote in message
news:uDi2ig88GH A.2268@TK2MSFTN GP05.phx.gbl...

>How can I execute a stored procedure and NOT wait for it to finish
>before my program continues. The SP takes about 15 minutes to run, so I
>want to run it but not have the program locked while it is running.
>>
>I am running it via:
>>
>strCommand = New SqlCommand(in_s p, xconn)
>strCommand.Com mandType = CommandType.Sto redProcedure
>strCommand.Com mandTimeout = 3600 '60 minutes
>strCommand.Exe cuteNonQuery
>>
>Thanks
>>

This is true yes. I'm using it in my client, however the OP should still
really be using threads to do this. Once you get the pattern right it's
fairly simple and much cleaner. The thread should "invoke" a method on the
form when it has completed (ie. just before it exists it's ThreadMain
function). the Begin/EndExecute should be used in a loop to check a flag to
see if the user has cancelled the operation. This means the main process
can continue on with it's work and won't have to sit in "DoEvents" loop and
you can potentially cancel the database operation if the user wants to.

gs · Oct 21 '06, 06:35 AM

Re: SQL Stored procedure

great idea for user cancel option provided the database operation has
transaction processing and rollback capability
"Robinson" <toomuchspamhas passed@myinboxt oomuchtoooften. comwrote in
message news:ehabub$7u1 $1$8302bc10@new s.demon.co.uk.. .

"Stephany Young" <noone@localhos twrote in message
news:%230JWRXA9 GHA.5012@TK2MSF TNGP06.phx.gbl. ..

>It would appear that everybody has missed the point here.
>>
>You really need to be checking out the :
> SqlCommand.Begi nExecuteNonQuer y
>and
> SqlCommand.EndE xecuteNonQuery
>methods, which are specifically designed for exactly the asynchronous
>behavior you appear to be looking for.
>>
>>
>"Darin" <darin_nospam@n ospameverwrote in message
>news:uDi2ig88G HA.2268@TK2MSFT NGP05.phx.gbl.. .

>>How can I execute a stored procedure and NOT wait for it to finish
>>before my program continues. The SP takes about 15 minutes to run, so I
>>want to run it but not have the program locked while it is running.
>>>
>>I am running it via:
>>>
>>strCommand = New SqlCommand(in_s p, xconn)
>>strCommand.Co mmandType = CommandType.Sto redProcedure
>>strCommand.Co mmandTimeout = 3600 '60 minutes
>>strCommand.Ex ecuteNonQuery
>>>
>>Thanks
>>>

>
This is true yes. I'm using it in my client, however the OP should still
really be using threads to do this. Once you get the pattern right it's
fairly simple and much cleaner. The thread should "invoke" a method on
the form when it has completed (ie. just before it exists it's ThreadMain
function). the Begin/EndExecute should be used in a loop to check a flag
to see if the user has cancelled the operation. This means the main
process can continue on with it's work and won't have to sit in "DoEvents"
loop and you can potentially cancel the database operation if the user
wants to.
>

SQL Stored procedure

SQL Stored procedure

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment