September 9, 2011 at 8:53 am
Did I miss the memo where September 2k11 is bug month for sql server?
I think I found another one today. Please confirm this is an issue on your servers. I'd like one confirmation for at least 2K8, R2 & Denali CTP3 (they won't fix this in 2K5 & pre)
Ran this on 2k5, 2k8 R2 and Denali CTP 3 with the same problem.
Buggy behavior
BEGIN TRAN
--no EXECUTE
sp_who2
ROLLBACK
--returns Command(s) completed successfully.
--No actual dataset / error returned
BEGIN TRAN
EXEC sp_who2
ROLLBACK
--(33 row(s) affected)
Expected behavior => Either run the code or return an error, but NOT NEITHER!
SELECT 1 as DumbSelect
sp_who2
/*
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'sp_who2'.
*/
Just when you think your code has ran to do something important, you realize it DIDN'T :crazy: and you look like an A$S to your clients (glad I skipped that last part)!
September 9, 2011 at 8:56 am
Reproduced on 2k8 R2
September 9, 2011 at 8:59 am
Cadavre (9/9/2011)
Reproduced on 2k8 R2
What SP level (MS usually prefers the latest SP, so I need to ask)
Tx, 2 more to go!
2K8 & Denali
September 9, 2011 at 9:01 am
FWIW I can reproduce but I dont think its necessarily a bug.
The first query appears to be starting a transaction named sp_who2.
The last one appears to have a problem with a "double barrelled" alias.
Not great behaviour but not entirely unexpected. Bad TSQL formatting really.
September 9, 2011 at 9:02 am
Ninja's_RGR'us (9/9/2011)
Cadavre (9/9/2011)
Reproduced on 2k8 R2What SP level (MS usually prefers the latest SP, so I need to ask)
Tx, 2 more to go!
2K8 & Denali
SP1
MysteryJimbo (9/9/2011)
FWIW I can reproduce but I dont think its necessarily a bug.The first query appears to be starting a transaction named sp_who2.
The last one appears to have a problem with a "double barrelled" alias.
Not great behaviour but not entirely unexpected. Bad TSQL formatting really.
Good point, didn't really think of it like that but I think you're right.
September 9, 2011 at 9:03 am
It behaves the same for user procs too, you can execute a proc singularly without the execute key word. Put another one in and boom!
September 9, 2011 at 9:05 am
MysteryJimbo (9/9/2011)
FWIW I can reproduce but I dont think its necessarily a bug.The first query appears to be starting a transaction named sp_who2.
The last one appears to have a problem with a "double barrelled" alias.
Not great behaviour but not entirely unexpected. Bad TSQL formatting really.
Good point!
September 9, 2011 at 9:10 am
I think this closes the debate. Th eproblem will self-correct in further releases :
See the semi-column after begin tran
BEGIN TRAN;
--no EXECUTE
sp_who2
ROLLBACK
/*
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'sp_who2'.
*/
So once ";" are mandatory, the problem will go away.
I was going to propose forcing a syntax change to for BEGIN TRAN [AS, or name = ]. But I'm 99% sure it would not have been approved because this can break working prod code.
In the mean time, this is annoying :-D.
September 9, 2011 at 9:14 am
Ninja's_RGR'us (9/9/2011)
So once ";" are mandatory, the problem will go away.
I love this proposed new "feature". Millions of lines of code to be changed = Unlikely to be anything other than an sp_configure option. 😀
September 9, 2011 at 9:18 am
MysteryJimbo (9/9/2011)
Ninja's_RGR'us (9/9/2011)
So once ";" are mandatory, the problem will go away.
I love this proposed new "feature". Millions of lines of code to be changed = Unlikely to be anything other than an sp_configure option. 😀
So why Am I under the impression that ; will be mandatories in the future? I'm pretty sure I read this somewhere!
September 9, 2011 at 9:24 am
Ninja's_RGR'us (9/9/2011)
I think this closes the debate. Th eproblem will self-correct in further releases :See the semi-column after begin tran
BEGIN TRAN;
--no EXECUTE
sp_who2
ROLLBACK
/*
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'sp_who2'.
*/
So once ";" are mandatory, the problem will go away.
I was going to propose forcing a syntax change to for BEGIN TRAN [AS, or name = ]. But I'm 99% sure it would not have been approved because this can break working prod code.
In the mean time, this is annoying :-D.
Agreed that this is never going to happen. A shame in a way, because SQL syntax does need it and as they've partially enforced it with Merge and CTE's, it's quite inconsistent and misleading at times.
I hate seeing examples of CTE's (even from BOL) that have semi-colons immediately preceeding WITH as if it's part of the CTE statement itself where it's actually the terminator for whatever code is going to run before it!
edit: retract the "even from BOL" statement. On reading again it was in the Community Content section
September 9, 2011 at 9:27 am
Ninja's_RGR'us (9/9/2011)
MysteryJimbo (9/9/2011)
Ninja's_RGR'us (9/9/2011)
So once ";" are mandatory, the problem will go away.
I love this proposed new "feature". Millions of lines of code to be changed = Unlikely to be anything other than an sp_configure option. 😀
So why Am I under the impression that ; will be mandatories in the future? I'm pretty sure I read this somewhere!
Its supposed to be happening but I cant see it being mandatory if microsoft actually want people to upgrade. Early adopters will be put off as I know I will. Something like 100,000 database objects to "fix" when they arent broken. Thats why I would see it becoming a database or server option. I could be wrong since i cant predict the future!
Pretty sure I found it on the deprecated features list @ MS
September 9, 2011 at 9:30 am
So is it worth opening a connect ticket on this so we can let the community vote on it?
September 9, 2011 at 9:38 am
Ninja's_RGR'us (9/9/2011)
Did I miss the memo where September 2k11 is bug month for sql server?I think I found another one today. Please confirm this is an issue on your servers. I'd like one confirmation for at least 2K8, R2 & Denali CTP3 (they won't fix this in 2K5 & pre)
Ran this on 2k5, 2k8 R2 and Denali CTP 3 with the same problem.
Ran on 2008 SP2 SP1 (ver 10.50.2500)Confirm 1st item as erroneous/buggy behaviour 2nd item runs SP_WHO2 as expected, 3rd failed as expected.
edit: I see I should have looked more carefully at the first item.
Tom
September 9, 2011 at 9:41 am
Ninja's_RGR'us (9/9/2011)
So is it worth opening a connect ticket on this so we can let the community vote on it?
If there isnt one already it's probably a good idea.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply