October 12, 2010 at 10:31 pm
Trick question but confused with explanation & query posted in the answer because it throws an error. Thanks Hugo for explanation. I learn usage of delimiters.
Thanks
October 13, 2010 at 1:12 am
Obvious! In the daily work you do not need a special syntax. But, sometime my clients ask me for something special. And thanks to the delimiter I can extract data and send them without writing an application outside sqlserver.
In the example, unfortunately, most of syntax has been lost because of html tag.
So, I provide another simplest one. The following script build syntax to expand sp, functions or views, comment or uncomment the type of object and substitute %my_proc% with the name of object, run it and than select all the result copy and paste and execute in the query window.
set nocount on
select 'exec sp_helptext ' AS [--],o.name
from sysobjects o(readuncommitted)
where o.xtype IN(''
,'P '--: stored procedure'
--,'IF'--: inline function'
--,'FN'--: scalar function'
--,'TF'--: table function'
--,'V '--: view'
--,'TR'--: trigger'
)
and o.name like '%my_proc%'
and o.name not like 'dt[_]%'
order by o.Name
set nocount off
At last, the 84% of users learned 3 new things:
1) Read carefully the Qotd.
2) Use of delimiter.
3) Only lack of fantasy is the limit.
October 13, 2010 at 1:56 am
Carlo Romagnano (10/13/2010)
In the example, unfortunately, most of syntax has been lost because of html tag.
Could you please zip that script and attach it to your next message? 🙂
October 13, 2010 at 2:39 am
thanks Hugo... 🙂
Hugo Kornelis (10/12/2010)
I got two points for answering a question that tests no useful skill whatsoever? Come on, Steve! Even one point would have been more than enough for this one!Carlo, thanks for the effort of submitting a QotD (not cynical!). But next time, please submit a question about something that actually has any real use for SQL Server professionals.
I can imagine someone accidentally not matching opening and closing brackets (especially if []] is used somewhere in the bracketed text). But then also having an extraneous closing bracket in another batch, that is sent at the same time, and with code after that bracket that "just happens" to make the original code complete so that no error is thrown? The chance of that happening anytime, anywhere, is infenitely small. So noone will learn anything useful from this question.
Since you obviously (based on the explanation) wanted to educate visitors of this site about the usefulness of using brackets to delimit identifiers, you should have submitted a question that did just that, instead of deliberately adding nonsensical code to ensure as little people as possible get it right. The aim of this site should not be to get as many people as possible to fail, but to show as many people as possible the great stuff SQL Server can do.
October 14, 2010 at 10:56 pm
deleteme
October 18, 2010 at 10:18 am
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 27, 2010 at 3:04 pm
Management studio shows me this script mostly in black meaning there is something wrong in the script even if it's completed successfully. I guess even colorblind people can see it. I don't see the point of this question. I hope nobody will post these kind of 'questions' any more. It's not CS spirit.
October 27, 2010 at 3:36 pm
natalie.ignatieva (10/27/2010)
Management studio shows me this script mostly in black meaning there is something wrong in the script even if it's completed successfully.
The color coding is actually correct. The first [ character starts a table name that only ends at the final ], so everything in between them is part of the table name. Black is the default color used for (a.o.) table names.
Please read the rest of the discussion for a more comprehensive explanation (and for many people agreeing with our dislike of the question)
October 27, 2010 at 3:54 pm
The color coding is actually correct. The first [ character starts a table name that only ends at the final ], so everything in between them is part of the table name. Black is the default color used for (a.o.) table names.
Please read the rest of the discussion for a more comprehensive explanation (and for many people agreeing with our dislike of the question)
Yes, the color is correct, but I didn't have a chance to copy the script, so I had to type it. I missed ] in the line with 'while' because you don't expect developers to create tables with such weird names and thought it was typo or something. but even if I didn't miss it and executed the statement, I would see that author just wanted to create a table with such weird name. My point is that if you have your app generating these kind of statements and you try to understand what actually is wrong (yes, author wanted to look us for some errors in the script as if it's supposed actually run infinite loop or begin transaction), anyway you copy it from profiler to studio, see 'BEGIN' and 'INSERT' in black and just then you look for syntax issues.
As I understand the point of this question is to make us develop skills in typing scripts from the image and don't make errors, right? I failed. Am I a bad SQL developer after that?
October 27, 2010 at 4:30 pm
natalie.ignatieva (10/27/2010)
As I understand the point of this question is to make us develop skills in typing scripts from the image and don't make errors, right? I failed. Am I a bad SQL developer after that?
No. I fully agree with you that this question is totally useless.
November 3, 2010 at 6:33 pm
Okay, finally got around to answering questions, and hit this one.
I got stuck on the title and started looking to see if something new had been added to SQL Server 2008 R2.
Usually I'll support individuals that post questionable QotD; but this one, once I saw the explaination, I knew I had been tricked. Of course you would get a column with a totally worthless column name.
This is what I consider a trick question.
November 22, 2010 at 2:34 pm
This question fooled me 🙁
Thanks Hugo for the great walkthrough, I learned something new 🙂
Cheers
February 1, 2011 at 1:01 am
A tricky question indeed.
And very good explaination by Hugo. Thanks Hugo. Learnt about delimiters.
August 12, 2011 at 12:16 am
Hugo Kornelis (10/12/2010)
lukus_g (10/12/2010)
so, does the transaction actually run and get rolled back?No.
The author of the question uses a long explanation to expand on how usefull the possibility to use non-standard characters in identifiers is. (It can be - but only in some cases, it can also be very confusing and introduce errors, so take care. And if you care for ANSI standards and portable code, consider using "double quotes" instead of [bracktes] to delimit identifiers). Unfortunately, he completely forgot to explain the actual question.
First, focus on the first line:
[font="Courier New"]create table [VarArray[]](i int)[/font]
The first [ starts a delimited identifier. That means that from there on, every character is considered part of the table name, and al characters are allowed. With one exception. The ] character will be considered the end of the delimited identifier. So what if we want to use a ] character as part of the identifier? The answer to that question is to escape it. In a [delimited identifier], you can escape the ] character by doubling it, so you get ]]. This can be very confusing. A human reader would interpret an identifier such as [identifier]]] as being terribly unmatched, but the SQL Server parser replaces the first two closing brackets with a single ] symbol as part of the identifier, and interprets the third closing identifier as the brackets that signifies the end of the delimited identifier.
Carlo played a trick on us by adding a [ sign and a double ] to the identifier. We tend to pair up the identifiers and conclude that the last ] ends the identifier, and (i int) is the column list. SQL Server simply treats the [ as one character in the identifier, then treats the ]] as one character in the identifier, and then also treats (i int) as part of the identifier.
If you execute ONLY the line
[font="Courier New"]create table [VarArray[]](i int)[/font]
you will get an error message:
[font="Courier New"]Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'VarArray[](i int)
'.[/font]
If you add a third ] right after the second and before (i int), the code will succeed and you will create a table with name VarArray[] and one integer column named i.
Since the first line does not end the delimited identifier, the end of line character and the next line is considered part of the identifier as well. That even includes the GO lines - SSMS has its own parser that also sees that the delimiter has not ended, so it will not interpret these specific GO lines as batch seperators, but will include them in the batch. All lines in the code are sent as one single batch.
This long delimited identifier finally ends on this line:
[font="Courier New"]while 1=1)][/font]
The extra ] at the end is easily missed by humans, but the SQL Server parser does recognise it, and interprets it as the end of the identifier. The lines that come after this line look like a single statement with two syntax errors (both enclosing in parentheses and the statement itself would violate syntax rules if used in a real WHILE loop), but are actually interpreted as a column list.
So, what this code really does is - it creates a table with this hideous name:
VarArray[](i int)GO
begin tran
insert into VarArray(i) select 1
rollback
GO
while(1=1)
and with one single column, named print_i and typed as integer.
Thank you for your explanation, I couldn't understand the original explanation.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
September 18, 2013 at 10:44 am
Thank you for the explanation, Hugo!
Yet I totally disagree with you in one point: This question definitely is NOT useless!
I find it helpful to train me on being careful and look twice before answering something. And that _you_ didn't learn something: OK, accepted. But please do not talk for me: I was surprised by the result and it was good for a laugh; additionally it improved my understanding for delimiters.
________________________________________________________
If you set out to do something, something else must be done first.
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply