We have a SQL Advance server 2000 running SQL Server 2000 SP3 and has
Outlook 2002 installed. The SQL Agent runs under the account
ghs2000\sqlexec.
I logged into this machine as ghs2000\sqlexec and setup a POP3 account
on the machine. Tested sending emails and test mails from SQL Server
and they work fine.
Now we log into the machine as a different user, it's normally logged in
as ghs2000\madmin. And we leave Outlook running. But the emails from
SQL Server never get sent out. They won't get sent out unless we log
into the server as ghs2000\sqlexec and open Outlook 2002.
Any way to set this up so the messages are sent out regardless of who is
logged in? Is this a limitation in Outlook 2002 or would the same thing
happen in Outlook 2002?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi
This may be happening!
From http://support.microsoft.com/default.aspx?scid=kb;pl;263556
WARNING: If you attempt to use SQL Mail to establish a connection to a
POP3/SMTP mail server you MUST be able to repeatedly connect and send mail
by using the following steps:
1.. Click the icon that starts the mail client.
2.. Send a message to the same e-mail address listed in the profile so
that you can test both the send and receive capability.
3.. Exit the mail client.
4.. Repeat steps 1 through 3 several times to simulate expected e-mail
traffic.
NOTE: If at any time during this process any dialog boxes appear that
require a response (such as clicking OK to log on) or you are prompted to
enter a password, it will not work with SQL Mail. For an Internet mail
connection to work with SQL Mail, you must have 100% connectivity or SQL
Mail is not usable and you might have to restart your server to clear up the
problem.
The reason for this is that SQL Mail does not provide for retries when
accessing a POP3 account. If SQL Mail cannot connect to the POP3 server on
the first attempt, the Internet Mail Connector normally opens a dialog box
that prompts you to click OK to retry. It that should occur, SQL Mail, which
is running as part of the MSSQLServer service, never sees the dialog box and
stops responding at this point. You might have to stop the Mapisp32.exe
application or even restart your Windows NT computer to clear the problem.
You may be better off using SMTP? http://www.sqldev.net/xp/xpsmtp.htm
John
"Colin Colin" <ccole@.ghs.guthrie.org> wrote in message
news:OJzDUx9lDHA.3024@.tk2msftngp13.phx.gbl...
> We have a SQL Advance server 2000 running SQL Server 2000 SP3 and has
> Outlook 2002 installed. The SQL Agent runs under the account
> ghs2000\sqlexec.
> I logged into this machine as ghs2000\sqlexec and setup a POP3 account
> on the machine. Tested sending emails and test mails from SQL Server
> and they work fine.
> Now we log into the machine as a different user, it's normally logged in
> as ghs2000\madmin. And we leave Outlook running. But the emails from
> SQL Server never get sent out. They won't get sent out unless we log
> into the server as ghs2000\sqlexec and open Outlook 2002.
> Any way to set this up so the messages are sent out regardless of who is
> logged in? Is this a limitation in Outlook 2002 or would the same thing
> happen in Outlook 2002?
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||I have Outlook 2K setup for a POP3 acct to work with SQL 2K. Honestly it is
painful to have it works. First thing it doesn't work well if I used
Terminal service, so I have to drive all the way at the midnight to the data
center where we keep our servers to logon physically. Then it never work as
expected. Why? Because emails stucked in the Out folder. I have to logon
as the domain acct that starts up SQL services then the mails are sent out.
I do every thing from MS KB. It works on this server but the others.
Finally it works on all servers but I will never know what made SQL mail
failed at the first time. I follow all steps, one by one, per MS KBs. If
it fails I will just start over, recreate the email profile, set up SQL mail
then it will work after second or third time.
>>Any way to set this up so the messages are sent out regardless of who is
>>logged in?
SQL mail is supposed to work that way. It is the way SQL mails is working
on my SQL boxes now.
"Colin Colin" <ccole@.ghs.guthrie.org> wrote in message
news:OJzDUx9lDHA.3024@.tk2msftngp13.phx.gbl...
> We have a SQL Advance server 2000 running SQL Server 2000 SP3 and has
> Outlook 2002 installed. The SQL Agent runs under the account
> ghs2000\sqlexec.
> I logged into this machine as ghs2000\sqlexec and setup a POP3 account
> on the machine. Tested sending emails and test mails from SQL Server
> and they work fine.
> Now we log into the machine as a different user, it's normally logged in
> as ghs2000\madmin. And we leave Outlook running. But the emails from
> SQL Server never get sent out. They won't get sent out unless we log
> into the server as ghs2000\sqlexec and open Outlook 2002.
> Any way to set this up so the messages are sent out regardless of who is
> logged in? Is this a limitation in Outlook 2002 or would the same thing
> happen in Outlook 2002?
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sqlsql
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Sunday, March 25, 2012
Tuesday, March 20, 2012
Conditional Where wildcard problem
Hi,
I have a problem using the LIKE operator in a stored procedure. I have simplified the script so that it runs in query analyser and still have the same problem. The script is:
DECLARE @.FirstName varchar (50)
SELECT @.FirstName = 'B%'
SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE CASE @.FirstName WHEN '' THEN PhoneList.FirstName ELSE @.FirstName END
This code produces no rows in the result. However if I change the second line to:
SELECT @.FirstName = 'Ben'
Then I get all of the rows with 'Ben' as the first name. If I change it to:
SELECT @.FirstName = 'Be%'
Then I get all of the rows with three character first names beginning with 'Be'. If I change it to:
SELECT @.FirstName = 'B%%'
Then I get all of the three character first names beginning with 'B'.
I need the conditional where so that if an empty string is passed it returns every row, which works fine as it is.
The % wildcard appears to be operating the same way as the _ wildcard. Has anyone seen this before?
This is SQL Server 2k SP3 on Win2003 server.
thanks
BenHi,
maybe you could try this:
DECLARE @.FirstName varchar (50)
SELECT @.FirstName = 'B%'
SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE @.FirstName + '%'
If @.FIrstName is an empty string the statement should return all data.
;)
I have a problem using the LIKE operator in a stored procedure. I have simplified the script so that it runs in query analyser and still have the same problem. The script is:
DECLARE @.FirstName varchar (50)
SELECT @.FirstName = 'B%'
SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE CASE @.FirstName WHEN '' THEN PhoneList.FirstName ELSE @.FirstName END
This code produces no rows in the result. However if I change the second line to:
SELECT @.FirstName = 'Ben'
Then I get all of the rows with 'Ben' as the first name. If I change it to:
SELECT @.FirstName = 'Be%'
Then I get all of the rows with three character first names beginning with 'Be'. If I change it to:
SELECT @.FirstName = 'B%%'
Then I get all of the three character first names beginning with 'B'.
I need the conditional where so that if an empty string is passed it returns every row, which works fine as it is.
The % wildcard appears to be operating the same way as the _ wildcard. Has anyone seen this before?
This is SQL Server 2k SP3 on Win2003 server.
thanks
BenHi,
maybe you could try this:
DECLARE @.FirstName varchar (50)
SELECT @.FirstName = 'B%'
SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE @.FirstName + '%'
If @.FIrstName is an empty string the statement should return all data.
;)
Wednesday, March 7, 2012
Conditional Formatting - Export
Using the web viewer control in VS 2005, is there a way to conditionally format items for export only?
For example, user runs a report which gets displayed in the viewer. The report contains small images and or decorated text for links to additional reports. If user selects pdf from the export dropdown list and clicks export, I would like hide the images in the pdf and or format the decorated text differently.
If the answer is no, is it due to the report already being in its intermediate state?
The answer to your first question is no. One way of getting close to the desired behavior is to either use different report definitions or use a report parameter to determine if images should be shown etc.
The answer to the second question is yes. When you export the report to PDF, it will just re-render the report from the already processed (and output-format independent) intermediate format.
-- Robert
Sunday, February 19, 2012
Concurrent User limitation?
Hi folks -
I have an application that runs a SQL database. I am using the MSDE to
access it. Client machine has the proprietary software and MDAC v 2.8
installed on a Win2KPro workstation that is fully patched. The support team
for my proprietary application is telling me that using MSDE instead of SQL
Server will give me a concurrent user limit of around 10 or 11.
I've been running this application for almost a year and have not run into
this until I recently instituted Group Policy settings (although I don't see
where the changes to Group Policy would have a bearing).
Sometimes the workstations can access the application, other times within
the same day, they receive an error stating that "the server does not exist
or access is denied", yet they can restart and get right in.
Does anyone know if there IS a concurrent user limitation using MSDE to
access a SQL application?
Thanks.
Deb Landrum, AAM, AIT
Insurance Alliance, Houston TX
HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
On Tue, 6 Sep 2005 14:29:13 -0500, Deb Landrum wrote:
(snip)
>Does anyone know if there IS a concurrent user limitation using MSDE to
>access a SQL application?
Hi Deb,
Well - sort of.
There is no real limit to the concurrent user connections, but there is
a limit to the number of concurrent workloads. If a connected user
spends most time entering data on the form, and only a fraction of the
total time actually executing queries, you can serve quite a few users
without ever running into problems. On the other hand, one single user
can spawn multiple concurrent workloads at once (though it's not
trivial).
The limit on concurrent workloads is set to 8 workloads, but since SQL
Server needs 3 workloads for it's internal processes, there are 5 left
for the users. Once you exceed the workload limit, you won;t get any
errors, but the overall performance of all workloads will be reduced.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi,
Deb Landrum wrote:
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE
> to access it. Client machine has the proprietary software and MDAC v
> 2.8 installed on a Win2KPro workstation that is fully patched. The
> support team for my proprietary application is telling me that using
> MSDE instead of SQL Server will give me a concurrent user limit of
> around 10 or 11.
> I've been running this application for almost a year and have not run
> into this until I recently instituted Group Policy settings (although
> I don't see where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times
> within the same day, they receive an error stating that "the server
> does not exist or access is denied", yet they can restart and get
> right in.
> Does anyone know if there IS a concurrent user limitation using MSDE
> to access a SQL application?
there's no upper limit but 32767 connections to an MSDE instance but, a
built-in Workload Governor will kick in when 8 concurrent workloads of a
certain type are running... this will result in a linear delay applied to
all concurrent batches in the I/O activities.. when the number decreases
under the 8 limit, the governor will go sleeping again, waiting for next
"overflow" to kick in again... that does not mean 8 concurrent users is the
maximum allowed without the governor to kick in, as usually lot of
connections will be "sleeping", just waiting for user intervention to do
something... Microsoft released a paper "estimating" a magic number of 25
concurrent users before the overhead will be noticeable to end users, but
this just is a "magic number"... it really depend on the application type,
access mode, activities against the data, data load and so on... as you may
understand, there's no actual documentation about this governor but the
papers as
http://msdn.microsoft.com/library/?u...asp?frame=true
...
but I think this is another story, as the exception you get is not related
to all this matter...
this error is a generic MDAC related exception, as you can see reading
http://support.microsoft.com/default...06&Product=sql ,
and probably depending on problem in your LAN...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi
To add what Hugo and Andrea have commented on, there is a limitation at OS
level. You are running w2k Professional which is a workstation product.
Currently the workstation products have a limitation of 10 simultaneous
connections from other machines. In effect, on your platform, you are
limited to 10 connections to MSDE. This limitation is in place to ensure
that workstation products, which are not optimized to be application or
file share servers, are not used as servers.
If you install MSDE on a server product, this limitation goes away.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
> team
> for my proprietary application is telling me that using MSDE instead of
> SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
> see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
> exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||Thanks to all for your input - the MSDE is on a server product, and, the
resolution was actually an issue within the SQL application that we are
running. For whatever reason, although the server name was properly and
correctly shown for the workstations to access it, the problematic ones were
receiving the error. However, by substituting the IP address of the server
for the actual server name, those same workstations were once again able to
access the application.
I don't know if this would be a particular weakness in the proprietary
application that we're using or not, but, again, Thank You to all for
replying.
Deb
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
team
> for my proprietary application is telling me that using MSDE instead of
SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||hi Deb,
Deb Landrum wrote:
> Thanks to all for your input - the MSDE is on a server product, and,
> the resolution was actually an issue within the SQL application that
> we are running. For whatever reason, although the server name was
> properly and correctly shown for the workstations to access it, the
> problematic ones were receiving the error. However, by substituting
> the IP address of the server for the actual server name, those same
> workstations were once again able to access the application.
> I don't know if this would be a particular weakness in the proprietary
> application that we're using or not, but, again, Thank You to all for
> replying.
you probably have a Name resolution problem in your LAN... let your Net
admin gain his money :D:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
I have an application that runs a SQL database. I am using the MSDE to
access it. Client machine has the proprietary software and MDAC v 2.8
installed on a Win2KPro workstation that is fully patched. The support team
for my proprietary application is telling me that using MSDE instead of SQL
Server will give me a concurrent user limit of around 10 or 11.
I've been running this application for almost a year and have not run into
this until I recently instituted Group Policy settings (although I don't see
where the changes to Group Policy would have a bearing).
Sometimes the workstations can access the application, other times within
the same day, they receive an error stating that "the server does not exist
or access is denied", yet they can restart and get right in.
Does anyone know if there IS a concurrent user limitation using MSDE to
access a SQL application?
Thanks.
Deb Landrum, AAM, AIT
Insurance Alliance, Houston TX
HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
On Tue, 6 Sep 2005 14:29:13 -0500, Deb Landrum wrote:
(snip)
>Does anyone know if there IS a concurrent user limitation using MSDE to
>access a SQL application?
Hi Deb,
Well - sort of.
There is no real limit to the concurrent user connections, but there is
a limit to the number of concurrent workloads. If a connected user
spends most time entering data on the form, and only a fraction of the
total time actually executing queries, you can serve quite a few users
without ever running into problems. On the other hand, one single user
can spawn multiple concurrent workloads at once (though it's not
trivial).
The limit on concurrent workloads is set to 8 workloads, but since SQL
Server needs 3 workloads for it's internal processes, there are 5 left
for the users. Once you exceed the workload limit, you won;t get any
errors, but the overall performance of all workloads will be reduced.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi,
Deb Landrum wrote:
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE
> to access it. Client machine has the proprietary software and MDAC v
> 2.8 installed on a Win2KPro workstation that is fully patched. The
> support team for my proprietary application is telling me that using
> MSDE instead of SQL Server will give me a concurrent user limit of
> around 10 or 11.
> I've been running this application for almost a year and have not run
> into this until I recently instituted Group Policy settings (although
> I don't see where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times
> within the same day, they receive an error stating that "the server
> does not exist or access is denied", yet they can restart and get
> right in.
> Does anyone know if there IS a concurrent user limitation using MSDE
> to access a SQL application?
there's no upper limit but 32767 connections to an MSDE instance but, a
built-in Workload Governor will kick in when 8 concurrent workloads of a
certain type are running... this will result in a linear delay applied to
all concurrent batches in the I/O activities.. when the number decreases
under the 8 limit, the governor will go sleeping again, waiting for next
"overflow" to kick in again... that does not mean 8 concurrent users is the
maximum allowed without the governor to kick in, as usually lot of
connections will be "sleeping", just waiting for user intervention to do
something... Microsoft released a paper "estimating" a magic number of 25
concurrent users before the overhead will be noticeable to end users, but
this just is a "magic number"... it really depend on the application type,
access mode, activities against the data, data load and so on... as you may
understand, there's no actual documentation about this governor but the
papers as
http://msdn.microsoft.com/library/?u...asp?frame=true
...
but I think this is another story, as the exception you get is not related
to all this matter...
this error is a generic MDAC related exception, as you can see reading
http://support.microsoft.com/default...06&Product=sql ,
and probably depending on problem in your LAN...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi
To add what Hugo and Andrea have commented on, there is a limitation at OS
level. You are running w2k Professional which is a workstation product.
Currently the workstation products have a limitation of 10 simultaneous
connections from other machines. In effect, on your platform, you are
limited to 10 connections to MSDE. This limitation is in place to ensure
that workstation products, which are not optimized to be application or
file share servers, are not used as servers.
If you install MSDE on a server product, this limitation goes away.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
> team
> for my proprietary application is telling me that using MSDE instead of
> SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
> see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
> exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||Thanks to all for your input - the MSDE is on a server product, and, the
resolution was actually an issue within the SQL application that we are
running. For whatever reason, although the server name was properly and
correctly shown for the workstations to access it, the problematic ones were
receiving the error. However, by substituting the IP address of the server
for the actual server name, those same workstations were once again able to
access the application.
I don't know if this would be a particular weakness in the proprietary
application that we're using or not, but, again, Thank You to all for
replying.
Deb
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
team
> for my proprietary application is telling me that using MSDE instead of
SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||hi Deb,
Deb Landrum wrote:
> Thanks to all for your input - the MSDE is on a server product, and,
> the resolution was actually an issue within the SQL application that
> we are running. For whatever reason, although the server name was
> properly and correctly shown for the workstations to access it, the
> problematic ones were receiving the error. However, by substituting
> the IP address of the server for the actual server name, those same
> workstations were once again able to access the application.
> I don't know if this would be a particular weakness in the proprietary
> application that we're using or not, but, again, Thank You to all for
> replying.
you probably have a Name resolution problem in your LAN... let your Net
admin gain his money :D:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Concurrent stored procs?
Hi all
I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records.
To do testing we run that stored proc manually, also.
Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.
What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently.
(May be semapohres,mutex or something like that?)
(I am trying not to use a table to store whether the stored proc is running or not)
Thanks in advance.
RochanaOne can use sp_getapplock and sp_releaseapplock when invoking a sp.
Hans.|||Thanks Hans for quick reply.
I tried it but found some problems.
sp_getapplock needs an active transaction, without which it fails.
I am trying not to use Transactions because it slows down the system so badly.
Originally posted by HansVE
One can use sp_getapplock and sp_releaseapplock when invoking a sp.
Hans.|||Could use global temporary table as semaphore.
CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END
Hans.|||It works to some extent Hans.
The error checking never happens. It quits the process without displaying the error.
..
IF @.@.error <> 0
PRINT 'Proc already running'
...
Originally posted by HansVE
Could use global temporary table as semaphore.
CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END
Hans.|||Yes, the error is too serious to continue. If you cannot trap that error at the client, you could instead check for existence of the table object.
IF OBJECT_ID('tempdb.dbo.##proc_running') > 0|||sysprocesses holds info about all processes currently running on the server (master.sysprocesses)
dbcc inputbuffer spid tells you what a specific process is doing
The two combined (in some way ;)) should tell you if the proc is running already..
Hope it helps a little bit..|||I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)
dbcc inputbuffer spid tells you what a specific process is doing
The two combined (in some way ;)) should tell you if the proc is running already..
Hope it helps a little bit..
One problem with this is that two people could still start the proc simultaneously.
Hans.|||Enigma here is the scenario:
Say, the job is scheduled at 12noon everyday.
One of the bosses come and asks us to run that particular job becos they need to see the data on their screen. So one of the programmers of the team runs that job, or invoke the particular sp.
The sp takes about 10-15 mins to complete.
If it reaches 12noon while that particular sp is running, job kicks in and invoke that same sp again. (our sp is still running)
Thats why I want to check if the particular sp is already running or not, so that the sp wont run again.
Originally posted by Enigma
I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Thanks Jonte..
Your suggestion worked !! ;)
I have to check the Event Info for my stored proc, thats running in the server.
I have pasted code to test what processes are currently running on the server on a db. May be you can run and see the results too
Thanks
declare @.spid bigint
declare crsr cursor read_only
for
select spid from master..sysprocesses where dbid>1 and kpid>1
open crsr
fetch next from crsr
into @.spid
while @.@.fetch_status<>-1
begin
dbcc inputbuffer(@.spid)
fetch next from crsr
into @.spid
end
close crsr
deallocate crsr
Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)
dbcc inputbuffer spid tells you what a specific process is doing
The two combined (in some way ;)) should tell you if the proc is running already..
Hope it helps a little bit..
I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records.
To do testing we run that stored proc manually, also.
Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.
What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently.
(May be semapohres,mutex or something like that?)
(I am trying not to use a table to store whether the stored proc is running or not)
Thanks in advance.
RochanaOne can use sp_getapplock and sp_releaseapplock when invoking a sp.
Hans.|||Thanks Hans for quick reply.
I tried it but found some problems.
sp_getapplock needs an active transaction, without which it fails.
I am trying not to use Transactions because it slows down the system so badly.
Originally posted by HansVE
One can use sp_getapplock and sp_releaseapplock when invoking a sp.
Hans.|||Could use global temporary table as semaphore.
CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END
Hans.|||It works to some extent Hans.
The error checking never happens. It quits the process without displaying the error.
..
IF @.@.error <> 0
PRINT 'Proc already running'
...
Originally posted by HansVE
Could use global temporary table as semaphore.
CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END
Hans.|||Yes, the error is too serious to continue. If you cannot trap that error at the client, you could instead check for existence of the table object.
IF OBJECT_ID('tempdb.dbo.##proc_running') > 0|||sysprocesses holds info about all processes currently running on the server (master.sysprocesses)
dbcc inputbuffer spid tells you what a specific process is doing
The two combined (in some way ;)) should tell you if the proc is running already..
Hope it helps a little bit..|||I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)
dbcc inputbuffer spid tells you what a specific process is doing
The two combined (in some way ;)) should tell you if the proc is running already..
Hope it helps a little bit..
One problem with this is that two people could still start the proc simultaneously.
Hans.|||Enigma here is the scenario:
Say, the job is scheduled at 12noon everyday.
One of the bosses come and asks us to run that particular job becos they need to see the data on their screen. So one of the programmers of the team runs that job, or invoke the particular sp.
The sp takes about 10-15 mins to complete.
If it reaches 12noon while that particular sp is running, job kicks in and invoke that same sp again. (our sp is still running)
Thats why I want to check if the particular sp is already running or not, so that the sp wont run again.
Originally posted by Enigma
I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Thanks Jonte..
Your suggestion worked !! ;)
I have to check the Event Info for my stored proc, thats running in the server.
I have pasted code to test what processes are currently running on the server on a db. May be you can run and see the results too
Thanks
declare @.spid bigint
declare crsr cursor read_only
for
select spid from master..sysprocesses where dbid>1 and kpid>1
open crsr
fetch next from crsr
into @.spid
while @.@.fetch_status<>-1
begin
dbcc inputbuffer(@.spid)
fetch next from crsr
into @.spid
end
close crsr
deallocate crsr
Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)
dbcc inputbuffer spid tells you what a specific process is doing
The two combined (in some way ;)) should tell you if the proc is running already..
Hope it helps a little bit..
Subscribe to:
Posts (Atom)