March 8, 2013 at 10:20 am
Hi,
It's official. I'm an idiot. I have my local SQL Server setup to create data files on my D drive due to space issues. But I connected to a database on another server to test this question. Big duh! on my part. When I finally figured it out and ran it on my Server and saw the correct results.
Sorry for any inconvenience. Thanks for your information.
Take care,
Scott
March 8, 2013 at 12:01 pm
Did I am misleading for the answer? Why so many answers are wrong?
How other members answeredCorrect answers: 55% (374)
Incorrect answers: 45% (302)
Total attempts: 676
--------------------------------------------------------------------------------
Query #1 12%
--------------------------------------------------------------------------------
Query #2 14%
--------------------------------------------------------------------------------
Query #3 7%
--------------------------------------------------------------------------------
Query #4 71%
--------------------------------------------------------------------------------
Query #5 26%
--------------------------------------------------------------------------------
Query #6 69%
Total scores and standings
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
March 8, 2013 at 12:22 pm
chgn01 (3/8/2013)
Did I am misleading for the answer? Why so many answers are wrong?How other members answeredCorrect answers: 55% (374)
Incorrect answers: 45% (302)
Total attempts: 676
The QotD is for learning and a daily challenge to make us think. What you gave was an opportunity for both. The vast majority of those answering the question did not post. Those who did post are those who like to have a voice in the process or think something is to be gained by posting. But again most of the folks read the question got it right or wrong and then went about their normal day.
Was it misleading? Hey, it was a question, and it got us thinking and talking, and thank you for that.
M.
Not all gray hairs are Dinosaurs!
March 8, 2013 at 6:35 pm
Miles Neale (3/8/2013)
L' Eomot Inversé (3/8/2013)
Revenant (3/7/2013)
I am nominating this for the Most Misguided QotD of the Year Award.I think it's a poor fifth in that race. 😎
Tom,
This being early March, your comment says volumes about the QotD so far this year.
The thing I must say about all those questions is that each has given the community a great platform for discussion about topics and uses that might not have taken place without the poor questions.
M.
It's important to remember that it's sometimes the misguided questions that give rise to the best discussions - in particular, misguided question will often result in a really excellent educational post from Hugo, and I don't imagine that I'm the only person who has benefited from his comments.
Tom
March 8, 2013 at 6:56 pm
chgn01 (3/8/2013)
Did I am misleading for the answer?
NO, you are not misleading in any way, the only thing genuinely wrong with the question is using OBJECTID when DBID would have been better - if that was intended as obfuscation then it was horribly wrong, but if - as seems more likely - it was just a mstake then because it was harmless in context it was acceptable, not a problem.
Why so many answers are wrong?
I don't dare to make an assertion as to why as mant as 45% of people answering were wrong, but I will admit to suffering from a suspicion that close to half of DBAs haven't a clue about writing SQL - they understand some of the comand line utilities and the things they can do by using the menus or right clicking in the obje ct explorer window of SSMS but T-SQL is beyond their comprehension, because it's a programming language and they know nothing of programming.
One of the good things about questions like yours is that it may teach these people a little about T-SQL. So thank you very much for the question.
Tom
March 11, 2013 at 3:53 am
Thanks for Good observation/question 😎
March 11, 2013 at 5:14 am
Hmmm, not sure why some people are so critical about this question. Okay, there is an issue with OBJECT_ID instead of DB_ID in query 2, but other than that the question looks okay to me.
I am disappointed to see the advice to enclose CREATE PROC in EXEC, making it dynamic. That is really horrible. You lose colorcoding of keywords, you lose intellisense syntax checking (and intellisense autocomplete, for those who have that feature enabled), and you can very easily make mistakes with doubling all the single quotemarks.
Here are the recommended patterns:
1. Create proc if it doesn't exist, or change it if it does
IF OBJECT_ID('dbo.procname, 'P') IS NOT NULL
DROP PROC dbo.procname;
GO
CREATE PROC dbo.procname
(@Parm1 int, ...)
AS
-- code goes here
GO
2. Same as above, but use ALTER instead of DROP/CREATE, so that permissions are retained
IF OBJECT_ID('dbo.procname, 'P') IS NULL
EXEC (N'CREATE PROC dbo.procname AS SELECT 1;'); -- Create dummy procedure to be altered in the next step
GO
ALTER PROC dbo.procname
(@Parm1 int, ...)
AS
-- code goes here
GO
(You could even have only the dummy CREATE PROC in the first batch and ignore the error message)
3. To mimic the QotD task: create if it doesn't exist, leave unchanged otherwise (though I fail to see a real-world use case for this):
IF OBJECT_ID('dbo.procname, 'P') IS NOT NULL
RAISERROR ('procedure already exists, aborting', 20, 1) WITH LOG;
GO
CREATE PROC dbo.procname
(@Parm1 int, ...)
AS
-- code goes here
GO
The severity of 20 in the raiserror forces SQL Server to drop the connection, so that the next batch is not executed. The WITH LOG is required for errors of that severity.
As an alternative, you could simply execute only the second batch and ignore the error message you get if the procedure already exists.
March 11, 2013 at 7:20 am
Hugo Kornelis (3/11/2013)
Hmmm, not sure why some people are so critical about this question. Okay, there is an issue with OBJECT_ID instead of DB_ID in query 2, but other than that the question looks okay to me.
Even that is really a non-issue, because it makes no difference in context (the database was dropped in query 1, so it changing OBJECT_ID to DB_ID leaves still gets NULL). My guess is that those who are complaining are complaining because they got it wrong. It looks to me like a good question, with a good answer.
I am disappointed to see the advice to enclose CREATE PROC in EXEC, making it dynamic. That is really horrible. You lose colorcoding of keywords, you lose intellisense syntax checking (and intellisense autocomplete, for those who have that feature enabled), and you can very easily make mistakes with doubling all the single quotemarks.
Me too. I didn't comment on it only because I didn't think I would avoid being offensive if I did. :ermm:
Tom
March 11, 2013 at 11:58 am
Miles Neale (3/8/2013)
chgn01 (3/8/2013)
Did I am misleading for the answer? Why so many answers are wrong?How other members answeredCorrect answers: 55% (374)
Incorrect answers: 45% (302)
Total attempts: 676
The QotD is for learning and a daily challenge to make us think. What you gave was an opportunity for both. The vast majority of those answering the question did not post. Those who did post are those who like to have a voice in the process or think something is to be gained by posting. But again most of the folks read the question got it right or wrong and then went about their normal day.
Was it misleading? Hey, it was a question, and it got us thinking and talking, and thank you for that.
M.
+1
March 23, 2013 at 7:25 pm
great question...
different one..
April 10, 2013 at 3:38 am
Definitely different question.
June 22, 2015 at 2:16 am
That explanation is loud and clear.
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply