BOL says you should not kill a SELECT statement. Why not?

BOL says this about the KILL command:
Use KILL very carefully, especially when critical processes are
running. You cannot kill your own process. Other processes not to kill
are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Those all make sense except for the SELECT but BOL doesn't elaborate on
why you shouldn't kill a SELECT statement. Any ideas why they caution
against this?
ThanksIt must be a mistake. They removed it from 2005 BOL.
<pshroads@.gmail.com> wrote in message
news:1154975355.227889.163170@.m79g2000cwm.googlegroups.com...
> BOL says this about the KILL command:
> Use KILL very carefully, especially when critical processes are
> running. You cannot kill your own process. Other processes not to kill
> are:
> AWAITING COMMAND
> CHECKPOINT SLEEP
> LAZY WRITER
> LOCK MONITOR
> SELECT
> SIGNAL HANDLER
> Those all make sense except for the SELECT but BOL doesn't elaborate on
> why you shouldn't kill a SELECT statement. Any ideas why they caution
> against this?
> Thanks
>

Bocking Issue

I have a spid that has a wait type of networkio, open
trans = 0, command = select, status is runnable, wait time
of around 70000000. application is IIS.
I'm assuming I can just kill this spid beceause it's
a "select" and not part of a transaction. Is there some
way to tell if it's safe to kill this?Did you do a DBCC INPUTBUFFER on that spid? That'll at least tell you what
exactly that spid is selecting, and you could determine, where that
connection is coming from.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"maryann" <anonymous@.discussions.microsoft.com> wrote in message
news:0bcf01c3d53c$b61fb070$a501280a@.phx.gbl...
I have a spid that has a wait type of networkio, open
trans = 0, command = select, status is runnable, wait time
of around 70000000. application is IIS.
I'm assuming I can just kill this spid beceause it's
a "select" and not part of a transaction. Is there some
way to tell if it's safe to kill this?

Blocking Telnet command for SQL Server Detection 1433/1434

Any ideas how I can I block telnet connections to SQL Server ports ?

The only way is to turn of TCP/IP connections for SQL Server on the server.

HTH, jens SUessmeyer.

http://www.sqlserver2005.de

blocking issue.

I have "scenario 2" blocking issue. the process is sleeping and open transac
tions is 1 or more.
However the command that was run was a prodedure that has "No Begin Transact
ion, or rollback or commit."
Any ideas?
I don't understand how the open transactions can be greater then zero.You are probably have SET IMPLICIT_TRANSACTIONS ON .
Andrew J. Kelly SQL MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:5F627C7E-59AD-4232-B80D-588E58B932C0@.microsoft.com...
> I have "scenario 2" blocking issue. the process is sleeping and open
transactions is 1 or more.
> However the command that was run was a prodedure that has "No Begin
Transaction, or rollback or commit."
> Any ideas?
> I don't understand how the open transactions can be greater then zero.|||I have not. Perhapes the default mode has been set in some configuration at
the server level. Is this possible?|||Yes, the default for a lot of drivers is to set implicit transactions on.
You can run profiler and make sure to include the Existing connections event
to see what gets set.
Andrew J. Kelly SQL MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A558E87B-FA4C-4FE4-B4D1-15D745B50F17@.microsoft.com...
> I have not. Perhapes the default mode has been set in some configuration
at the server level. Is this possible?

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
Mangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(http://msdn.microsoft.com/library/de...on_7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:

>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>
|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
http://msdn.microsoft.com/library/de...on_07_7d6b.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:

>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>
>

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
MangeshMangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(7a_8um1.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:

>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
7_7d6b.asp" target="_blank">http://msdn.microsoft.com/library/d... />
7_7d6b.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:

>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>
>

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
MangeshMangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--070605070403080308080508
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:
>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>
--070605070403080308080508
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A SELECT statement will hold a shared (S) lock on the specific
resource (generally a KEY or RID) and an intent shared (IS) lock on the
escalated resources (usually an IS(PAG) and IS(TAB)). The data
browsing window in SQLEM will hold locks open for big tables while
you're scrolling around through the data (just like MSAccess). If
SQLEM happens to have a shared lock on a page (PAG), which is entirely
possibly, and some other process requests an intent exclusive (IX) lock
on that same page (for example it wants to delete a row on the page, so
it will acquire an X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then
the IX(PAG) will be blocked by the S(PAG), because they're incompatible
lock types on the same resource, until the S)PAG) is release.<br>
<br>
See BOL for more into on lock compatibility:
(<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>)<br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>)<br>
<br>
The important thing to remember is that SQLEM is just another client
app running SELECT queries and issuing shared lock requests, sometimes
on pages of data at a time, and often doesn't release those locks (if
it's a bit enough table and you haven't reached the last page of it
yet) until you close the child window displaying that data (that issued
the SELECT statement).<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Mangesh Deshpande wrote:
<blockquote cite="mid5515F106-3E87-4255-9DD6-7CAA798E2526@.microsoft.com"
type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
</body>
</html>
--070605070403080308080508--|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have one user who uses SQL EM and run queries and modifies data.
> > We see that when the main blocking SPID is SQLEM and command is
> > "select"
> >
> > My question is
> > 1. How can select command from EM be a blocking spid.
> > 2. How can you cause blocling using EM?
> >
> > Please explain.
> >
> > Mangesh
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||This is a multi-part message in MIME format.
--060809080506050602090205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>>Mangesh Deshpande wrote:
>>
>>Hi
>>I have one user who uses SQL EM and run queries and modifies data.
>>We see that when the main blocking SPID is SQLEM and command is
>>"select"
>>My question is
>>1. How can select command from EM be a blocking spid.
>>2. How can you cause blocling using EM?
>>Please explain.
>>Mangesh
>>
>>I can't stress this enough when I say that you should never use SQL EM
>>for editing data, unless in development or off-hours.
>>SQL EM uses a server-side, firehose cursor which is good, but does not
>>fetch all data immediately (which is bad), like Query Analyzer or any
>>well-written application might. The benefit is that a large table can be
>>queried and viewed quickly, but most of the rows are sitting on the
>>server in the result set waiting for the user to scroll though the
>>results (at which point they are fetched). Until then, shared locks
>>remain on some pages on the table.
>>To avoid this, immediately issue a CTRL+END to move the end of the
>>results. That forces SQL EM to fetch all data, thereby releasing the
>>locks. If it's a large table, however, this process could be time
>>consuming.
>>
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>
>
--060809080506050602090205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A firehose cursor is essentially a forward-only, read-only cursor.
From BOL:<br>
</tt>
<blockquote><a>Rows are sent to the client in the order they are placed
in the result set, and the application must process the rows in this
order. After executing an SQL statement on a connection, the
application cannot do anything on the connection other than retrieve
the rows in the result set until all the rows have been retrieved. The
only other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.<br>
</a></blockquote>
<tt>See
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt><br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Hassan wrote:
<blockquote cite="midujrJPozZFHA.2420@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">What is a firehose cursor ?
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl">news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Mangesh Deshpande wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is
"select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
<pre wrap="">I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060809080506050602090205--

Blocked Process Command

Hi,
My system is suffering long time blocking by one process (Here for simple, I
name it Process A), I want to know what was blocked, but only saw
'sp_executesql;1' in the blocked process's (Process B) command window. And
there are lots of processes blocked by second process (Process B).
That is Porcess A is blocking Process B, and Process B is blocking many
other processes.
Please anyone help to express what could be the Process B.
Thanks in advance
FrankFrank
Run sp_who2 and take a look at BlkBy column
For more details please refer to the BOL.
"Frank" <wangping@.lucent.com> wrote in message
news:%23aThAQ6SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hi,
> My system is suffering long time blocking by one process (Here for simple,
I
> name it Process A), I want to know what was blocked, but only saw
> 'sp_executesql;1' in the blocked process's (Process B) command window. And
> there are lots of processes blocked by second process (Process B).
> That is Porcess A is blocking Process B, and Process B is blocking many
> other processes.
> Please anyone help to express what could be the Process B.
> Thanks in advance
> Frank
>|||Thanks Uri,
I will try on this, but I still wondering why the blocked process's command
is only 'sp_executesql;1'. And also wondering why it blocking the other
processes.
B/R
Frank
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eiofb46SFHA.160@.TK2MSFTNGP10.phx.gbl...
> Frank
> Run sp_who2 and take a look at BlkBy column
> For more details please refer to the BOL.
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:%23aThAQ6SFHA.2096@.TK2MSFTNGP14.phx.gbl...
simple,
> I
And
>|||Frank
http://www.sql-server-performance.com/blocking.asp
"Frank" <wangping@.lucent.com> wrote in message
news:eCcCRG7SFHA.3936@.TK2MSFTNGP15.phx.gbl...
> Thanks Uri,
> I will try on this, but I still wondering why the blocked process's
command
> is only 'sp_executesql;1'. And also wondering why it blocking the other
> processes.
> B/R
> Frank
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eiofb46SFHA.160@.TK2MSFTNGP10.phx.gbl...
> simple,
> And
many
>

Block a truncate command

Is there any way to block a truncate command on a table via a trigger or
something else in MS SQL Server 2000?
Thanks,
--
JasonHere's a quote from Books Online:
"TRUNCATE TABLE permissions default to the table owner, members of the
symin fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable."
Are you worried about the above users executing a TRUNCATE TABLE command?
Also note that this command cannot be run against a table that is referenced
by foreign key.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:9E2D92A7-C397-4538-9574-1133F9B37B33@.microsoft.com...
> Is there any way to block a truncate command on a table via a trigger or
> something else in MS SQL Server 2000?
> Thanks,
> --
> Jason|||If you don't want the table truncated, then don't issue the truncte in the
first place.
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:9E2D92A7-C397-4538-9574-1133F9B37B33@.microsoft.com...
> Is there any way to block a truncate command on a table via a trigger or
> something else in MS SQL Server 2000?
> Thanks,
> --
> Jason|||No.
Try better user/permissions management.
Do you have people that have been given symin or dbo privileges and are
going to go in and issue unauthorized TRUNCATE commands? I think you need
to fix that problem instead of trying to patch the symptom...
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:9E2D92A7-C397-4538-9574-1133F9B37B33@.microsoft.com...
> Is there any way to block a truncate command on a table via a trigger or
> something else in MS SQL Server 2000?
> Thanks,
> --
> Jason|||I want them to be able to truncate some tables, but not others.
--
Jason
"Aaron Bertrand [SQL Server MVP]" wrote:

> No.
> Try better user/permissions management.
> Do you have people that have been given symin or dbo privileges and are
> going to go in and issue unauthorized TRUNCATE commands? I think you need
> to fix that problem instead of trying to patch the symptom...
>
>
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:9E2D92A7-C397-4538-9574-1133F9B37B33@.microsoft.com...
>
>|||But only the table owner can truncate anyhow. Are you saying that these user
s act as dbo, the object
owner, db_owner or symin in your system?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:76E0D204-6026-4BDB-8B54-F384FF67556A@.microsoft.com...
>I want them to be able to truncate some tables, but not others.
> --
> Jason
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Blank space

Hi I am trying to remove the space between the words to make it one single
word.
ETK SETTLEM ESCROW ACCOUNT
I used this command: REPLACE(String,' ','')
Thank you for any helpHi,
This should work
declare @.s varchar(50)
Set @.s = 'ETK SETTLEM ESCROW ACCOUNT'
Set @.s = REPLACE(@.s,' ','')
select @.s
Maybe your syntax was incorrect
Thanks
Barry|||> I used this command: REPLACE(String,' ','')
And what happened? The syntax looks correct, but incomplete. Are you
trying to update the data in a table, or just select it without the spaces?
Give some more information other than "I did this"...

Blank password from command line installation

Hi, I need blank password for user "sa" when I instaling SQL 2005 Express from command line. SAPWD=<strong password> --> SAPWD=<blank password> ?

If you install SQL Express to use Windows Authentication (the deafult) you don't need to supply an SAPWD. (In fact the SA account is disabled in this case for SQL Express.) If you are installing using SQL Authentication you need to supply a strong password, I don't know of a way around this.

I can't think of any reason that I would ever let the SA user have a blank password. You might as well just send hackers a key to you house, the dates of your next vacation and a note telling them to have a good time while you're away. Security is provided for very good reasons, you should not try to defeat it in this way.

Regards,

Mike Wachal
SQL Express team

|||I agree you should never use a blank password, from memory you needed to supply a password for the sa account from sql 2000 sp3 onwards.

Black Windows when starting SQL Server Agent

Hello guys,
I just wanted to ask you a question regarding a funny
behavior on my server. Out of nowhere, when I start the
SQL Server Agent, a command window appears, with the path
to sqlagent.exe, but nothing else happens. The Agent runs
fine while that window is open, but when I close it, it
stops. This was not happening before. I wonder if the
agent is corrupted or something like that. I tried copying
the sqlagent.exe file from another computer, where this
behavior is not showing and it still happens. I wonder if
another file is corrupted.
Any comments and/or suggestions will be appreciated.
Thanks.What version / service pack of SQL Server are you running ..?
--
HTH
Ryan Waight, MCDBA, MCSE
"Francisco Elizondo" <drcoco@.softhome.net> wrote in message
news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
> Hello guys,
> I just wanted to ask you a question regarding a funny
> behavior on my server. Out of nowhere, when I start the
> SQL Server Agent, a command window appears, with the path
> to sqlagent.exe, but nothing else happens. The Agent runs
> fine while that window is open, but when I close it, it
> stops. This was not happening before. I wonder if the
> agent is corrupted or something like that. I tried copying
> the sqlagent.exe file from another computer, where this
> behavior is not showing and it still happens. I wonder if
> another file is corrupted.
> Any comments and/or suggestions will be appreciated.
> Thanks.|||I'm running SQL Server 2000, with Service Pack 3, but the
problem started before I applied SP3. I applied it to see
if that would fix the problem, but it didn't.
Thanks
>--Original Message--
>What version / service pack of SQL Server are you
running ..?
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Francisco Elizondo" <drcoco@.softhome.net> wrote in
message
>news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
>> Hello guys,
>> I just wanted to ask you a question regarding a funny
>> behavior on my server. Out of nowhere, when I start the
>> SQL Server Agent, a command window appears, with the
path
>> to sqlagent.exe, but nothing else happens. The Agent
runs
>> fine while that window is open, but when I close it, it
>> stops. This was not happening before. I wonder if the
>> agent is corrupted or something like that. I tried
copying
>> the sqlagent.exe file from another computer, where this
>> behavior is not showing and it still happens. I wonder
if
>> another file is corrupted.
>> Any comments and/or suggestions will be appreciated.
>> Thanks.
>
>.
>|||Francisco,
In the services configuration panel, open sqlserveragent service. In the
LogOn tab, uncheck "Allow service to interact with desktop" checkbox which
is available only when running the service undes localsystem account.
"Francisco Elizondo" <drcoco@.softhome.net> wrote in message
news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
> Hello guys,
> I just wanted to ask you a question regarding a funny
> behavior on my server. Out of nowhere, when I start the
> SQL Server Agent, a command window appears, with the path
> to sqlagent.exe, but nothing else happens. The Agent runs
> fine while that window is open, but when I close it, it
> stops. This was not happening before. I wonder if the
> agent is corrupted or something like that. I tried copying
> the sqlagent.exe file from another computer, where this
> behavior is not showing and it still happens. I wonder if
> another file is corrupted.
> Any comments and/or suggestions will be appreciated.
> Thanks.|||Are you seing any messages in the SQL Agents ErrorLog.
Right Click 'SQL Server Agent' and Display ErrorLog
--
HTH
Ryan Waight, MCDBA, MCSE
"Francisco Elizondo" <drcoco@.softhome.net> wrote in message
news:0b3e01c3a546$526fd3a0$a601280a@.phx.gbl...
> I'm running SQL Server 2000, with Service Pack 3, but the
> problem started before I applied SP3. I applied it to see
> if that would fix the problem, but it didn't.
> Thanks
> >--Original Message--
> >What version / service pack of SQL Server are you
> running ..?
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
> message
> >news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
> >> Hello guys,
> >>
> >> I just wanted to ask you a question regarding a funny
> >> behavior on my server. Out of nowhere, when I start the
> >> SQL Server Agent, a command window appears, with the
> path
> >> to sqlagent.exe, but nothing else happens. The Agent
> runs
> >> fine while that window is open, but when I close it, it
> >> stops. This was not happening before. I wonder if the
> >> agent is corrupted or something like that. I tried
> copying
> >> the sqlagent.exe file from another computer, where this
> >> behavior is not showing and it still happens. I wonder
> if
> >> another file is corrupted.
> >> Any comments and/or suggestions will be appreciated.
> >> Thanks.
> >
> >
> >.
> >|||No errors in the errorlog. Only loading information for
the service. The service runs fine, but I'm just concerned
about the command window not closing. If I close it, the
service stops.
Thanks.
>--Original Message--
>Are you seing any messages in the SQL Agents ErrorLog.
>Right Click 'SQL Server Agent' and Display ErrorLog
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Francisco Elizondo" <drcoco@.softhome.net> wrote in
message
>news:0b3e01c3a546$526fd3a0$a601280a@.phx.gbl...
>> I'm running SQL Server 2000, with Service Pack 3, but
the
>> problem started before I applied SP3. I applied it to
see
>> if that would fix the problem, but it didn't.
>> Thanks
>> >--Original Message--
>> >What version / service pack of SQL Server are you
>> running ..?
>> >
>> >--
>> >HTH
>> >Ryan Waight, MCDBA, MCSE
>> >
>> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
>> message
>> >news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
>> >> Hello guys,
>> >>
>> >> I just wanted to ask you a question regarding a funny
>> >> behavior on my server. Out of nowhere, when I start
the
>> >> SQL Server Agent, a command window appears, with the
>> path
>> >> to sqlagent.exe, but nothing else happens. The Agent
>> runs
>> >> fine while that window is open, but when I close it,
it
>> >> stops. This was not happening before. I wonder if the
>> >> agent is corrupted or something like that. I tried
>> copying
>> >> the sqlagent.exe file from another computer, where
this
>> >> behavior is not showing and it still happens. I
wonder
>> if
>> >> another file is corrupted.
>> >> Any comments and/or suggestions will be appreciated.
>> >> Thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||Did you check the other recommendation? The service config?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Francisco Elizondo" <drcoco@.softhome.net> wrote in message
news:0bcd01c3a54c$739f40a0$a601280a@.phx.gbl...
> No errors in the errorlog. Only loading information for
> the service. The service runs fine, but I'm just concerned
> about the command window not closing. If I close it, the
> service stops.
> Thanks.
> >--Original Message--
> >Are you seing any messages in the SQL Agents ErrorLog.
> >
> >Right Click 'SQL Server Agent' and Display ErrorLog
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
> message
> >news:0b3e01c3a546$526fd3a0$a601280a@.phx.gbl...
> >> I'm running SQL Server 2000, with Service Pack 3, but
> the
> >> problem started before I applied SP3. I applied it to
> see
> >> if that would fix the problem, but it didn't.
> >>
> >> Thanks
> >> >--Original Message--
> >> >What version / service pack of SQL Server are you
> >> running ..?
> >> >
> >> >--
> >> >HTH
> >> >Ryan Waight, MCDBA, MCSE
> >> >
> >> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
> >> message
> >> >news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
> >> >> Hello guys,
> >> >>
> >> >> I just wanted to ask you a question regarding a funny
> >> >> behavior on my server. Out of nowhere, when I start
> the
> >> >> SQL Server Agent, a command window appears, with the
> >> path
> >> >> to sqlagent.exe, but nothing else happens. The Agent
> >> runs
> >> >> fine while that window is open, but when I close it,
> it
> >> >> stops. This was not happening before. I wonder if the
> >> >> agent is corrupted or something like that. I tried
> >> copying
> >> >> the sqlagent.exe file from another computer, where
> this
> >> >> behavior is not showing and it still happens. I
> wonder
> >> if
> >> >> another file is corrupted.
> >> >> Any comments and/or suggestions will be appreciated.
> >> >> Thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Well, it seems like that did the trick. Thanks a lot for
all the help. I will keep you updated if anything else
happens. Sorry to bother you if my dumb questions. :-D
Francisco
>--Original Message--
>Francisco,
>In the services configuration panel, open sqlserveragent
service. In the
>LogOn tab, uncheck "Allow service to interact with
desktop" checkbox which
>is available only when running the service undes
localsystem account.
>"Francisco Elizondo" <drcoco@.softhome.net> wrote in
message
>news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
>> Hello guys,
>> I just wanted to ask you a question regarding a funny
>> behavior on my server. Out of nowhere, when I start the
>> SQL Server Agent, a command window appears, with the
path
>> to sqlagent.exe, but nothing else happens. The Agent
runs
>> fine while that window is open, but when I close it, it
>> stops. This was not happening before. I wonder if the
>> agent is corrupted or something like that. I tried
copying
>> the sqlagent.exe file from another computer, where this
>> behavior is not showing and it still happens. I wonder
if
>> another file is corrupted.
>> Any comments and/or suggestions will be appreciated.
>> Thanks.
>
>.
>|||Yeps, tried the service config, and it did work. This is
the first time something like this happened and I was
scared that it had to do with the sql server install.
Thanks again for all your help.
>--Original Message--
>Did you check the other recommendation? The service
config?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Francisco Elizondo" <drcoco@.softhome.net> wrote in
message
>news:0bcd01c3a54c$739f40a0$a601280a@.phx.gbl...
>> No errors in the errorlog. Only loading information for
>> the service. The service runs fine, but I'm just
concerned
>> about the command window not closing. If I close it, the
>> service stops.
>> Thanks.
>> >--Original Message--
>> >Are you seing any messages in the SQL Agents ErrorLog.
>> >
>> >Right Click 'SQL Server Agent' and Display ErrorLog
>> >
>> >--
>> >HTH
>> >Ryan Waight, MCDBA, MCSE
>> >
>> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
>> message
>> >news:0b3e01c3a546$526fd3a0$a601280a@.phx.gbl...
>> >> I'm running SQL Server 2000, with Service Pack 3, but
>> the
>> >> problem started before I applied SP3. I applied it to
>> see
>> >> if that would fix the problem, but it didn't.
>> >>
>> >> Thanks
>> >> >--Original Message--
>> >> >What version / service pack of SQL Server are you
>> >> running ..?
>> >> >
>> >> >--
>> >> >HTH
>> >> >Ryan Waight, MCDBA, MCSE
>> >> >
>> >> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
>> >> message
>> >> >news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
>> >> >> Hello guys,
>> >> >>
>> >> >> I just wanted to ask you a question regarding a
funny
>> >> >> behavior on my server. Out of nowhere, when I
start
>> the
>> >> >> SQL Server Agent, a command window appears, with
the
>> >> path
>> >> >> to sqlagent.exe, but nothing else happens. The
Agent
>> >> runs
>> >> >> fine while that window is open, but when I close
it,
>> it
>> >> >> stops. This was not happening before. I wonder if
the
>> >> >> agent is corrupted or something like that. I tried
>> >> copying
>> >> >> the sqlagent.exe file from another computer, where
>> this
>> >> >> behavior is not showing and it still happens. I
>> wonder
>> >> if
>> >> >> another file is corrupted.
>> >> >> Any comments and/or suggestions will be
appreciated.
>> >> >> Thanks.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>