Processing the TEXT datatype with TSQL

Hi;

I have a table with a TEXT datatype.
Its a comment field.

Right now the users who put in singlequotes are killing the web front
end.

The programmer responsible is fixing this issue but it might be a few
weeks until we get the patch.

I would like to write a trigger that whenever this field is updated it
will scan the text for single quotes ( and hard returns \r ) and
extract them.

I found some nice string functions in HELP.

Will these string functions work with the TEXT datatype in a TSQL
script/trigger?

Thanks in advance

SteveI handle quotes with the REPLACE function. All languages that I work with
has it.

Two single quotes in a row signify an escape sequence from the normal
interpretation of the single quote character. When two single quotes appear
together, they are interpreted by SQL as one literal single quote. All we
need do, then, is replace any single quote with two single quotes in strings
that we want interpreted literally by SQL.

This won't work on a Text datatype, however it does work on varchars and
stuff. Check your max len() on that field and see if it actually is using
more than the capacity of other datatypes and see about changing it to
varchar or something. This t-sql replaces one quote with two and would save
your web person endless hours of javascript'ing validation code!!

Select REPLACE(testColumn, char(39), char(39) + char(39)) as texta from
myTable

After all, quotes are valid characters too!!!

Good luck!

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Steve" <stevesusenet@.yahoo.com> wrote in message
news:6f8cb8c9.0311140657.59346a15@.posting.google.c om...
> Hi;
> I have a table with a TEXT datatype.
> Its a comment field.
> Right now the users who put in singlequotes are killing the web front
> end.
> The programmer responsible is fixing this issue but it might be a few
> weeks until we get the patch.
> I would like to write a trigger that whenever this field is updated it
> will scan the text for single quotes ( and hard returns \r ) and
> extract them.
> I found some nice string functions in HELP.
> Will these string functions work with the TEXT datatype in a TSQL
> script/trigger?
> Thanks in advance
> Steve

Processes locking themselves after SP4

Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall
Are you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI
|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:

> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>
|||Processes appearing to lock themselves is a change in reporting in SP4, not an actual issue. Basically the process reports itself as a blocking process when it goes to grab disk allocation (as I understand it). The Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on databases up to 400 GB in size.

Quote:

Originally posted by Dave Wall
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall

|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/...513ab281?hl=en
--
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/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall
|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
Dave Wall
"Kyle Quinby" wrote:

> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1756427.html
>

Processes locking themselves after SP4

Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
--
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
--
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en
--
--
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/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
> > *Hi All,
> >
> > We've recently rebuilt our failover cluster to Windows 2003 and SQL
> > Server
> > 200 SP4. Users are complaining that the system is running slowly,
> > looking at
> > the processes, the active processes appear to be blocking themselves.
> > The
> > configuration of SQL is identical to before and the hardware is the
> > same,
> > just the OS and the SP version of SQL has changed.
> >
> > Any ideas what might be causing the blocking? I've checked for lock
> > escalation (using profiler) and we're not experiencing it!
> >
> > --
> > Dave Wall *
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>

Processes locking themselves after SP4

Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking a
t
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:

> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group...
513ab281?hl=en
--
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/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started t
o
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:

> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>

Processed Date for a Cube

Hi

I want to be able to display to the users the date/time a cube was last processed. We have SSAS 2005 and presenting the cube through Excel 2003 (with the SSAS 9 OLAP driver).

Thanks

LastProcessed is a property on the object avaliable only through AMO. Dont think Excel will show you this property.

You can possible wirte a AMO code yourself and use Excel programming model to show it.

Another way is to create a dimension with a single memeber based on the column in the relational database where you would keep the current date and during processing you update this dimension to pickup a new value.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for the suggestions.

I created a table with the current date and use this.

Process with status SLEEPING.

Hi all,

We have a web application with multiple users connecting to SQL Server 2005 database.

Once the users start using the system, I could see in the Process Info from the activity monitor with a several process with status as sleeping and command as Awaiting Command.

I have few questions regarding this for which simple answers would help me ( I searched and couldnt understand from the pages that explained the process information )

My questions are,

1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?

2. Does these process affect the performance ?

3. Does killing these process would affect the application connectivity ?

4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?

5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?

Any help would be appreciated.

Thanks in advance,

DBLearner.

Hi DBLearner,

1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?

-- No

