March 7, 2013 at 4:53 am
demonfox (3/6/2013)
did I miss something ???or there are more than 2 failures ...
select db_id('dbdb')
-- and the second one
select object_id('dbdb')
I don't think it will return the same value for a database.
I dig a little bit on the Object_id for a reference .. and in msdn
http://msdn.microsoft.com/en-us/library/ms190328.aspx
it says
OBJECT_ID Returns the database object identification number of a schema-scoped object.
It would return NULL ..
so the query
USE master;
GO
IF OBJECT_ID('DBDB') IS NULL CREATE DATABASE DBDB
-- it should be IF DB_ID('DBDB') IS NULL CREATE DATABASE DBDB
GO
it would fail, if the database already exist..
EDIT : Okay, in the question , the first query drops the database .. so , it would be fine in this Qotd , but in general.. I think it would be an information while using it in the code..
Thanks for the info. Object_id() has a 2nd parameter, object_type, and database is not on the list. Thus, Object_id('dbname') returns null.
http://msdn.microsoft.com/en-us/library/ms190324%28v=sql.100%29.aspx
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
March 7, 2013 at 5:59 am
+1
Nice Question ....
March 7, 2013 at 7:09 am
GOOD QUESTION...
NICE EXPLANATION FROM GUYS lOKESH, DANNY... 😛
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 7, 2013 at 7:40 am
Nice question... I really learned two points.... 1. with go- how to create stored procedures and functions
2. without go- how to create stroed procedures and functions........:-D
Manik
You cannot get to the top by sitting on your bottom.
March 7, 2013 at 10:03 am
Good Question.... lots of fun to read through... I started saying Dibbiddy Dibbidy Dibbiddy....
March 7, 2013 at 10:11 am
Nothing wrong with the following for step 6 though (and the equivalent for step 4):
USE DBDB;
-- no 'GO' necessary
IF OBJECT_ID('dbo.udfUDF') IS NULL
exec('CREATE FUNCTION dbo.udfUDF(@f int) RETURNS INT AS BEGIN SET @f=1 RETURN @f END;');
In my opinion 'GO' is very overused. It's almost "stick a go in everywhere just in case", particularly in code examples.
For instance in this QOD, all except one 'GO' can be ommitted and the whole script executed in one 'GO' (but two batches(!)):
-- Query #1
USE master;
IF DB_ID('DBDB') IS NOT NULL DROP DATABASE DBDB
-- Query #2
IF DB_ID('DBDB') IS NULL CREATE DATABASE DBDB
go -- this one is necessary otherwise the rest of the script won't compile.
-- Query #3
USE DBDB;
IF OBJECT_ID('dbo.TBTB') IS NULL CREATE Table dbo.TBTB (tb1 int)
-- Query #4
IF OBJECT_ID('dbo.uspUSP') IS NULL
exec('CREATE PROCEDURE dbo.uspUSP AS SET NOCOUNT OFF');
-- Query #5
IF OBJECT_ID('dbo.TYTP') IS NULL CREATE TYPE dbo.TPTP AS TABLE(TYTB int)
-- Query #6
IF OBJECT_ID('dbo.udfUDF') IS NULL
exec('CREATE FUNCTION dbo.udfUDF(@f int) RETURNS INT AS BEGIN SET @f=1 RETURN @f END;');
March 7, 2013 at 3:06 pm
I ran all 6 in my SQL Server 2008R2 and the only one that did not error was the first one. Query 2 gives me error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\Database files\SQL Server\DBDB.mdf" failed with the operating system error 3(The system cannot find the path specified.).
and this caused every subsequent query to fail. I even changed the query 2 to use DB_ID. I changed back to original which I knew would fail and yes, that fails too.
So what is going on here? I am running SQL Server 2008R2 Express. Could that be it? I am just learning SQL Server. I work mostly with Oracle.
March 7, 2013 at 3:40 pm
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
March 7, 2013 at 9:39 pm
I am nominating this for the Most Misguided QotD of the Year Award.
March 8, 2013 at 12:18 am
Revenant (3/7/2013)
I am nominating this for the Most Misguided QotD of the Year Award.
😀
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
March 8, 2013 at 2:13 am
sgehret 2557 (3/7/2013)
I ran all 6 in my SQL Server 2008R2 and the only one that did not error was the first one. Query 2 gives me error:Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\Database files\SQL Server\DBDB.mdf" failed with the operating system error 3(The system cannot find the path specified.).
and this caused every subsequent query to fail. I even changed the query 2 to use DB_ID. I changed back to original which I knew would fail and yes, that fails too.
So what is going on here? I am running SQL Server 2008R2 Express. Could that be it? I am just learning SQL Server. I work mostly with Oracle.
sgehret: If the directory doesn't exist, create it. If the directory exists (and there's enough free space on the disk for a new database), start services.msc and see what user the SQL Server service is running under, then look at the permissions on the directory to check the service user has full access.
March 8, 2013 at 4:21 am
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
March 8, 2013 at 4:45 am
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2013 at 6:15 am
Interesting question, thanks so much!
March 8, 2013 at 10:11 am
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.
Not all gray hairs are Dinosaurs!
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply