March 8, 2012 at 8:56 pm
Comments posted to this topic are about the item Temporary Objects 2
March 8, 2012 at 8:59 pm
Hi Bitbucket,
The link you provide is not correct.
And there is a typo in the question.
SELECT Id,,ProductionDate FROM Foobar
Initially I was confused whether it has to do anything with the question itself.
Anyways finally managed to get it right. 🙂
March 8, 2012 at 9:28 pm
Once again I looked at my notes and here is the link
Posted 11/15/2011 1:34 PM
http://www.sqlservercentral.com/Forums/Topic1205649-1222-2.aspx
Mr Hugo Kornelis comment is
in my display mode is at the bottom of page 2 of the forum
The title of the forum is Table variable - 2
March 8, 2012 at 9:29 pm
Once again I looked at my notes and here is the link
Posted 11/15/2011 1:34 PM
http://www.sqlservercentral.com/Forums/Topic1205649-1222-2.aspx
Mr Hugo Kornelis comment is
in my display mode is at the bottom of page 2 of the forum
The title of the forum is Table variable - 2
March 9, 2012 at 12:29 am
The answer is I think incorrect:
SELECT Id,,ProductionDate FROM Foobar - if you assume this is not a typo then both first and second users will receive an error message - I demand a recount!
March 9, 2012 at 12:40 am
The real answer is: the proc cannot be created because of the flagrant syntax violation.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 9, 2012 at 1:05 am
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).
March 9, 2012 at 1:43 am
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.
March 9, 2012 at 3:03 am
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.
March 9, 2012 at 3:46 am
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:
March 9, 2012 at 3:59 am
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
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
March 9, 2012 at 4:02 am
sqlmunkee (3/9/2012)
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.
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).
March 9, 2012 at 4:16 am
Toreador (3/9/2012)
sqlmunkee (3/9/2012)
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.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).
yes, the temp table with same name can definitely existed in different sessions.
March 9, 2012 at 4:19 am
I agree with monkeyloafer the answer is wrong
March 9, 2012 at 4:32 am
You're right, and for the reasons in my blog post 🙂
My bad. Sorry, I'll get my coat.
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply