September 9, 2011 at 9:48 am
Tom.Thomson (9/9/2011)
edit: I see I should have looked more carefully at the first item.
Then what's your opinion on this?
September 9, 2011 at 9:48 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.
Yup, that's exactly it. Not a bug, just badly written T-SQL. (A stored proc must be called with EXEC if it's not the first statement in the batch
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2011 at 9:49 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?
I have verified the behavior with SQL Server 2005 SP4.
I also tried adding a trailing ";" in the first and last examples and still got this error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'sp_who2'.
So at least with 2005 SP4 the semi-colon doesn't exactly fix it, you need the EXEC or EXECUTE whenever including an SP call in a batch.
I'd like to see them allow it to work if you terminate with a semi-colon, but as is, I don't know how you could code for it without some kind of keyword or terminator since SQL Server doesn't care about white space.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2011 at 9:50 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!
They very likely will become mandatory (and not an sp_configure option), though it'll take a few versions.
The T-SQL language is getting to the point that the parser can't completely tell where a statement ends and where the next begins. That's why new features like CTEs and MERGE require that either the previous statement or the current statement be terminated with a ;
It's becoming good practice to terminate statements with a ; anyway, get into the habit of doing that for any new code written
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2011 at 9:52 am
Jack Corbett (9/9/2011)
So at least with 2005 SP4 the semi-colon doesn't exactly fix it, you need the EXEC or EXECUTE whenever including an SP call in a batch.
Yes you do. That's documented. A stored proc call must start with EXEC unless it's the first statement in the batch (and ; is not a batch terminator)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2011 at 9:57 am
GilaMonster (9/9/2011)
They very likely will become mandatory (and not an sp_configure option), though it'll take a few versions.The T-SQL language is getting to the point that the parser can't completely tell where a statement ends and where the next begins. That's why new features like CTEs and MERGE require that either the previous statement or the current statement be terminated with a ;
It's becoming good practice to terminate statements with a ; anyway, get into the habit of doing that for any new code written
True.
Nevertheless this is going to become an issue with old, currently unsupported applications still running out there. I foresee some shops stopping to upgrade some SQL Server platforms, staying on the last version that supports such applications. This is not new, I've seen same happening with a particular application that would not run on anything better than Oracle 7.
Bottom line is... we will survive and a lot of people are going to get a chance of getting an entry level position.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 9, 2011 at 9:57 am
GilaMonster (9/9/2011)
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!
They very likely will become mandatory (and not an sp_configure option), though it'll take a few versions.
The T-SQL language is getting to the point that the parser can't completely tell where a statement ends and where the next begins. That's why new features like CTEs and MERGE require that either the previous statement or the current statement be terminated with a ;
It's becoming good practice to terminate statements with a ; anyway, get into the habit of doing that for any new code written
So absolutely no point in a connect item with this, even if just to document the behavior and have it findable in google?
September 9, 2011 at 10:03 am
No. This is not a bug, it's not a suggestion. Blog it if you want the world to know, Connect is for bugs and suggestions, not quirks of the T-SQL language.
It's not more a bug than this returning 2 columns not 4.
SELECT
object_id
object_name,
type
type_desc
FROM sys.objects
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2011 at 10:05 am
GilaMonster (9/9/2011)
No. This is not a bug, it's not a suggestion. Blog it if you want the world to know, Connect is for bugs and suggestions, not quirks of the T-SQL language.It's not more a bug than this returning 2 columns not 4.
SELECT
object_id
object_name,
type
type_desc
FROM sys.objects
tx.
September 9, 2011 at 10:06 am
PaulB-TheOneAndOnly (9/9/2011)
Nevertheless this is going to become an issue with old, currently unsupported applications still running out there. I foresee some shops stopping to upgrade some SQL Server platforms, staying on the last version that supports such applications. This is not new, I've seen same happening with a particular application that would not run on anything better than Oracle 7.
Sure, same as with *= joins and hints without brackets. Running the upgrade advisor will throw up warnings/errors and people decide based on that. I know an app where they won't upgrade from SQL 2000 because of the *= joins (yes, that's still in compatibility mode, I know)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2011 at 10:15 am
GilaMonster (9/9/2011)
PaulB-TheOneAndOnly (9/9/2011)
Nevertheless this is going to become an issue with old, currently unsupported applications still running out there. I foresee some shops stopping to upgrade some SQL Server platforms, staying on the last version that supports such applications. This is not new, I've seen same happening with a particular application that would not run on anything better than Oracle 7.Sure, same as with *= joins and hints without brackets. Running the upgrade advisor will throw up warnings/errors and people decide based on that. I know an app where they won't upgrade from SQL 2000 because of the *= joins (yes, that's still in compatibility mode, I know)
I had a very short client with access 1997, sql 7 EOM (as in SP 0).
The whole thing was on a 32/64 bit mixed vm (I forget which was what).
Wouldn't upgrade ANYTHING. Just putting out fires 24 / 7 instead of doing it right.
They had processing windows from 11 to 13 where NO ONE could use the system because they were running the billing code (copy between systems). Then another window around 3 PM. Then another during the night to process like 100 documents and that 1 tooks hours too.
Crap RBAR code, crap machine, worst setup and a small - medium amount of data to process (DB was like 40GB) & small-ish amount of users (±20).
I came in, setup a linked server, insert where not exists...
Took the mid-day window down from 2 hours to 1-10 seconds. 😀
That's when the boss came in and said I could have upped the ante a little bit about my skills during the interview (that was like the 5th process I had fixed in less than 1 hour work of work).
To which I replied 2 weeks later with, you should have paid what I asked for (wanted 50$ / H, offered 45K / year :w00t:).
Found something else while I was getting just enough to keep eating and left.
Good times!
September 9, 2011 at 10:35 am
GilaMonster (9/9/2011)
No. This is not a bug, it's not a suggestion. Blog it if you want the world to know, Connect is for bugs and suggestions, not quirks of the T-SQL language.It's not more a bug than this returning 2 columns not 4.
SELECT
object_id
object_name,
type
type_desc
FROM sys.objects
Still processing this in my mind...
Any reason why Begin tran <name>
doesn't require rollback tran <name> for the undo?
I'm thinking that it's because in a error situation you don't know exactly where you are and just want to rollback everything... but then again you could just make a command like ROLLBACK TRAN ALL
I'm sure MS thought long and hard about all the inner workings here, but it still seems "broken" when I look at it (despite all your explaination :-)).
Just a small FYI, I hit this "problem" when testing a switch between implicit_transactions ON by default in any ssms window to a default template of BEGIN / ROLLBACK TRAN.
I was used to just type sp_who2 or sp_WhoIsActive and then I hit this little issue with the new setup.
I'm just glad I was just running small checkup sps and not something critical that doesn't return a dataset.
September 9, 2011 at 10:49 am
A ROLLBACK TRANSACTION only refers to the outermost transaction.
Straight from BOL:
It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.
Therefore, any transaction name other than the outermost would raise an error. So, why bother to require it in the first place? 😉
September 9, 2011 at 10:53 am
LutzM (9/9/2011)
A ROLLBACK TRANSACTION only refers to the outermost transaction.Straight from BOL:
It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.
Therefore, any transaction name other than the outermost would raise an error. So, why bother to require it in the first place? 😉
As I said, still processing the whole list of meetings the ms guys about this part of the product.... getting there!
September 9, 2011 at 11:21 am
Ninja's_RGR'us (9/9/2011)
Tom.Thomson (9/9/2011)
edit: I see I should have looked more carefully at the first item.Then what's your opinion on this?
That it doesn't show any buggy behaviour in 2008 R2(SP1).
But, on the other point, here's an opinion that will probably shock people:-
SQL syntax has been a pig's breakfast since day 1, and has deteriorated continuously since. It has now decayed to the point where it cannot be parsed without having some sort of marker to distinguish one meaning of a reserved word (which can occur near the end of a statement) from another (which can occur at the beginning). Since the obvious and sensible way to do that was to choose a new reserved word for the new usage, instead of using an already existing one in such a way as to screw up the parser, a different method had to be found - it is anathema to SQL language definers to do anything the right way. What we have acquired in effect might as well be a new reserved word, ";WITH" (and is very often written that way) but the standards people insist that ";" be a statement terminator (not a seperator, mark you, but a terminator - I can't remember any new language apart from SQL having or gaining one of those since about 1965). Life would probably have been a bit more pleasant if the early designers of SQL (or SEQUEL as it then was called) had paid attention to someone with an understanding of language design and mathematics but there was no hope of that because at the relvant time mainstream IBM thoughts on programming languages were dominated by the PL/I nonsense and their real language experts were sidelined (as also was Codd). Later on others (including ANSI) mainly just followed IBM's lead, with its strong bias towards a slightly updated 1950s style reserved word driven language where no more thought was given to conformity to any general syntactic principles than was given say to harmonising the computed IF statement and the FORMAT statement in Fortran during the Fortran IV farce. Fortunately relational calculus is fairly hard to express in that style, so some sanity crept through - but not much. For example the insistence that rowset expressions had to be given names if they were to act as data sources even though attribute names might be enough to resolve expressions involving their attributes demonstrated that the concept of anonymous objects based on structures found in mathematical and logical calculi (eg in the functional calculus used by McCarthy about fifteen years earlier as the basis for Lisp) was anathema. It seems strange to say the least that IBM used none of Codd, McCarthy, Backus, and Iverson on SQL - after all, surely the inventor of the relational model and the company's three most famed and successful language designers experts were just the people such a project should have had access to and made use of.
The proposal to make statement separators mandatory wherever they are permitted was pure lunacy, since it will invalidate a vast part of the SQL already out there. Perhaps it was designed and pushed through with the intention of forcing the SQL language to remain permanently a poorly constructed nightmare by people who feel threatened by modern (ie post 1958 in some places but more recent in others) ideas about language design.
I've found over the decades that many people are frightened by logic languages like Prolog, by functional languages like ML or Parlog, by function languages like FP, by Object Oriented languages like Simula, and by process description languages like CCS or CSP; this fright is caused by fear of the unknown - so it's usually easily overcome by a little education (of course I've also met people who were frightened by C++, and there education can only increase the fear) unless the person concerned is too old to learn new tricks. I suspect the reason SQL has ended up like it is is simply that the people who have at various times controlled its definition were frightened of it ending up too different from Fortran or Cobol - a relational language would frighten them just as they might be frightened by those other new language types. The insistence on statement terminators is more of the same - NewLine for statement separator, anyone? - it was very common in the bad old days! But they couldn't have newline, so they made ";" a statement terminator.
Tom
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply