InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 05:25:40 GMT20http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxJust do this in a binary collation and the "in one batch" issue no longer matters.Tue, 26 Feb 2013 05:10:53 GMTsqlcentral-bugmenothttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]crazy_s245 21087 (2/19/2013)[/b][hr]This code works in SQL 2005 and returns the inserted row, only there was error "Column, parameter, or variable #4: Cannot find data type Date". Same code didn't work in SQL 2012. Can any one please explain why this code worked in SQL 2005?[/quote]If you haven't found the answer already, the create table part didnot execute in 2005 (since the date datatype is invalid) so the table got created with the select into statement.Tue, 19 Feb 2013 20:31:09 GMTjoeroshanhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxThis code works in SQL 2005 and returns the inserted row, only there was error "Column, parameter, or variable #4: Cannot find data type Date". Same code didn't work in SQL 2012. Can any one please explain why this code worked in SQL 2005?Tue, 19 Feb 2013 19:27:07 GMTcrazy_s245 21087http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxNice and Easy question..Thanks..Sun, 17 Feb 2013 13:38:25 GMTpchiragshttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Hugo Kornelis (2/11/2013)[/b][hr]I'm surprised you didn't know this. Integer is actually the official name; int is an accepted abbreviation.*cough* I have to retract the above. That's what I always thought, but when I went out to find a Books Online reference, I was surprised to see only "int" there. "Integer" is not even mentioned. :unsure:EDIT: Found the source of my confusion. The ANSI documents describing the SQL standard define both INT and INTEGER, and describe INT as "equivalent to INTEGER". I've always interpreted that as "INTEGER" being the official form and "INT" being an accepted alternative.[/quote]And that is one more reason why I value your posts, Hugo: you're honest enough to give us your initial, erroneous thought and then the reason reality is different. I often find that I learn more from articles, posts, blogs, etc. when the author is able to walk me along through their train of thought, even if there are discovered errors along the way.Thanks,RichFri, 15 Feb 2013 11:26:46 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]john.arnott (2/13/2013)[/b][hr][quote]I know I'm late to this party, but have to ask. What 4th statement? I see a Create Table, a Select Into and then a Select. Was the original code edited between publication and today?[/quote]The third statement was actualluy two statements (a select statement and a drop statement) so Hugo counted them as two, not one. Seems reasonable to me.edit: 's i litreachadh beurla thar comas agam - I can't spell English without an edit!Wed, 13 Feb 2013 18:56:05 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Dave62 (2/13/2013)[/b][hr]By the way I also appreciate your extensive knowledge and contributions here. I find some of your posts educational, some entertaining, and some are both.Enjoy![/quote]Well, I try to be educational, but with Gail and Hugo and Jeff and Paul posting with far more understanding of SQL Server that I have (or ever expect to have) I suspect that I'm a bit of a failure.Wed, 13 Feb 2013 18:51:10 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Hugo Kornelis (2/11/2013)[/b][hr]. . . . The option "Only the 1st and 3rd statement will succeed but no rows will be returned", though technically not 100% accurate (as the 4th statement will also succeed), comes close enough to be considered te correct answer.I threw my dart to pick one of the two options, and failed. Oh well.[/quote]I know I'm late to this party, but have to ask. What 4th statement? I see a Create Table, a Select Into and then a Select. Was the original code edited between publication and today?Wed, 13 Feb 2013 17:07:54 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]L' Eomot Inversé (2/12/2013)[/b][hr]... When wearing my mathematician hat I used to hate it when people got careless like that with numerical data, but these days I no longer worry about it, just point it out when it seems appropriate to do so. [/quote]Tom, you have a lot of interesting theories about my intent for the last 2 buckets but the simple explanation is that it was to try and make a correlation with the 1st 2 buckets. The 1st 2 buckets are about as straight forward as you can get - Correct and Incorrect. It didn't take much of a mathematician's hat to see at the time of my post they were at 67% and 33%. The last 2 buckets may have been loosely labeled but it's easy to count that there have been more positive than negative posts. Correlation confirmed.By the way I also appreciate your extensive knowledge and contributions here. I find some of your posts educational, some entertaining, and some are both.Enjoy!Wed, 13 Feb 2013 06:44:55 GMTDave62http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxWhat Hugo said.Wed, 13 Feb 2013 06:30:52 GMTStephen_W_Doddhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Hugo Kornelis (2/12/2013)[/b][hr]In my opinion, positive posts ("great question!"), negative posts ("you suck!") and whining posts ("give me my point") are all an utter waste of bandwith, and I suspect most of the people who regularly write posts like that of doing it for the sole purpose of accumulating points. [/quote]For the sole purpose of accumulating a point I have to agree with you. Just kidding Hugo. We greatly appreciate your contribution to these discusssions.Tue, 12 Feb 2013 14:54:05 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Dave62 (2/12/2013)[/b][hr]I must say that a large majority of your posts I find educational.[/quote]Thanks! That is what I normally try to achieve here.As to that previous post ... with hindsight, I now think I should have hit the browsers close button instead of the post button after writing it out and getting it off my chest. But that insight has come too late.My apologies to anyone who might feel offended.Tue, 12 Feb 2013 11:43:14 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Hugo Kornelis (2/12/2013)[/b][hr]I also don't see the point of having a single bucket for "positive and/or educational".[/quote]One common purpose of combining two things into a single bucket is to create an impression that these two things are closely associated. It's an extremely common practise amongst PR people. But Dave is presumably a DBA not a PR hack so I don't imagine his purpose of doing it was that. Probably it was just a rather silly (because it might cause people to think that he was trying to create associations between disagreeing with him and whining and between agreeing with him and being educational) means of reducing the number of buckets. Anyway, his arithmetic is wrong, since the educational posts are mostly negative and should be in both buckets so that the percentages should sum to more than 100%, but he has them sum to 100%. When wearing my mathematician hat I used to hate it when people got careless like that with numerical data, but these days I no longer worry about it, just point it out when it seems appropriate to do so.Tue, 12 Feb 2013 11:17:26 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxI often work with product owners who write requirements with very similar logic and wording. ;-)Thanks for the question!Tue, 12 Feb 2013 10:20:44 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Hugo Kornelis (2/12/2013)[/b][hr]No, I don't find that interesting at all. ...[/quote]Well of course we are all entitled to our own opinion of what is interesting.[quote](I also can't help wondering how you would qualify your own post. Or my response to it in this post. My qualification would be "waste of bandwidth", for I don't really see how discussing statistics of posts adds value that is relevant for the average SQL Server Central visitor.)[/quote]Again, we are all entitled to our own opinion of what may be relevant to people who use SQL Server. I would think some DBA's may find a discussion of statistical analysis interesting and/or relevant and of course some will not.I guess you are free to make posts that you qualify as a "waste of bandwidth" but I must say that a large majority of your posts I find educational. Thank you for your contributions.Enjoy!Tue, 12 Feb 2013 10:11:00 GMTDave62http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Dave62 (2/12/2013)[/b][hr]Isn't it interesting there is a correlation within 5% of correct answers to positive posts and incorrect answers to whining posts. :w00t:[/quote]No, I don't find that interesting at all.I also don't see the point of having a single bucket for "positive and/or educational".In my opinion, positive posts ("great question!"), negative posts ("you suck!") and whining posts ("give me my point") are all an utter waste of bandwith, and I suspect most of the people who regularly write posts like that of doing it for the sole purpose of accumulating points. I hate it when I get mails from twnety different QotD discussions I am subscribed to, all within thrigy minutes, all because of a post from the same poster, and all contributing nothing but "good question", "got it right", "missed it", or whatever.Posts that are useful are what you probably label "educational". Those include obviously posts that give (extra) information, but also posts that ask for more information, for an explanation, or for a clarification - those posts in themselves don't educate, but they (hopefully) trigger a response that WILL add information to the topic.Then again - a post that adds information that has already been added a dozen times in the same topic would, in my opinion, again qualify as waste of bandwidth.(I also can't help wondering how you would qualify your own post. Or my response to it in this post. My qualification would be "waste of bandwidth", for I don't really see how discussing statistics of posts adds value that is relevant for the average SQL Server Central visitor.)Tue, 12 Feb 2013 09:49:45 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[b][u]Statistics so far[/u][/b]:[color="#F4F8FB"]---[/color]Number of attempted answers: 880[color="#F4F8FB"]---[/color]Number of correct answers: 587 (67%)[color="#F4F8FB"]---[/color]Number of incorrect answers: 293 (33%)[color="#F4F8FB"]---[/color]Number of discussion posts: 39[color="#F4F8FB"]---[/color]Number of positive and/or educational posts: 28 (72%)[color="#F4F8FB"]---[/color]Number of negative and/or whining posts: 11 (28%)Isn't it interesting there is a correlation within 5% of correct answers to positive posts and incorrect answers to whining posts. :w00t:Enjoy!Tue, 12 Feb 2013 08:44:13 GMTDave62http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxI thought the question was straight forward and got that correct. Thanks :-)Mon, 11 Feb 2013 22:12:52 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxSince the question didn't ask what version of SQL Server, none of the answers are correct. In SQL 2005, you get an error message, yet you still get one row selected.Msg 2715, Level 16, State 7, Line 2Column, parameter, or variable #4: Cannot find data type Date.Mon, 11 Feb 2013 12:51:27 GMTrfreiberg001http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxWording was confusing, however I managed to get it right.Saludos,Mon, 11 Feb 2013 12:12:59 GMTEL Jerryhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxI made the assumption that the author didn't actually understand that GO was a batch separator and was instead focused on the temp table creation process. I hate to make these assumptions but I couldn't see why they would write to run it as one batch when one could not do so without modifying the code supplied. Another flawed QoTD. Thanks for trying.Mon, 11 Feb 2013 11:55:33 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]ldorian81 (2/11/2013)[/b][hr]If you put the whole query into SSMS 2008 and run it without removing the 'GO' commands you will still get the same results as shown below.[code]Msg 2714, Level 16, State 1, Line 18There is already an object named '#TempQoD' in the database.[/code].[/quote]That appears on the messages tab. The results tab had the table selected with no rows.Mon, 11 Feb 2013 10:03:51 GMTEd Bhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxI have been reading the QODs as of late with a grain of salt. If the wording is correct there is one answer, but given the wording of some of the questions and guessing on the intent the answer is not always as it appears. I guessed at the answer and in doing so I made an assumption. I assumed that the author was saying "what happens if you include all of the SQL in a batch job or a scheduled script run as a batch job?" THis gives a second meaning to the word batch and made the correct answer correct. However, I have to agree that GO denotes a batch execution of all previous unexecuted SQL, that is just how it is done. Multiple Go statements are by definition multiple batch executions. If you remove all but the last Go there is a different answer. But I have to admit that this line of discussion to a rookie, ( and we were all rookies at one time), this discussion is very valuable and educational. M.Mon, 11 Feb 2013 10:03:04 GMTMiles Nealehttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxI saw that the the CREATE TABLE and INSERT INTO #Table would conflict therfore running it as one batch (as stated in the Question) would error the whole statement regardless of the GO commands being there or not being there.The results are the same if you run the whole query as one batch with or without the 'GO' commands.Therfore the Answer[Quote]Only the 1st and 3rd statement will succeed but no rows will be returned.[/Quote]is wrong!And the Correct answer should be.[Quote]All statements will fail.[/Quote]If You run the whole query as 1 Batch (as stated!)If however, you were asked to run the three independantly in the same Query window then the right answer would be.[Quote]Only the 1st and 3rd statement will succeed but no rows will be returned.[/Quote]Poorly wrote question and needs correcting. (oh and give me the point for a correct answer :-D:-P)Mon, 11 Feb 2013 08:50:06 GMTldorian81http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]sestell1 (2/11/2013)[/b][hr]Even if you ignore the erroneous use of the word 'batch' in the question and leave the GOs in place... the third answer is still not correct because it says that only statements 1 and 3 will run successfully, which is not the case. It's unfortunate because the wording issues are confusing and distract from the intent of the question.[/quote]I agree! A truce?Mon, 11 Feb 2013 08:44:29 GMT(Bob Brown) http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b](Bob Brown) (2/11/2013)[/b][hr][quote][b]Sean Lange (2/11/2013)[/b][hr]Here is the original code with the GOs commented out so it will be a single batch.[/quote]But the GOs [b]are [/b]there.[/quote]Even if you ignore the erroneous use of the word 'batch' in the question and leave the GOs in place... the third answer is still not correct because it says that only statements 1 and 3 will run successfully, which is not the case. It's unfortunate because the wording issues are confusing and distract from the intent of the question.Mon, 11 Feb 2013 08:37:26 GMTsestell1http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxI really wasn't sure if I have to 'GO' or not to 'GO' for my answer...I GOed and got it right..So what I learned from this Qotd is whenever there is a doubt just keep GOing ;-)Mon, 11 Feb 2013 08:22:24 GMTvenkat9.sqlhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Sean Lange (2/11/2013)[/b][hr]Here is the original code with the GOs commented out so it will be a single batch.[/quote]But the GOs [b]are [/b]there.Mon, 11 Feb 2013 08:10:32 GMT(Bob Brown) http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxIf you put the whole query into SSMS 2008 and run it without removing the 'GO' commands you will still get the same results as shown below.[code]Msg 2714, Level 16, State 1, Line 18There is already an object named '#TempQoD' in the database.[/code]Very poor wording.Mon, 11 Feb 2013 08:06:23 GMTldorian81http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b](Bob Brown) (2/11/2013)[/b][hr][quote][b]sestell1 (2/11/2013)[/b][hr]It's too bad about the wording issues, as otherwise this would have been a decent QotD.[/quote]I think it [i]was[/i] a pretty decent QotD. Approximately 2/3 got it right. Seems that number three was the only obvious answer to choose.[/quote]But #3 is not the correct choice when you consider the wording of the question.[quote]What will the outcome be after running all the following statements as 1 batch? [/quote]Does that not indicate that you should remove the batch separators from the code posted so that it will run in a single batch?Here is the original code with the GOs commented out so it will be a single batch.[code]-- Statement 1Create Table #TempQoD ( RowId Integer , Question varChar(max) , Answer varChar(max) , PublishDate Date );--GO-- Statement 2Select 1 As [RowId] , 'True or False: The HAVING clause is the 4th phase of logical query processing?' As [Question] , 'True: 1-FROM, 2-WHERE, 3-GROUP BY, 4-HAVING, 5-SELECT, 6-ORDER BY.' As [Answer] , '15 Jan 2013' As [PublishDate] Into #TempQoD;--GO-- Statement 3Select * From #TempQoD;Drop Table #TempQoD;--GO[/code]Executing this as a single batch will not parse.[code]Msg 2714, Level 16, State 1, Line 18There is already an object named '#TempQoD' in the database.[/code]Therefore the only obvious answers are either #2 or #3 depending...Mon, 11 Feb 2013 07:51:39 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Koen Verbeeck (2/11/2013)[/b][hr]Well, it doesn't help that no-one ever uses Integer [/quote]I [i]always [/i]use Integer :-)Mon, 11 Feb 2013 07:45:18 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]sestell1 (2/11/2013)[/b][hr]It's too bad about the wording issues, as otherwise this would have been a decent QotD.[/quote]I think it [i]was[/i] a pretty decent QotD. Approximately 2/3 got it right. Seems that number three was the only obvious answer to choose.Mon, 11 Feb 2013 07:40:17 GMT(Bob Brown) http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxI got lucky in guessing that the intent of the question was to show that you can't use INTO with an existing table, and that the answer really meant statements 1, 3, and *4* would execute successfully. It's too bad about the wording issues, as otherwise this would have been a decent QotD.Mon, 11 Feb 2013 07:32:37 GMTsestell1http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxThanks for the question!Mon, 11 Feb 2013 07:30:17 GMT(Bob Brown) http://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxI too had the same thought as Hugo. The single batch part threw me off so I figured that the OP must have been running the code as posted when creating the question and by single batch meant running all of the code by hitting F5. All in all a decent question but the wording could have been better. Thanks for the effort Dave.Mon, 11 Feb 2013 07:13:34 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxThanks for the trips down memory lane... This question reminds me of things I did before I learned how temp tables worked. I was so confused way back when on why I couldn't use select INTO after I had created said table.Of course I learned better way back then too... :)Mon, 11 Feb 2013 07:04:50 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxMaybe another option for a(n) (in)correct answer: "Only statement 2 and 3 will succeed and 1 row will be returned...".But probably I am the only one still having SQL Servers running pre-2008. :-DMon, 11 Feb 2013 06:55:01 GMTMightyhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]Hugo Kornelis (2/11/2013)[/b][hr]I'm surprised you didn't know this. Integer is actually the official name; int is an accepted abbreviation.*cough* I have to retract the above. That's what I always thought, but when I went out to find a Books Online reference, I was surprised to see only "int" there. "Integer" is not even mentioned. :unsure:[/quote]Well, it doesn't help that no-one ever uses Integer and every script generated by SSMS uses int.Mon, 11 Feb 2013 06:40:58 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspxI think, i didn't take it seriously, and i got it wrong.anyway good question.Mon, 11 Feb 2013 06:38:51 GMTDanny Oceanhttp://www.sqlservercentral.com/Forums/Topic1418057-2739-1.aspx[quote][b]binod.soft (2/10/2013)[/b][hr]The Statement has three GO syntax which indicates three Batches. Each Go used shows an end of the Batch. [/quote]Yes, there's not really any getting round that - however you sometimes learn as much from a badly worded question as a perfectly worded one - you just don't necessarily get your point(s).;-)Mon, 11 Feb 2013 06:13:14 GMTsipas