package detect that instance is already running?

Folks,

I have a package scheduled to run every hour.

Users have asked if, in addition to the scheduled run, they can have it

so that they could dump a file into the input directory and then

kick-off the package immediately.

Problem is that things fail if they try to start the package when the scheduled instance of the package is still running.

Is there any way that the package could check to see if an instance if

itself is currently executing and refuse to execute if there IS an

instance running?

PJHow do users start the package?

If they use the same job that you run periodically, just start it manually regardless of schedule using sp_start_job, the single-instance behavior will be implemented by Agent (sp_start_job fails if the job is currently running).|||

You can use the MSMQ package to synchronize packages.

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi Michael,

Currently I'm just piloting this by getting users to go to SQL Server Agent in the management studio, right clicking on the job and selecting "Start Job at step.."

and I was going to give them a small winforms app to run which would do it.. I suppose I could get the app to execute a call to sp_start_job now and when it fails just inform em that the scheduled job is running...

By the way, if the scheduled time between runs is too short.. and the job is started again before the previous run has completed.. Agent will just fail the new instance ? Is that right?

Thanks

PJ

|||

By the way, if the scheduled time between runs is too short.. and the job is started again before the previous run has completed.. Agent will just fail the new instance ? Is that right?

Yes, I believe Agent always ensures only instance of a Job is ever running, so it will not run the second instance in this scenario.

Overriding System Security

Hey folks!
First, if I am understanding correctly, even though I remove the
BUILTIN\Administrators from any roles and/or access in RS, they still have full
access to Report Manager, system settings and all. Is this correct?
If the above is true, has anyone found any way to circumvent this. I think this
a stupid concept. Just because a user is in the local Administrators group on a
server, doesn't mean he/she knows how to manage RS.
Thanks in advance.A local administrator can do just about anything on the box, including
format the hard drive. This does not mean the user should do it or knows
that he should not do it. That is why everyone is not an administrator.
Same applies to RS. Does the user need to be an administrator on the box?
If you remove the Builtin Administrators, they will not have full access to
RS. They can still do admin type work on RS, but will find they cannot view
reports and many other things.
--
| Date: Tue, 28 Jun 2005 10:01:41 -0500
| From: Darrell <Darrell.Wright.nospam@.okc.gov>
| User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Overriding System Security
| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
| Lines: 1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hey folks!
|
| First, if I am understanding correctly, even though I remove the
| BUILTIN\Administrators from any roles and/or access in RS, they still
have full
| access to Report Manager, system settings and all. Is this correct?
|
| If the above is true, has anyone found any way to circumvent this. I
think this
| a stupid concept. Just because a user is in the local Administrators
group on a
| server, doesn't mean he/she knows how to manage RS.
|
| Thanks in advance.
||||Brad Syputa - MS wrote:
> A local administrator can do just about anything on the box, including
> format the hard drive. This does not mean the user should do it or knows
> that he should not do it. That is why everyone is not an administrator.
>
Good point.
> Same applies to RS. Does the user need to be an administrator on the box?
>
There are a number of AD groups that these users are in that have admin rights
on the server so they can perform various maintenance tasks.
> If you remove the Builtin Administrators, they will not have full access to
> RS. They can still do admin type work on RS, but will find they cannot view
> reports and many other things.
As I said, the BUILTIN\Administrators was removed from RS altogether. Is there
any way, perhaps through IIS, that the local admins could have their access to
RS restricted?
> --
> | Date: Tue, 28 Jun 2005 10:01:41 -0500
> | From: Darrell <Darrell.Wright.nospam@.okc.gov>
> | User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
> | X-Accept-Language: en-us, en
> | MIME-Version: 1.0
> | Subject: Overriding System Security
> | Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> | Content-Transfer-Encoding: 7bit
> | Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
> | Lines: 1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hey folks!
> |
> | First, if I am understanding correctly, even though I remove the
> | BUILTIN\Administrators from any roles and/or access in RS, they still
> have full
> | access to Report Manager, system settings and all. Is this correct?
> |
> | If the above is true, has anyone found any way to circumvent this. I
> think this
> | a stupid concept. Just because a user is in the local Administrators
> group on a
> | server, doesn't mean he/she knows how to manage RS.
> |
> | Thanks in advance.
> |
>

Overhead of Multiple SQL Calls

Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
--
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>

Overhead of Multiple SQL Calls

Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>

Overhead of Multiple SQL Calls

Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)
Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>