2. Does these process affect the performance ?

-- It will if you have left thousand connections open. Connections need resources.

3. Does killing these process would affect the application connectivity ?

-- You shouldn't have to go around killing these processes. I don't think it would affect. More work for you.

4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?

-- Web app doesnot close the connection after the work has been done.

5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?

-- Your web application needs to be configured or modified so that whenever it needs to do any work on SQL Server it does the following:

1. Opens a connection.

2. Do the SQL work e.g. execute a stored procedure or select some data back.

3. Close the connection.

regards

Jag

|||Most web applications use a "connection pool" for SQL connections. This is a normal operation. Leave it alone.

Openning and closing connections is very time consuming. You do NOT want to open and close several thousand connections per min.

|||

Thanks Jag & Tom. But what should I do to optimise my connections and applications ?

DBLearner

|||It depends on your web application. You will need to look and see what mechanisim it is using to access the SQL server.

|||

Agree with Tom, if it is using Connection pooling then the connections should be left alone.

But if there is no connection pooling present, then the web application should be closing there connection after the work has been completed.

regards

Jag

|||

Hi,

Actually I am having a same problem where there are quite a number of process with sleeping status in the database. And I noticed that all these processes are called by the crystal report (using store procedures to get the data from db). FYI, I am using VS.Net 2003 and crystal report 9.0. Does anyone have idea on the solution for the this problem? Thanks.

|||

As Jag and Tom say - if this is connection pooling then you are best to leave it alone.

What you are seeing is one sleeping process for each active pool connection. They are waiting, ready to respond quickly when needed (without logging in again). The only time you have a problem is if the number of these sleeping connections does not hold steady but instead rises dramatically (implying that either it is not pooling somewhere or the pooling is failing).

