SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / Temporary Objects 2 / Latest Posts

RE: Temporary Objects 2

Dineshbabu — Thu, 10 Jan 2013 23:13:00 GMT

[quote][b]Hugo Kornelis (1/10/2013)[/b][hr][quote][b]Dineshbabu (1/10/2013)[/b][hr]Can anyone help me in simulating the question in management studio without any application.[/quote]Using Management Studio, create the stored procedure as specified in the question, but add a command WAITFOR DELAY '0:00:10'; to simulate the 10 second running time.Now open two connections, and type EXEC GetData_Date; in both. Hit execute in one tab, switch to the other tab, then hit execute there.[/quote]Thanks Hugo..I already tried this, but i used WAITFOR in exec statement, so i'm unable to simulate this... Now i got it..

RE: Temporary Objects 2

Hugo Kornelis — Thu, 10 Jan 2013 07:16:43 GMT

[quote][b]Dineshbabu (1/10/2013)[/b][hr]Can anyone help me in simulating the question in management studio without any application.[/quote]Using Management Studio, create the stored procedure as specified in the question, but add a command WAITFOR DELAY '0:00:10'; to simulate the 10 second running time.Now open two connections, and type EXEC GetData_Date; in both. Hit execute in one tab, switch to the other tab, then hit execute there.

RE: Temporary Objects 2

Dineshbabu — Thu, 10 Jan 2013 07:11:52 GMT

Can anyone help me in simulating the question in management studio without any application.Thanks

RE: Temporary Objects 2

John Esraelo-498130 — Wed, 04 Apr 2012 21:02:38 GMT

I did not continue to read the rest of the script after seeing the 2 "," back to back.. therefore, I have answered the last 2 .. since both will fail.. oh well, whatever..

RE: Temporary Objects 2

Kangana Beri — Wed, 21 Mar 2012 13:33:32 GMT

Good question! Thanks.

RE: Temporary Objects 2

Britt Cluff — Wed, 14 Mar 2012 19:52:01 GMT

Good question. Thanks for submitting.

RE: Temporary Objects 2

codebyo — Mon, 12 Mar 2012 21:14:20 GMT

Great question.But am I the only that thought that the syntax error was part of the question?But even so there wouldn't be a right answer for that.I didn't understand the explanations about the conflict in object names.Doesn't that only happen with global temp tables (those starting with ##)?EDIT: Nevermind. I read the error messages wrong.

RE: Temporary Objects 2

Peter Trast — Mon, 12 Mar 2012 07:34:24 GMT

I got it for the right logical reason but on the wrong object (constraint, not temp table). Great question!

RE: Temporary Objects 2

Evil Kraig F — Fri, 09 Mar 2012 13:23:45 GMT

I almost got this wrong but the 'select 2 answers' bit excludes the need for the answer 'both run to completion', which would thus give me 3 answers.The first option should be removed to reduce second-guessing to the correct answer. I know the bit about not naming Temp Table constraints but I was skimming and didn't really read into it the first time. *shrugs*

RE: Temporary Objects 2

Brigadur — Fri, 09 Mar 2012 12:52:23 GMT

Good question! Good to be reminded of this about constraints on temporary tables.

RE: Temporary Objects 2

bitbucket-25253 — Fri, 09 Mar 2012 10:55:49 GMT

[quote][b]SQLRNNR (3/9/2012)[/b][hr]That typo threw me way off. I want my point back.So OK let you have one of mine ... Thanks for the laugh I need it and that is for sure (no typo here)Just kidding - I thought Ron might appreciate that.:hehe:[/quote]

RE: Temporary Objects 2

Rob Schripsema — Fri, 09 Mar 2012 10:47:29 GMT

Great question. Definitely learned something today.I agree that the typo was a serious distraction, but I thought it was pretty obviously a typo and I assumed that QOTD would NEVER, NEVER try to stump us with a trick question....:-P

RE: Temporary Objects 2

KWymore — Fri, 09 Mar 2012 10:10:23 GMT

Thanks for the question Ron. Learned something today!

RE: Temporary Objects 2

SQLRNNR — Fri, 09 Mar 2012 09:53:46 GMT

That typo threw me way off. I want my point back.Just kidding - I thought Ron might appreciate that.:hehe:

RE: Temporary Objects 2

OzYbOi d(-_-)b — Fri, 09 Mar 2012 08:36:53 GMT

great question. good reminder - cheers

RE: Temporary Objects 2

Lon-860191 — Fri, 09 Mar 2012 08:03:05 GMT

Thinking about this a bit more, the question would have been a bit more difficult if the first answer were eliminated. Then both running to success would have been a potential answer. Either way it made m e think.

