I need to write an UPDATE using a SET where I fill in a NULL if a default
field is blank. Like the below:
UPDATE table SET birthdate = { expression | default | null }
I simply don't know the correct syntax (even after reading the online
books). I really need a working sample with anything close to this. I
can't get it to work and end-up with the birthdate pulling from a text
field and only placing a null in the table when no birthday is given.
Thanks in advance.
BobbyJI thnk you want COALESCE ( expression [ ,...n ] ), it returns the first non-null argument or Null if all argumetns are null.|||Originally posted by BobbyJ
I need to write an UPDATE using a SET where I fill in a NULL if a default
field is blank. Like the below:
UPDATE table SET birthdate = { expression | default | null }
I simply don't know the correct syntax (even after reading the online
books). I really need a working sample with anything close to this. I
can't get it to work and end-up with the birthdate pulling from a text
field and only placing a null in the table when no birthday is given.
Thanks in advance.
BobbyJ
Hi
Try
UPDATE table
set birthdate = isnull(yourtextitem,' ')|||All-
Thanks for the replies. I will try it out this weekend.|||I tried this from a prompt and got the same results (1/1/1900).
Any other ideas?|||Which one did you try? For sure walshx's will not work. Inserting a '' value into a date/time field results in the '1/1/1900' that you see. I thought Paul's suggestion would work, but I get the following error when doing a test:
None of the result expressions in a CASE specification can be NULL.
The test code I ran was:
declare @.temp varchar(20)
select @.temp = coalesce(null,null,null)
select @.temp as results
I even tried it with ansi_warnings off with no luck. I ran into a similar issue with a vb script i was running. My solution (bad as it is) was to append an update script that searched for 1/1/1900 and changed those entries to null.
I am sorry, but I don't have any good answers right now.
Hugh Scott
Originally posted by BobbyJ
I tried this from a prompt and got the same results (1/1/1900).
Any other ideas?|||It's funny you should mention that. I was thinking the exact same thing
before I started doing any of this. I figured I could write a server service
to periodically check the table and replace empty values or 1/1/1900 with nulls. I'll do this for now and keep searching for a simpler way as
I go. Thanks.
BobbbyJ|||1.select isnull(isnull(A,B),null) works
2.Look at this code. Periodic running of code is not needed.
create table XXXX
(
idX int identity(1,1) primary key
,X int null
)
GO
create trigger ti_XXXX_I on XXXX
instead of insert as
insert XXXX(X)
select isnull(inserted.X,0) from inserted
GO
create trigger ti_XXXX_U on XXXX
instead of update as
update t set
t.X=isnull(i.X,0)
from XXXX t
join inserted i on t.idx=i.idx
GO
insert XXXX values(NULL)
insert XXXX values(3)
select * from XXXX
update XXXX set X=NULL where X=3
select * from XXXX
GO
drop table XXXX
GO|||I'm sorry, I forgot to mention I'm doing this all within Visual Studio .NET (VB) and SQL2000 standard calls. I'm not too familiar with straight SQL
without referring to a book. Can I save a null in an
UPDATE table column = value and if value is empty save it as a null?
BobbyJ|||Empty means NULL is SQL.|||I'm referring to empty as VB sees a textbox with nothing typed in it or
where the length is 0 bytes.
All the coding I've done always places a 1/1/1900 in SQL. I don't have
the code in front of me (it's at work but looks like this - from memory).
UPDATE tblEMPLOYEE SET BIRTHDATE = ISNULL(txtBIRTH.text,'') WHERE EMPLOYEEID = form.EMPLOYEEID
There are more fields being update, I just selected one for this sample
in VB coding. The second half of the ISNULL I even replaced with
system.dbnull.value and get the same result. Can't figure out what's
wrong. I suppose VB never makes it a null as SQL needs to see it (just
an empty string coming in - at times).
BobbyJ|||I am not familiar with VB(.NET). In VB(6) Textbox property Text cannot store NULL values. If you want to use NULL, try variable for example Text1IsNull as boolean or Textbox.BackColor indication.|||I follow you (I think). Is this what you're saying:
Example:
Dim xBIRTHDATE as string (strings can be null if I recall)
'Birthdate is a textbox on a webform
if BIRTHDATE.TEXT <> STRING.EMPTY then
xBIRTHDATE = BIRTHDATE.TEXT
endif
'So at this point if the textbox is empty xBIRTHDATE is still set to null
'and it is safe to save.
UPDATE table SET dbBIRTHDATE = ISNULL(xBIRTHDATE,'')
Correct??|||bobbyj, can you have a look at the table definition please
if the birthdate column is defined NOT NULL you will never get a null in there
alternatively, it may have DEFAULT 0 which would explain the 1/1/1900 (this is the date that a day number of 0 converts to)
so before you write any weird script, check whether the database will even let you put a null in there
as for the syntax, try this --
script logic to generate update statement:
update table
set foo ='bar'
if birthdate form field is empty
, birthdate = null
else
, birthdate = form field value
endif|||Yes. It does allow nulls and I'll give that script a shot (maybe later tonight).
Thanks,|||I think I've figured out what's wrong. In Visual Studio .NET VB, it doesn't
set variables to NULL but something called NOTHING. When NOTHING
is passed to ISNULL, ISNULL thinks it's an empty string and not a NULL.
Do I need to declare my VS.NET variables as SQLTYPES in order to get
a true NULL? At first I thought this was a simple SQL issue but starting to
think otherwise.
BobbyJ|||Try
IIF(YourVar is nothing,"NULL","'+replace(YourVar,"'","''")+'")
for string variables to pass variable to sql.|||I will try this later today (I'm actually off today - after the SuperBowl)
when I remote in to check mail. We had system problems from the
worm virus that started out last Saturday (so hopefully the systems
are available - SQL).
Thanks,
BobbyJ|||I tried it but my compiler has issues with the syntax of the replace
statement. It's getting hung up on the single ' marks (thinks it's a comment of sorts). I really appreciate you and other taking time to
help with this. It's GREATLY appreciated.
BobbyJ|||Corrected in VB6, I dont know if this syntax can be used in .NET.
IIf(YourVar Is Nothing, "NULL", "'" + Replace(YourVar, "'", "''") + "'")|||Thanks. I'll try again.|||No matter what happens, the YOURVAR is always returned as NOTHING
and not NULL. It must be an issue with .NET. Your logic looks fine as did
my old code but I can't get a NULL for a return value. I think I need to
do some research on how to obtain a NULL value in the .NET. I suppose
I may have to look deeper into the SQLTYPES as I know there should be
a DBNULL.VALUE I can load into SQL in order to get a NULL in the database. This whole thing is really strange.
BobbyJ|||SOLVED! Code I used:
Dim strRANKDATE As String
If Me.txtGradeDate.Text <> String.Empty Then
strRANKDATE = "RANK = '" & Me.txtGradeDate.Text & "', "
Else
strRANKDATE = "RANK = NULL ,"
End If
'Building SQL string
strUpdateStatement = "UPDATE tblEMPLOYEE SET " & _
"FIRSTNAME = '" & Me.txtFirstName.Text & "', " & _
"MIDDLENAME = '" & Me.txtMiddle.Text & "', " & _
"LASTNAME = '" & Me.txtLastName.Text & "', " & _
"SUFFIX = '" & Me.ddlSuffix.SelectedItem.Text & "', " & _
"NICKNAME = '" & Me.txtNickname.Text & "', " & _
"SERVICE = '" & Me.ddlService.SelectedItem.Text & "', " & _
"GRADE = '" & Me.ddlGrade.SelectedItem.Text & "', " & _
strRANKDATE & _
"RANK = '" & Me.ddlRankTitle.SelectedItem.Text & "', " & _
etc. Now the NULL is properly added to the table when the user
removes the date from date fields on the webform. The If else
can be modied to a shorter IIF or a function can be made of it.
BobbyJ
Showing posts with label sample. Show all posts
Showing posts with label sample. Show all posts
Saturday, February 25, 2012
Friday, February 10, 2012
Can not kill a process
When I try to stop a process which running command 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
output window shows that this process is 'KILLED/ROLLBACK'. It looks like it
is continue updating statistics.
Why 'Cancel Query' command in the query analyser works, but kill command
does not wok?
How can I stop this process?
Thanks,
LijunHi,
Kill command is always safe and will rollback all the transaction.
Thanks
Hari
MCDBA
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:e3jW7Iq6DHA.3648@.TK2MSFTNGP11.phx.gbl...
> When I try to stop a process which running command 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
> output window shows that this process is 'KILLED/ROLLBACK'. It looks like
it
> is continue updating statistics.
> Why 'Cancel Query' command in the query analyser works, but kill command
> does not wok?
> How can I stop this process?
> Thanks,
> Lijun
>|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information, when you run 'UPDATE STATISTICS table_name WITH
SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
that the process is 'KILLED/ROLLBACK'. But you think you might not seen
this process when running sp_who2, right?
Is this problem always happen? Could you provide the information which
edition of SQL Server you are using and could you please provide me the
table definition by right-click the table and choose 'all task' and choose
'genterate SQL script'? Or just by running
use database_name
go
exec sp_help table_name
Waiting for you reply and thank you for you post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can reproduce this problem regardless which table I use. Our server
edition is:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:9yJvtc56DHA.2164@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> From you information, when you run 'UPDATE STATISTICS table_name WITH
> SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
> that the process is 'KILLED/ROLLBACK'. But you think you might not seen
> this process when running sp_who2, right?
> Is this problem always happen? Could you provide the information which
> edition of SQL Server you are using and could you please provide me the
> table definition by right-click the table and choose 'all task' and choose
> 'genterate SQL script'? Or just by running
> use database_name
> go
> exec sp_help table_name
> Waiting for you reply and thank you for you post.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL statement
will be a transaction without explicitly claim. If it will take many
actions within this transaction within it, when killed this process by
'KILL SPID' it will take some time to rollback all the actions it has
taken. So, from the sp_who2, you will notice that the status is
'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
press 'Cancel Query Execution' button, it will also be stoped and be rolled
back. For the rolled back process, it will take time depends on what it has
done before it is killed/stopped when it begin. So, in Query Analyzer and
for 'Kill', both will work and stop the 'update statistics' and rolled it
back.
Hope this answered you questions and if you still have questions, please
feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I don't think it is really ROLLBACK. For example, if UPDATE STATISTICS WITH
FULLSCAN on a table takes one hour, pressing 'Cancel Query Excution' button
will stop the process immediately. But if you using KILL command, even you
just started the UPDATE STATISTICS two second ago, it will continue run
until the procees is done. If you issues two UPDATE STATISTICS on a single
batch, it will stop until both table is done. You can check the result with
STATS_DATE, and it shows that both table's statistics have been updated.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:jueBFVK7DHA.808@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL
statement
> will be a transaction without explicitly claim. If it will take many
> actions within this transaction within it, when killed this process by
> 'KILL SPID' it will take some time to rollback all the actions it has
> taken. So, from the sp_who2, you will notice that the status is
> 'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
> press 'Cancel Query Execution' button, it will also be stoped and be
rolled
> back. For the rolled back process, it will take time depends on what it
has
> done before it is killed/stopped when it begin. So, in Query Analyzer and
> for 'Kill', both will work and stop the 'update statistics' and rolled it
> back.
> Hope this answered you questions and if you still have questions, please
> feel free to post new message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||U can also use
kill spid with statusonly to tell U how much percentage rollback is left . . .|||Hi Lijun,
Thank you for your update and it is my pleasure to help you with you issue.
I need to make some explanation here. When a transaction is terminated
before it completed, it must be roll back. Also, when a transaction is
cancelled, the rollback time will not based on how long this transation it
has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
press' Cancel Query Execution', the transaction is terminated within the
same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel Query
Execution' button, is the 'UPDATE STATISTICS' rolled back'
When you kill a SPID, a kill command is sent to the SPID, that is, one
thread will send a KILL command to another thread, it is not guaranteed
that the SPID is killed. A spid may respond to the KILL command
immediately, or after a delay, or not at all.
Please refer to the following KB:
INF: Understanding How the Transact-SQL KILL Command Works
http://support.microsoft.com/?id=171224
When you kill a SPID running 'UPDATE STATISTICS, could you check the status
by DBCC PSS? What the status of it? When you restart the SQL Server Service
and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about the
'Updated' time?
I am looking forward to your reply. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for you reply. I suggest that you try to reproduce the scenario by
yourself (It is not difficult to do). By the way, the article you motioned
is apply to SQL Server 6.0 and 6.5.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:IkW$Pnt7DHA.568@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for your update and it is my pleasure to help you with you
issue.
> I need to make some explanation here. When a transaction is terminated
> before it completed, it must be roll back. Also, when a transaction is
> cancelled, the rollback time will not based on how long this transation it
> has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
> press' Cancel Query Execution', the transaction is terminated within the
> same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
> that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel
Query
> Execution' button, is the 'UPDATE STATISTICS' rolled back'
> When you kill a SPID, a kill command is sent to the SPID, that is, one
> thread will send a KILL command to another thread, it is not guaranteed
> that the SPID is killed. A spid may respond to the KILL command
> immediately, or after a delay, or not at all.
> Please refer to the following KB:
> INF: Understanding How the Transact-SQL KILL Command Works
> http://support.microsoft.com/?id=171224
> When you kill a SPID running 'UPDATE STATISTICS, could you check the
status
> by DBCC PSS? What the status of it? When you restart the SQL Server
Service
> and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about
the
> 'Updated' time?
> I am looking forward to your reply. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you very much for your update!
Yes, I create a huge database and tested what you have done. Yes, you are
right that when using the KILL to kill the SPID running 'UPDATE
STATISTICS', although SP_WHO2 showed the command is KILLED/ROLLBACK and I
could even got the information indicating that it is rolled back 100% and
estimated time for roll back is 1 second. However, the 'UPDATE STATISTICS'
is not stopped by the KILL command.
I tried to figure it out and it really took me some time. Later, I
discussed it with my senior engineers in my team and I got the some
information of it. It is a known issue of SQL Server. I should appologize
that in my last reply to your question, I did not provide you the right
answer, and I did not get the information of it in time. Could I provide
some more assistance this time. I mean if you have any bussiness impact
from it, please feel free to post here and I would try my best to help you!
Thank you very much.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Can you please share with us what the reason for this problem is and how it can be solved?|||Hi Lijun,
Thank you for your update. Our development team is aware of it and is
working on that, I cannot guarantee anything of it. I will update you as
soon as possilbe for the progress of it. Thanks for you understanding.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
output window shows that this process is 'KILLED/ROLLBACK'. It looks like it
is continue updating statistics.
Why 'Cancel Query' command in the query analyser works, but kill command
does not wok?
How can I stop this process?
Thanks,
LijunHi,
Kill command is always safe and will rollback all the transaction.
Thanks
Hari
MCDBA
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:e3jW7Iq6DHA.3648@.TK2MSFTNGP11.phx.gbl...
> When I try to stop a process which running command 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
> output window shows that this process is 'KILLED/ROLLBACK'. It looks like
it
> is continue updating statistics.
> Why 'Cancel Query' command in the query analyser works, but kill command
> does not wok?
> How can I stop this process?
> Thanks,
> Lijun
>|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information, when you run 'UPDATE STATISTICS table_name WITH
SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
that the process is 'KILLED/ROLLBACK'. But you think you might not seen
this process when running sp_who2, right?
Is this problem always happen? Could you provide the information which
edition of SQL Server you are using and could you please provide me the
table definition by right-click the table and choose 'all task' and choose
'genterate SQL script'? Or just by running
use database_name
go
exec sp_help table_name
Waiting for you reply and thank you for you post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can reproduce this problem regardless which table I use. Our server
edition is:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:9yJvtc56DHA.2164@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> From you information, when you run 'UPDATE STATISTICS table_name WITH
> SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
> that the process is 'KILLED/ROLLBACK'. But you think you might not seen
> this process when running sp_who2, right?
> Is this problem always happen? Could you provide the information which
> edition of SQL Server you are using and could you please provide me the
> table definition by right-click the table and choose 'all task' and choose
> 'genterate SQL script'? Or just by running
> use database_name
> go
> exec sp_help table_name
> Waiting for you reply and thank you for you post.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL statement
will be a transaction without explicitly claim. If it will take many
actions within this transaction within it, when killed this process by
'KILL SPID' it will take some time to rollback all the actions it has
taken. So, from the sp_who2, you will notice that the status is
'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
press 'Cancel Query Execution' button, it will also be stoped and be rolled
back. For the rolled back process, it will take time depends on what it has
done before it is killed/stopped when it begin. So, in Query Analyzer and
for 'Kill', both will work and stop the 'update statistics' and rolled it
back.
Hope this answered you questions and if you still have questions, please
feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I don't think it is really ROLLBACK. For example, if UPDATE STATISTICS WITH
FULLSCAN on a table takes one hour, pressing 'Cancel Query Excution' button
will stop the process immediately. But if you using KILL command, even you
just started the UPDATE STATISTICS two second ago, it will continue run
until the procees is done. If you issues two UPDATE STATISTICS on a single
batch, it will stop until both table is done. You can check the result with
STATS_DATE, and it shows that both table's statistics have been updated.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:jueBFVK7DHA.808@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL
statement
> will be a transaction without explicitly claim. If it will take many
> actions within this transaction within it, when killed this process by
> 'KILL SPID' it will take some time to rollback all the actions it has
> taken. So, from the sp_who2, you will notice that the status is
> 'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
> press 'Cancel Query Execution' button, it will also be stoped and be
rolled
> back. For the rolled back process, it will take time depends on what it
has
> done before it is killed/stopped when it begin. So, in Query Analyzer and
> for 'Kill', both will work and stop the 'update statistics' and rolled it
> back.
> Hope this answered you questions and if you still have questions, please
> feel free to post new message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||U can also use
kill spid with statusonly to tell U how much percentage rollback is left . . .|||Hi Lijun,
Thank you for your update and it is my pleasure to help you with you issue.
I need to make some explanation here. When a transaction is terminated
before it completed, it must be roll back. Also, when a transaction is
cancelled, the rollback time will not based on how long this transation it
has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
press' Cancel Query Execution', the transaction is terminated within the
same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel Query
Execution' button, is the 'UPDATE STATISTICS' rolled back'
When you kill a SPID, a kill command is sent to the SPID, that is, one
thread will send a KILL command to another thread, it is not guaranteed
that the SPID is killed. A spid may respond to the KILL command
immediately, or after a delay, or not at all.
Please refer to the following KB:
INF: Understanding How the Transact-SQL KILL Command Works
http://support.microsoft.com/?id=171224
When you kill a SPID running 'UPDATE STATISTICS, could you check the status
by DBCC PSS? What the status of it? When you restart the SQL Server Service
and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about the
'Updated' time?
I am looking forward to your reply. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for you reply. I suggest that you try to reproduce the scenario by
yourself (It is not difficult to do). By the way, the article you motioned
is apply to SQL Server 6.0 and 6.5.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:IkW$Pnt7DHA.568@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for your update and it is my pleasure to help you with you
issue.
> I need to make some explanation here. When a transaction is terminated
> before it completed, it must be roll back. Also, when a transaction is
> cancelled, the rollback time will not based on how long this transation it
> has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
> press' Cancel Query Execution', the transaction is terminated within the
> same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
> that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel
Query
> Execution' button, is the 'UPDATE STATISTICS' rolled back'
> When you kill a SPID, a kill command is sent to the SPID, that is, one
> thread will send a KILL command to another thread, it is not guaranteed
> that the SPID is killed. A spid may respond to the KILL command
> immediately, or after a delay, or not at all.
> Please refer to the following KB:
> INF: Understanding How the Transact-SQL KILL Command Works
> http://support.microsoft.com/?id=171224
> When you kill a SPID running 'UPDATE STATISTICS, could you check the
status
> by DBCC PSS? What the status of it? When you restart the SQL Server
Service
> and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about
the
> 'Updated' time?
> I am looking forward to your reply. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you very much for your update!
Yes, I create a huge database and tested what you have done. Yes, you are
right that when using the KILL to kill the SPID running 'UPDATE
STATISTICS', although SP_WHO2 showed the command is KILLED/ROLLBACK and I
could even got the information indicating that it is rolled back 100% and
estimated time for roll back is 1 second. However, the 'UPDATE STATISTICS'
is not stopped by the KILL command.
I tried to figure it out and it really took me some time. Later, I
discussed it with my senior engineers in my team and I got the some
information of it. It is a known issue of SQL Server. I should appologize
that in my last reply to your question, I did not provide you the right
answer, and I did not get the information of it in time. Could I provide
some more assistance this time. I mean if you have any bussiness impact
from it, please feel free to post here and I would try my best to help you!
Thank you very much.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Can you please share with us what the reason for this problem is and how it can be solved?|||Hi Lijun,
Thank you for your update. Our development team is aware of it and is
working on that, I cannot guarantee anything of it. I will update you as
soon as possilbe for the progress of it. Thanks for you understanding.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Can not kill a process
When I try to stop a process which running command 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
output window shows that this process is 'KILLED/ROLLBACK'. It looks like it
is continue updating statistics.
Why 'Cancel Query' command in the query analyser works, but kill command
does not wok?
How can I stop this process?
Thanks,
LijunHi,
Kill command is always safe and will rollback all the transaction.
Thanks
Hari
MCDBA
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:e3jW7Iq6DHA.3648@.TK2MSFTNGP11.phx.gbl...
it
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information, when you run 'UPDATE STATISTICS table_name WITH
SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
that the process is 'KILLED/ROLLBACK'. But you think you might not seen
this process when running sp_who2, right?
Is this problem always happen? Could you provide the information which
edition of SQL Server you are using and could you please provide me the
table definition by right-click the table and choose 'all task' and choose
'genterate SQL script'? Or just by running
use database_name
go
exec sp_help table_name
Waiting for you reply and thank you for you post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can reproduce this problem regardless which table I use. Our server
edition is:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:9yJvtc56DHA.2164@.cpmsftngxa07.phx.gbl...
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL statement
will be a transaction without explicitly claim. If it will take many
actions within this transaction within it, when killed this process by
'KILL SPID' it will take some time to rollback all the actions it has
taken. So, from the sp_who2, you will notice that the status is
'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
press 'Cancel Query Execution' button, it will also be stoped and be rolled
back. For the rolled back process, it will take time depends on what it has
done before it is killed/stopped when it begin. So, in Query Analyzer and
for 'Kill', both will work and stop the 'update statistics' and rolled it
back.
Hope this answered you questions and if you still have questions, please
feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I don't think it is really ROLLBACK. For example, if UPDATE STATISTICS WITH
FULLSCAN on a table takes one hour, pressing 'Cancel Query Excution' button
will stop the process immediately. But if you using KILL command, even you
just started the UPDATE STATISTICS two second ago, it will continue run
until the procees is done. If you issues two UPDATE STATISTICS on a single
batch, it will stop until both table is done. You can check the result with
STATS_DATE, and it shows that both table's statistics have been updated.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:jueBFVK7DHA.808@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL
statement
> will be a transaction without explicitly claim. If it will take many
> actions within this transaction within it, when killed this process by
> 'KILL SPID' it will take some time to rollback all the actions it has
> taken. So, from the sp_who2, you will notice that the status is
> 'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
> press 'Cancel Query Execution' button, it will also be stoped and be
rolled
> back. For the rolled back process, it will take time depends on what it
has
> done before it is killed/stopped when it begin. So, in Query Analyzer and
> for 'Kill', both will work and stop the 'update statistics' and rolled it
> back.
> Hope this answered you questions and if you still have questions, please
> feel free to post new message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||U can also use
kill spid with statusonly to tell U how much percentage rollback is left . .
.|||Hi Lijun,
Thank you for your update and it is my pleasure to help you with you issue.
I need to make some explanation here. When a transaction is terminated
before it completed, it must be roll back. Also, when a transaction is
cancelled, the rollback time will not based on how long this transation it
has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
press' Cancel Query Execution', the transaction is terminated within the
same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel Query
Execution' button, is the 'UPDATE STATISTICS' rolled back'
When you kill a SPID, a kill command is sent to the SPID, that is, one
thread will send a KILL command to another thread, it is not guaranteed
that the SPID is killed. A spid may respond to the KILL command
immediately, or after a delay, or not at all.
Please refer to the following KB:
INF: Understanding How the Transact-SQL KILL Command Works
http://support.microsoft.com/?id=171224
When you kill a SPID running 'UPDATE STATISTICS, could you check the status
by DBCC PSS? What the status of it? When you restart the SQL Server Service
and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about the
'Updated' time?
I am looking forward to your reply. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for you reply. I suggest that you try to reproduce the scenario by
yourself (It is not difficult to do). By the way, the article you motioned
is apply to SQL Server 6.0 and 6.5.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:IkW$Pnt7DHA.568@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for your update and it is my pleasure to help you with you
issue.
> I need to make some explanation here. When a transaction is terminated
> before it completed, it must be roll back. Also, when a transaction is
> cancelled, the rollback time will not based on how long this transation it
> has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
> press' Cancel Query Execution', the transaction is terminated within the
> same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
> that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel
Query
> Execution' button, is the 'UPDATE STATISTICS' rolled back'
> When you kill a SPID, a kill command is sent to the SPID, that is, one
> thread will send a KILL command to another thread, it is not guaranteed
> that the SPID is killed. A spid may respond to the KILL command
> immediately, or after a delay, or not at all.
> Please refer to the following KB:
> INF: Understanding How the Transact-SQL KILL Command Works
> http://support.microsoft.com/?id=171224
> When you kill a SPID running 'UPDATE STATISTICS, could you check the
status
> by DBCC PSS? What the status of it? When you restart the SQL Server
Service
> and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about
the
> 'Updated' time?
> I am looking forward to your reply. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you very much for your update!
Yes, I create a huge database and tested what you have done. Yes, you are
right that when using the KILL to kill the SPID running 'UPDATE
STATISTICS', although SP_WHO2 showed the command is KILLED/ROLLBACK and I
could even got the information indicating that it is rolled back 100% and
estimated time for roll back is 1 second. However, the 'UPDATE STATISTICS'
is not stopped by the KILL command.
I tried to figure it out and it really took me some time. Later, I
discussed it with my senior engineers in my team and I got the some
information of it. It is a known issue of SQL Server. I should appologize
that in my last reply to your question, I did not provide you the right
answer, and I did not get the information of it in time. Could I provide
some more assistance this time. I mean if you have any bussiness impact
from it, please feel free to post here and I would try my best to help you!
Thank you very much.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
output window shows that this process is 'KILLED/ROLLBACK'. It looks like it
is continue updating statistics.
Why 'Cancel Query' command in the query analyser works, but kill command
does not wok?
How can I stop this process?
Thanks,
LijunHi,
Kill command is always safe and will rollback all the transaction.
Thanks
Hari
MCDBA
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:e3jW7Iq6DHA.3648@.TK2MSFTNGP11.phx.gbl...
quote:
> When I try to stop a process which running command 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
> output window shows that this process is 'KILLED/ROLLBACK'. It looks like
it
quote:|||Hi Lijun,
> is continue updating statistics.
> Why 'Cancel Query' command in the query analyser works, but kill command
> does not wok?
> How can I stop this process?
> Thanks,
> Lijun
>
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information, when you run 'UPDATE STATISTICS table_name WITH
SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
that the process is 'KILLED/ROLLBACK'. But you think you might not seen
this process when running sp_who2, right?
Is this problem always happen? Could you provide the information which
edition of SQL Server you are using and could you please provide me the
table definition by right-click the table and choose 'all task' and choose
'genterate SQL script'? Or just by running
use database_name
go
exec sp_help table_name
Waiting for you reply and thank you for you post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can reproduce this problem regardless which table I use. Our server
edition is:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:9yJvtc56DHA.2164@.cpmsftngxa07.phx.gbl...
quote:|||Hi Lijun,
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> From you information, when you run 'UPDATE STATISTICS table_name WITH
> SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
> that the process is 'KILLED/ROLLBACK'. But you think you might not seen
> this process when running sp_who2, right?
> Is this problem always happen? Could you provide the information which
> edition of SQL Server you are using and could you please provide me the
> table definition by right-click the table and choose 'all task' and choose
> 'genterate SQL script'? Or just by running
> use database_name
> go
> exec sp_help table_name
> Waiting for you reply and thank you for you post.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL statement
will be a transaction without explicitly claim. If it will take many
actions within this transaction within it, when killed this process by
'KILL SPID' it will take some time to rollback all the actions it has
taken. So, from the sp_who2, you will notice that the status is
'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
press 'Cancel Query Execution' button, it will also be stoped and be rolled
back. For the rolled back process, it will take time depends on what it has
done before it is killed/stopped when it begin. So, in Query Analyzer and
for 'Kill', both will work and stop the 'update statistics' and rolled it
back.
Hope this answered you questions and if you still have questions, please
feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I don't think it is really ROLLBACK. For example, if UPDATE STATISTICS WITH
FULLSCAN on a table takes one hour, pressing 'Cancel Query Excution' button
will stop the process immediately. But if you using KILL command, even you
just started the UPDATE STATISTICS two second ago, it will continue run
until the procees is done. If you issues two UPDATE STATISTICS on a single
batch, it will stop until both table is done. You can check the result with
STATS_DATE, and it shows that both table's statistics have been updated.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:jueBFVK7DHA.808@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL
statement
> will be a transaction without explicitly claim. If it will take many
> actions within this transaction within it, when killed this process by
> 'KILL SPID' it will take some time to rollback all the actions it has
> taken. So, from the sp_who2, you will notice that the status is
> 'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
> press 'Cancel Query Execution' button, it will also be stoped and be
rolled
> back. For the rolled back process, it will take time depends on what it
has
> done before it is killed/stopped when it begin. So, in Query Analyzer and
> for 'Kill', both will work and stop the 'update statistics' and rolled it
> back.
> Hope this answered you questions and if you still have questions, please
> feel free to post new message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||U can also use
kill spid with statusonly to tell U how much percentage rollback is left . .
.|||Hi Lijun,
Thank you for your update and it is my pleasure to help you with you issue.
I need to make some explanation here. When a transaction is terminated
before it completed, it must be roll back. Also, when a transaction is
cancelled, the rollback time will not based on how long this transation it
has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
press' Cancel Query Execution', the transaction is terminated within the
same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel Query
Execution' button, is the 'UPDATE STATISTICS' rolled back'
When you kill a SPID, a kill command is sent to the SPID, that is, one
thread will send a KILL command to another thread, it is not guaranteed
that the SPID is killed. A spid may respond to the KILL command
immediately, or after a delay, or not at all.
Please refer to the following KB:
INF: Understanding How the Transact-SQL KILL Command Works
http://support.microsoft.com/?id=171224
When you kill a SPID running 'UPDATE STATISTICS, could you check the status
by DBCC PSS? What the status of it? When you restart the SQL Server Service
and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about the
'Updated' time?
I am looking forward to your reply. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for you reply. I suggest that you try to reproduce the scenario by
yourself (It is not difficult to do). By the way, the article you motioned
is apply to SQL Server 6.0 and 6.5.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:IkW$Pnt7DHA.568@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for your update and it is my pleasure to help you with you
issue.
> I need to make some explanation here. When a transaction is terminated
> before it completed, it must be roll back. Also, when a transaction is
> cancelled, the rollback time will not based on how long this transation it
> has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
> press' Cancel Query Execution', the transaction is terminated within the
> same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
> that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel
Query
> Execution' button, is the 'UPDATE STATISTICS' rolled back'
> When you kill a SPID, a kill command is sent to the SPID, that is, one
> thread will send a KILL command to another thread, it is not guaranteed
> that the SPID is killed. A spid may respond to the KILL command
> immediately, or after a delay, or not at all.
> Please refer to the following KB:
> INF: Understanding How the Transact-SQL KILL Command Works
> http://support.microsoft.com/?id=171224
> When you kill a SPID running 'UPDATE STATISTICS, could you check the
status
> by DBCC PSS? What the status of it? When you restart the SQL Server
Service
> and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about
the
> 'Updated' time?
> I am looking forward to your reply. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you very much for your update!
Yes, I create a huge database and tested what you have done. Yes, you are
right that when using the KILL to kill the SPID running 'UPDATE
STATISTICS', although SP_WHO2 showed the command is KILLED/ROLLBACK and I
could even got the information indicating that it is rolled back 100% and
estimated time for roll back is 1 second. However, the 'UPDATE STATISTICS'
is not stopped by the KILL command.
I tried to figure it out and it really took me some time. Later, I
discussed it with my senior engineers in my team and I got the some
information of it. It is a known issue of SQL Server. I should appologize
that in my last reply to your question, I did not provide you the right
answer, and I did not get the information of it in time. Could I provide
some more assistance this time. I mean if you have any bussiness impact
from it, please feel free to post here and I would try my best to help you!
Thank you very much.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Subscribe to:
Posts (Atom)