The pool of connections means that the application can be responsive, and if you start killing them then there will be a reduction in that responsiveness (and servicing requests is the database's function). Even worse if you manage to kill a connection just as it is activated to service a request you could cause all kinds of problems (hopefully just longer delays).

|||This is not a "problem", this is how it WORKS. As said before, leave it alone unless you are seeing hundreds of sleeping processes overloading your SQL server.

Generally it opens new pipes when other pipes are busy. So if you have 10 sleeping processes, it is because you NEEDED THEM sometime before. It should timeout the pipe and close them after 60 mins or some time of inactivity.

Crystal does have some options to change the min/max connections, but I would leave them the default unless you are seriously having a problem.

|||

I have the same problem but i don't have hundread of conn just about 40.

These 40 sleeping connections thought eat me about 90% from the CPU and reset users Session and they loose their products before ariving to the payment.

I did close all the recordsets and connections and still appear new sleeping connections.

Any suggestions?

i used for any recordset opened

rs.close

set rs = nothing

still make me these problems i don't know what to do anymore

Any help will be appreciated

thank you

Process with status SLEEPING.

Hi all,

We have a web application with multiple users connecting to SQL Server 2005 database.

Once the users start using the system, I could see in the Process Info from the activity monitor with a several process with status as sleeping and command as Awaiting Command.

I have few questions regarding this for which simple answers would help me ( I searched and couldnt understand from the pages that explained the process information )

My questions are,

1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?

2. Does these process affect the performance ?

3. Does killing these process would affect the application connectivity ?

4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?

5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?

Any help would be appreciated.

Thanks in advance,

DBLearner.

Hi DBLearner,

1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?

-- No

2. Does these process affect the performance ?

-- It will if you have left thousand connections open. Connections need resources.

3. Does killing these process would affect the application connectivity ?

-- You shouldn't have to go around killing these processes. I don't think it would affect. More work for you.

4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?

-- Web app doesnot close the connection after the work has been done.

5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?

-- Your web application needs to be configured or modified so that whenever it needs to do any work on SQL Server it does the following:

1. Opens a connection.

2. Do the SQL work e.g. execute a stored procedure or select some data back.

3. Close the connection.

regards

Jag

|||Most web applications use a "connection pool" for SQL connections. This is a normal operation. Leave it alone.

Openning and closing connections is very time consuming. You do NOT want to open and close several thousand connections per min.

|||

Thanks Jag & Tom. But what should I do to optimise my connections and applications ?

DBLearner

|||It depends on your web application. You will need to look and see what mechanisim it is using to access the SQL server.

|||

Agree with Tom, if it is using Connection pooling then the connections should be left alone.

But if there is no connection pooling present, then the web application should be closing there connection after the work has been completed.

regards

Jag

|||

Hi,

Actually I am having a same problem where there are quite a number of process with sleeping status in the database. And I noticed that all these processes are called by the crystal report (using store procedures to get the data from db). FYI, I am using VS.Net 2003 and crystal report 9.0. Does anyone have idea on the solution for the this problem? Thanks.

|||

As Jag and Tom say - if this is connection pooling then you are best to leave it alone.

What you are seeing is one sleeping process for each active pool connection. They are waiting, ready to respond quickly when needed (without logging in again). The only time you have a problem is if the number of these sleeping connections does not hold steady but instead rises dramatically (implying that either it is not pooling somewhere or the pooling is failing).

The pool of connections means that the application can be responsive, and if you start killing them then there will be a reduction in that responsiveness (and servicing requests is the database's function). Even worse if you manage to kill a connection just as it is activated to service a request you could cause all kinds of problems (hopefully just longer delays).

|||This is not a "problem", this is how it WORKS. As said before, leave it alone unless you are seeing hundreds of sleeping processes overloading your SQL server.

Generally it opens new pipes when other pipes are busy. So if you have 10 sleeping processes, it is because you NEEDED THEM sometime before. It should timeout the pipe and close them after 60 mins or some time of inactivity.

Crystal does have some options to change the min/max connections, but I would leave them the default unless you are seriously having a problem.

|||

I have the same problem but i don't have hundread of conn just about 40.

These 40 sleeping connections thought eat me about 90% from the CPU and reset users Session and they loose their products before ariving to the payment.

I did close all the recordsets and connections and still appear new sleeping connections.

Any suggestions?

i used for any recordset opened

rs.close

set rs = nothing

still make me these problems i don't know what to do anymore

Any help will be appreciated

thank you

Process Info: # of connnections

I have a SQLServer that gets accessed by some medical software through Citrix. So there are about 100 users at any point in time,but I can see 650+ Processes in Current Activity->Process Info in Enterprise Manager.

The company is complaining that if they dont reboot their SQL Server everyday it gets too slow to use. Does the # of Processes have any relation to their preformance issues?Doesn't anybody know?|||surely the number of active process (and so of active connection) will impact on performace...if a connection is open it is probably serving an application that is querying the server.

With the information you give, its quite impossibile to say that is SQL Server the cause of the slowdown or not.

You need to check your entire application, not only SQL Server. Is the application using stored procedure? someone has used cursors? Is the database split across multiple discs? Is the network functioning correctly?

There are so many possibile bottlenecks that you need - first of all - to figure what bottleneck you have, and then optimize.|||DBCC OPENTRAN ('<database_name>') will show you if there are any open transactions in a database. Their presence may be the reason that the server resources are being depleted.|||Thank you for the input.
aobut 90% or the 650 connections are sleeping, they could be unclosed ODBC connections. Will they have an impact on SQL performance?|||It is >possible< that you have a connection leak. If this is a third party product, I would think this sort of thing would have been detected by one of the first few customers. But then, I am silly that way. Can it cause a problem, yes. The amount of memory that SQL Server has to manage for connections will get a bit heavy (I think it is on the order of 80Kb per connection). Does that mean you should go on a SPID Chainsaw Massacre? Maybe not. Check on the last_batch (time the last query finished) values and compare to the Login_time values in sysprocesses (might be displayed in EM, but I am too lazy to check). If you see large discrepancies in these two times, then you might have to go on a killing spree. But before you do something you may regret, do some testing to see what the effect is going to be on your users and your support agreements with your vendor.

Process Info

Dear All,
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
Peter
Users have different ideas of what "being in an application"
means so it depends on what they meant. If they meant they
weren't actively using it, then the processes would still be
there if they are still connected. If they application was
actually closed then the processes will clean up eventually.
The processes remaining after closing an app could be due to
poor coding practices in the application, such as ado
references are not being cleaned up. If the issue is the 10
or more processes, that is also controlled by the
application. If it's an ADO app, then it probably wasn't
written to use the active connection - instead it keeps
creating new connections for whatever it needs to do.
It's not really going to hurt anything but it looks like
it's likely related to how the application was written.
-Sue
On Wed, 7 Apr 2004 08:17:28 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:

>Dear All,
>Under Managment/Current Activity/Process Info you can see
>all the users with connections to the system.
>Here is the problem. A couple of users seem to have
>multiple (10 or more) process ID's, all of them sleeping.
>After a chat with them they said they were not in the
>application, but the process was still being displayed.
>My questions are then, should I be concerned, and why
>aren't they closing automatically ?
>Thanks
>Peter

Process Info

I want to check who access the database in the sql server. When i go to current activity then process info. I found not all the users who access the database are displayed. Even though i refresh it. It didn't show up. But i am sure some user was accessing the database. Anybody know why, how can i avoid it. Thanks.Perhaps the user disconnected prior the current activity was shown? Or the user connected using a different username/password?|||I'd use SQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_2xiq.asp).

-PatP

Process Info

Dear All,
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
PeterUsers have different ideas of what "being in an application"
means so it depends on what they meant. If they meant they
weren't actively using it, then the processes would still be
there if they are still connected. If they application was
actually closed then the processes will clean up eventually.
The processes remaining after closing an app could be due to
poor coding practices in the application, such as ado
references are not being cleaned up. If the issue is the 10
or more processes, that is also controlled by the
application. If it's an ADO app, then it probably wasn't
written to use the active connection - instead it keeps
creating new connections for whatever it needs to do.
It's not really going to hurt anything but it looks like
it's likely related to how the application was written.
-Sue
On Wed, 7 Apr 2004 08:17:28 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:

>Dear All,
>Under Managment/Current Activity/Process Info you can see
>all the users with connections to the system.
>Here is the problem. A couple of users seem to have
>multiple (10 or more) process ID's, all of them sleeping.
>After a chat with them they said they were not in the
>application, but the process was still being displayed.
>My questions are then, should I be concerned, and why
>aren't they closing automatically ?
>Thanks
>Peter

Process Info

Dear All,
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
PeterUsers have different ideas of what "being in an application"
means so it depends on what they meant. If they meant they
weren't actively using it, then the processes would still be
there if they are still connected. If they application was
actually closed then the processes will clean up eventually.
The processes remaining after closing an app could be due to
poor coding practices in the application, such as ado
references are not being cleaned up. If the issue is the 10
or more processes, that is also controlled by the
application. If it's an ADO app, then it probably wasn't
written to use the active connection - instead it keeps
creating new connections for whatever it needs to do.
It's not really going to hurt anything but it looks like
it's likely related to how the application was written.
-Sue
On Wed, 7 Apr 2004 08:17:28 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>Dear All,
>Under Managment/Current Activity/Process Info you can see
>all the users with connections to the system.
>Here is the problem. A couple of users seem to have
>multiple (10 or more) process ID's, all of them sleeping.
>After a chat with them they said they were not in the
>application, but the process was still being displayed.
>My questions are then, should I be concerned, and why
>aren't they closing automatically ?
>Thanks
>Peter

procedure to send an email reminder help?

Hi guys,I hope somebody can help as I am trying to write a procedure in SQL that will be an email reminder sent to users on every 5 th or the month if they don't submit data before.

I am quite new to sql and procedures in particular so I was hoping somebody can help.

I need to:
1.Check the day of the month, if not 5th do nothing

2.Get the date of the prevoious month (get current date - 1 month,set day to 1)this is the funny bit.
I have a field DateEntered,but users only select the month and the year on the acctual page,but when submitted it gets written as a full date and defaults to the 1st of the month.

3.Get the list of hospitals that haven't got data for the previous month

4.Email the hospitals.

DECLARE @.returnDay int;
DECLARE @.DateEntered datetime;

SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 5 (syntax error near 5)
BEGIN
SELECT @.DateEntered = GetDate() - 30
Print DatePart(month, @.DateEntered)

I was hoping somebody could look at this and help me.

thanksYou don't have to get the fifth day of the month at all. When you set up your job to run your proc, just set it Monthly, every 5th day of the month.

You can the get the other date by:

SELECT @.datetime = DATEPART(MM,DATEADD(MM,-1,GETDATE()) + '/01/' + DATEPART(YY,GETDATE()

Then do what you need to do.|||skunked again, (job part)|||Yes, but I am getting this error "Syntax error converting datetime from character string"

I know there is a CONVERT function to do this but suprise,suprise I can't put it properly inside the formula