RE: Temporary Objects 2

Lon-860191 — Fri, 09 Mar 2012 07:46:27 GMT

I looked at it and since answer 1 also made answer 2 and 3 correct, that was not the answer. So my second take was the correct answer but I had to sit and think was there a gotcha that I was missing. I didn't think this was extremely difficult but it was thought provoking.

RE: Temporary Objects 2

Daniel Bowlin — Fri, 09 Mar 2012 07:21:59 GMT

Learned something today, thanks.

RE: Temporary Objects 2

John-150025 — Fri, 09 Mar 2012 06:54:45 GMT

Typo in the question!!

RE: Temporary Objects 2

rmechaber — Fri, 09 Mar 2012 06:30:40 GMT

I think of this having less to do with temporary objects so much as it does with schema restrictions: you can't have 2 objects with the same name in the schema. But it's easier to make the mistake when creating temp tables, b/c you can create many temp tables with the "same" name.I found posted examples of this problem here: [url=http://sequelserver.blogspot.com/2006/12/named-constraints-on-temp-tables.html]http://sequelserver.blogspot.com/2006/12/named-constraints-on-temp-tables.html[/url] and here: [url=http://www.sqlservercentral.com/articles/63472/]http://www.sqlservercentral.com/articles/63472/[/url].Also, in my haste, I overlooked the explicitly named constraint in the question, and I was about to check the first answer (both sessions execute successfully), but you gave it away by telling us to "SELECT THE CORRECT 2 ANSWERS". So I sharpened my eyeballs! Thanks for the question,Rich

RE: Temporary Objects 2

bitbucket-25253 — Fri, 09 Mar 2012 05:49:49 GMT

[quote][b]L' Eomot Inversé (3/9/2012)[/b][hr]Good straightforward question and answer.Easy for me, as it was from Hugo's first comment that I first discovered that people could have named constraints (instead of letting SQLServer generate the constraint names) on temporary tables, and that was sufficiently recent (just last November) for me not to have forgotten the shock (shock because I could see where it would probably lead and so immediately tested it, with a result that was confirmed by Hugo's second comment in that discussion).[/quote]I also followed your path, especially since it was Hugo commenting on another QOD that I had published. The number of comments for that QOD was rather sparse and I felt that more people should be aware of the pitfalls of what at first appearence appears to be innocuous. Hence this QOD.

RE: Temporary Objects 2

L' Eomot Inversé — Fri, 09 Mar 2012 05:21:31 GMT

Good straightforward question and answer.Easy for me, as it was from Hugo's first comment that I first discovered that people could have named constraints (instead of letting SQLServer generate the constraint names) on temporary tables, and that was sufficiently recent (just last November) for me not to have forgotten the shock (shock because I could see where it would probably lead and so immediately tested it, with a result that was confirmed by Hugo's second comment in that discussion).

RE: Temporary Objects 2

bitbucket-25253 — Fri, 09 Mar 2012 05:21:09 GMT

[quote][b]Hugo Kornelis (3/9/2012)[/b][hr]I am embarassed to admit that I got this question, that apparently was based on my comments, wrong.I should have been warned. I even checked the code several times. All because I thought that if both programs run without error (as I assumed), there would have been THREE correct answers. So I checked. And checked again.I did contemplate the extra comma, but figured that this would not be Ron's style, so it had to be a genuine type (hint: test all code before submitting QotD!)I did also contemplate the inconsistent use of upper- and lowercase, but figure that wouldn't be Ron's style either (hint: test all code on a database with case-sensitive collation)And after reading the script yet another time, I ticked the first three options - and then kicked myself for overlooking the named constraint.Thanks, Ron! Good question (though unfortunately hampered by the typo and the inconsistent upper-/lowercase).[/quote]I am too use to using a case insensitive collation .. another bad habit that I have developed. I did test the code BEFORE submitting for a QOD, and that extra comma must have slipped in at that time, now that is not a valid excuse (as I well know), what can I say except things happen, and it is my fault for which I apologize to all.

RE: Temporary Objects 2

sqlmunkee — Fri, 09 Mar 2012 04:32:49 GMT

You're right, and for the reasons in my blog post :-) My bad. Sorry, I'll get my coat.

RE: Temporary Objects 2

david-739410 — Fri, 09 Mar 2012 04:19:49 GMT

I agree with monkeyloafer the answer is wrong

RE: Temporary Objects 2

lightyun — Fri, 09 Mar 2012 04:16:56 GMT

[quote][b]Toreador (3/9/2012)[/b][hr][quote][b]sqlmunkee (3/9/2012)[/b][hr]Even if you hadn't added the constraint to the table, the second program would still fail because there would already be the temp table from the first program.[/quote]That's not true. You can create as many identically named temp tables as you want provided they are in different sessions (even 2 different connections for the same user).[/quote]yes, the temp table with same name can definitely existed in different sessions.

RE: Temporary Objects 2

Toreador — Fri, 09 Mar 2012 04:02:36 GMT

[quote][b]sqlmunkee (3/9/2012)[/b][hr]Even if you hadn't added the constraint to the table, the second program would still fail because there would already be the temp table from the first program.[/quote]That's not true. You can create as many identically named temp tables as you want provided they are in different sessions (even 2 different connections for the same user).

RE: Temporary Objects 2

sqlmunkee — Fri, 09 Mar 2012 03:59:33 GMT

Even if you hadn't added the constraint to the table, the second program would still fail because there would already be the temp table from the first program.I wrote a small bit on my blog about what goes on in tempdb as regards temporary object names, which kind of touches on why you can have multiple table variables of the same name, but not multiple temp tables.http://sqlmunkee.blogspot.com/2011/09/temporary-variable-couple-of-points.html

RE: Temporary Objects 2

Toreador — Fri, 09 Mar 2012 03:46:49 GMT

Got this right as I was hit by it a few weeks ago after an attempt to "tidy up" some code by naming the constraints :blush:

RE: Temporary Objects 2

call.copse — Fri, 09 Mar 2012 03:03:47 GMT

That one took me about 20 minutes of careful thought to get what the problem was - I knew there had to be one somewhere (besides the obvious typo). Useful to be aware of the issue.

RE: Temporary Objects 2

Keld Laursen (VFL) — Fri, 09 Mar 2012 01:43:53 GMT

Well, I didn't catch the error on the select from Foobar, but just thought about locking while selecting/inserting.I didn't really look into the temp table as this should be local to the procedure, but the constraint eluded my reading abilities. I have on other occations been bitten by ghe "named sometning" in a procedure, so I should be warned.Anyway, the "select any two correct" should have hinted at the correct answer. If we disregard deadlocks between concurrent runs, then instance #2 should always wait for instance #1, så if it were possible to complete #2 at all, it should. And as others have pointed out: If both completed, you would have 3 check marks.

RE: Temporary Objects 2

Hugo Kornelis — Fri, 09 Mar 2012 01:05:45 GMT

I am embarassed to admit that I got this question, that apparently was based on my comments, wrong.I should have been warned. I even checked the code several times. All because I thought that if both programs run without error (as I assumed), there would have been THREE correct answers. So I checked. And checked again.I did contemplate the extra comma, but figured that this would not be Ron's style, so it had to be a genuine type (hint: test all code before submitting QotD!)I did also contemplate the inconsistent use of upper- and lowercase, but figure that wouldn't be Ron's style either (hint: test all code on a database with case-sensitive collation)And after reading the script yet another time, I ticked the first three options - and then kicked myself for overlooking the named constraint.Thanks, Ron! Good question (though unfortunately hampered by the typo and the inconsistent upper-/lowercase).

RE: Temporary Objects 2

Koen Verbeeck — Fri, 09 Mar 2012 00:40:13 GMT

The real answer is: the proc cannot be created because of the flagrant syntax violation.

RE: Temporary Objects 2

monkeyloafer — Fri, 09 Mar 2012 00:29:48 GMT

The answer is I think incorrect:[i]SELECT Id,,ProductionDate FROM Foobar[/i] - if you assume this is not a typo then both first and second users will receive an error message - I demand a recount!

RE: Temporary Objects 2

bitbucket-25253 — Thu, 08 Mar 2012 21:29:55 GMT

Once again I looked at my notes and here is the linkPosted 11/15/2011 1:34 PM[url]http://www.sqlservercentral.com/Forums/Topic1205649-1222-2.aspx[/url]Mr Hugo Kornelis comment is in my display mode is at the bottom of page 2 of the forumThe title of the forum is Table variable - 2

RE: Temporary Objects 2

bitbucket-25253 — Thu, 08 Mar 2012 21:28:13 GMT

RE: Temporary Objects 2

baabhu — Thu, 08 Mar 2012 20:59:29 GMT

Hi Bitbucket, The link you provide is not correct. http://www.sqlservercentral.com/questions/UNION/88678/http://www.sqlservercentral.com/Forums/Topic1205649-1222-2.aspxAnd there is a typo in the question. SELECT Id,,ProductionDate FROM FoobarInitially I was confused whether it has to do anything with the question itself. Anyways finally managed to get it right. :-)

Temporary Objects 2

bitbucket-25253 — Thu, 08 Mar 2012 20:56:14 GMT

Comments posted to this topic are about the item [B]Temporary Objects 2[/B]