Another bug in SS?

Question

Another bug in SS?

Ninja's_RGR'us

SSC Guru

Points: 294069
More actions
September 9, 2011 at 8:53 am

#243523

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)!

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Reproduced on 2k8 R2

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

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

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 3

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.

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Ninja's_RGR'us (9/9/2011)
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

SP1

--EDIT--

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.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

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!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

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!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

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.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 8

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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!

HowardW One Orange Chip Points: 29109 More actions · Answer 10

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

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 11

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

So is it worth opening a connect ticket on this so we can let the community vote on it?

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 14

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.

Another bug in SS?

Cookies on SQLServerCentral