Running a 3 step dts process serially in SQL Server 2000

Hi,
I trying to run a 3 steps process in one dts package. Is there a way to run
these processes serially? It is currently running in parallel mode. This of
course is causing a blocking issue in SQL Server 2000.
Please let me know if this is possible in SQL Server 2000. And if it is,
how do I go about implementing it.
Thank you very much in advance for your help.
Thanks,
Sally
I use workflow. You select the first step, then select the second step and
hit workflow.
Russ
"Sally" wrote:

> Hi,
> I trying to run a 3 steps process in one dts package. Is there a way to run
> these processes serially? It is currently running in parallel mode. This of
> course is causing a blocking issue in SQL Server 2000.
> Please let me know if this is possible in SQL Server 2000. And if it is,
> how do I go about implementing it.
> Thank you very much in advance for your help.
> --
> Thanks,
> Sally
sql

Running a 3 step dts process serially in SQL Server 2000

Hi,
I trying to run a 3 steps process in one dts package. Is there a way to run
these processes serially? It is currently running in parallel mode. This o
f
course is causing a blocking issue in SQL Server 2000.
Please let me know if this is possible in SQL Server 2000. And if it is,
how do I go about implementing it.
Thank you very much in advance for your help.
Thanks,
SallyI use workflow. You select the first step, then select the second step and
hit workflow.
Russ
"Sally" wrote:

> Hi,
> I trying to run a 3 steps process in one dts package. Is there a way to r
un
> these processes serially? It is currently running in parallel mode. This
of
> course is causing a blocking issue in SQL Server 2000.
> Please let me know if this is possible in SQL Server 2000. And if it is,
> how do I go about implementing it.
> Thank you very much in advance for your help.
> --
> Thanks,
> Sally

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
KannanIs this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegroups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ' multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.
> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegroups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
Kannan
Is this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>
|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>
|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan
|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.

> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegro ups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>
|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ? multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan
|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.

> will opening multiple connections from end application help ? multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.

> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegr oups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ? multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
KannanIs this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.

> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegroups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ' multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.

> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.

> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegroups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run a Job in Parallel?

Hi,
We have a job that I would like to run every 1 minute. For business &
performance reasons we would like the job to be able to run itself in
paralell - aka if there is alot of work to do the job can start a second, or
third, etc. instance of itself.
However we get this error whenever we ry to start the second instance:
Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to run job
SLARunReport (from User CFC\00SQLSRVQA) refused because the job is already
running from a request by Schedule 2 (1 Minute Intervals). [SQLSTATE 42000]
(Error 22022). The step failed.
Is there any way around this? Is there a way to allow a job to run in
paralell?
--RichardRichard wrote:
> Hi,
> We have a job that I would like to run every 1 minute. For business &
> performance reasons we would like the job to be able to run itself in
> paralell - aka if there is alot of work to do the job can start a
> second, or third, etc. instance of itself.
> However we get this error whenever we ry to start the second instance:
> Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to
> run job SLARunReport (from User CFC\00SQLSRVQA) refused because the
> job is already running from a request by Schedule 2 (1 Minute
> Intervals). [SQLSTATE 42000] (Error 22022). The step failed.
> Is there any way around this? Is there a way to allow a job to run in
> paralell?
> --Richard
You can create multiple jobs and execute as many of them as needed. You
can have Job1, for example, clone itself using T-SQL whenever it needs
to spawn another instance of itself.
One way to do this is to use SQL Enterprise Manager and have it generate
the T-SQL. Once you have it, it's just a matter of creating a unique job
name (the newid() function is great for this) in the T-SQL and
incorporating it into the existing job or make it a new job altogether.
If you have a job that creates the job you actually want to execute, you
can call the "creation" job as many times as you need since all it does
is quickly create and start the job.
David Gugick
Imceda Software
www.imceda.com|||Basically no there isn't. In a case like this I think it is best handled by
running sp's from an outside application that can be more flexible than SQL
Agent. But another option is to create two or three jobs that run at 1
minute intervals but are spaced 20 seconds apart. Just make sure what ever
code they are running is fully capable of doing the work without contention
with each other.
--
Andrew J. Kelly SQL MVP
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:2BB6E1EE-2D8D-46D1-8B20-8EBE3C199C34@.microsoft.com...
> Hi,
> We have a job that I would like to run every 1 minute. For business &
> performance reasons we would like the job to be able to run itself in
> paralell - aka if there is alot of work to do the job can start a second,
> or
> third, etc. instance of itself.
> However we get this error whenever we ry to start the second instance:
> Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to run job
> SLARunReport (from User CFC\00SQLSRVQA) refused because the job is already
> running from a request by Schedule 2 (1 Minute Intervals). [SQLSTATE
> 42000]
> (Error 22022). The step failed.
> Is there any way around this? Is there a way to allow a job to run in
> paralell?
> --Richard
>
>

Run a Job in Parallel?

Hi,
We have a job that I would like to run every 1 minute. For business &
performance reasons we would like the job to be able to run itself in
paralell - aka if there is alot of work to do the job can start a second, or
third, etc. instance of itself.
However we get this error whenever we ry to start the second instance:
Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to run job
SLARunReport (from User CFC\00SQLSRVQA) refused because the job is already
running from a request by Schedule 2 (1 Minute Intervals). [SQLSTATE 420
00]
(Error 22022). The step failed.
Is there any way around this? Is there a way to allow a job to run in
paralell?
--RichardRichard wrote:
> Hi,
> We have a job that I would like to run every 1 minute. For business &
> performance reasons we would like the job to be able to run itself in
> paralell - aka if there is alot of work to do the job can start a
> second, or third, etc. instance of itself.
> However we get this error whenever we ry to start the second instance:
> Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to
> run job SLARunReport (from User CFC\00SQLSRVQA) refused because the
> job is already running from a request by Schedule 2 (1 Minute
> Intervals). [SQLSTATE 42000] (Error 22022). The step failed.
> Is there any way around this? Is there a way to allow a job to run in
> paralell?
> --Richard
You can create multiple jobs and execute as many of them as needed. You
can have Job1, for example, clone itself using T-SQL whenever it needs
to spawn another instance of itself.
One way to do this is to use SQL Enterprise Manager and have it generate
the T-SQL. Once you have it, it's just a matter of creating a unique job
name (the newid() function is great for this) in the T-SQL and
incorporating it into the existing job or make it a new job altogether.
If you have a job that creates the job you actually want to execute, you
can call the "creation" job as many times as you need since all it does
is quickly create and start the job.
David Gugick
Imceda Software
www.imceda.com|||Basically no there isn't. In a case like this I think it is best handled by
running sp's from an outside application that can be more flexible than SQL
Agent. But another option is to create two or three jobs that run at 1
minute intervals but are spaced 20 seconds apart. Just make sure what ever
code they are running is fully capable of doing the work without contention
with each other.
Andrew J. Kelly SQL MVP
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:2BB6E1EE-2D8D-46D1-8B20-8EBE3C199C34@.microsoft.com...
> Hi,
> We have a job that I would like to run every 1 minute. For business &
> performance reasons we would like the job to be able to run itself in
> paralell - aka if there is alot of work to do the job can start a second,
> or
> third, etc. instance of itself.
> However we get this error whenever we ry to start the second instance:
> Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to run job
> SLARunReport (from User CFC\00SQLSRVQA) refused because the job is already
> running from a request by Schedule 2 (1 Minute Intervals). [SQLSTATE
> 42000]
> (Error 22022). The step failed.
> Is there any way around this? Is there a way to allow a job to run in
> paralell?
> --Richard
>
>

Run a Job in Parallel?

Hi,
We have a job that I would like to run every 1 minute. For business &
performance reasons we would like the job to be able to run itself in
paralell - aka if there is alot of work to do the job can start a second, or
third, etc. instance of itself.
However we get this error whenever we ry to start the second instance:
Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to run job
SLARunReport (from User CFC\00SQLSRVQA) refused because the job is already
running from a request by Schedule 2 (1 Minute Intervals). [SQLSTATE 42000]
(Error 22022). The step failed.
Is there any way around this? Is there a way to allow a job to run in
paralell?
--Richard
Richard wrote:
> Hi,
> We have a job that I would like to run every 1 minute. For business &
> performance reasons we would like the job to be able to run itself in
> paralell - aka if there is alot of work to do the job can start a
> second, or third, etc. instance of itself.
> However we get this error whenever we ry to start the second instance:
> Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to
> run job SLARunReport (from User CFC\00SQLSRVQA) refused because the
> job is already running from a request by Schedule 2 (1 Minute
> Intervals). [SQLSTATE 42000] (Error 22022). The step failed.
> Is there any way around this? Is there a way to allow a job to run in
> paralell?
> --Richard
You can create multiple jobs and execute as many of them as needed. You
can have Job1, for example, clone itself using T-SQL whenever it needs
to spawn another instance of itself.
One way to do this is to use SQL Enterprise Manager and have it generate
the T-SQL. Once you have it, it's just a matter of creating a unique job
name (the newid() function is great for this) in the T-SQL and
incorporating it into the existing job or make it a new job altogether.
If you have a job that creates the job you actually want to execute, you
can call the "creation" job as many times as you need since all it does
is quickly create and start the job.
David Gugick
Imceda Software
www.imceda.com
|||Basically no there isn't. In a case like this I think it is best handled by
running sp's from an outside application that can be more flexible than SQL
Agent. But another option is to create two or three jobs that run at 1
minute intervals but are spaced 20 seconds apart. Just make sure what ever
code they are running is fully capable of doing the work without contention
with each other.
Andrew J. Kelly SQL MVP
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:2BB6E1EE-2D8D-46D1-8B20-8EBE3C199C34@.microsoft.com...
> Hi,
> We have a job that I would like to run every 1 minute. For business &
> performance reasons we would like the job to be able to run itself in
> paralell - aka if there is alot of work to do the job can start a second,
> or
> third, etc. instance of itself.
> However we get this error whenever we ry to start the second instance:
> Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to run job
> SLARunReport (from User CFC\00SQLSRVQA) refused because the job is already
> running from a request by Schedule 2 (1 Minute Intervals). [SQLSTATE
> 42000]
> (Error 22022). The step failed.
> Is there any way around this? Is there a way to allow a job to run in
> paralell?
> --Richard
>
>